If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is a special filter form a reasonable idea?
Hello!
I have a library application. It has a form which is used to enter/edit book information. This form uses a tab control and three subforms: one each for author, subject and series. Now, I've begun to realize that I have a problem. On this main form, I can do filters/sorts/finds, and that is great! However, of course, I can't filter the main form based on author, title or series. So, to solve this problem, I've created another form, based on a query which returns *all* of the necessary fields. This query isn't updateable. This form allows for filters/sorts/finds in all fields, author, subject, series included... On this form, there is a command button ("Edit this record") which opens the main form at that particular record for editing... Questions: 1. I *am* right that I can't filter/sort the main form based on subforms, right? 2. Does the approach I am trying seem reasonable, or is there a better way? Thanks for taking the time to consider this! Fred Boer (table structures, query structures below...) Tables... Tbl_Library Book_ID Title Dewey etc... Tbl_Author Tbl_Subject Tbl_Series etc..... Author_ID Subject_ID Series_ID AuthorLastName Subject SeriesName etc.... etc... etc.. Tbl_BookAuthor Tbl_BookSubject Tbl_BookSeries etc... Author_ID Subject_ID Series_ID Book_ID Book_ID Book_ID Queries for filter form: Qry_AllLibrary SELECT Tbl_Library.*, Tbl_Author.AuthorFirstName, Tbl_Author.AuthorMiddleName, Tbl_Author.AuthorLastName, Tbl_Location.Location, Tbl_Status.Status, Tbl_PubPlace.PubPlace, Tbl_Binding.Binding, Tbl_MediaFormat.MediaFormat, Tbl_Publisher.Publisher, Tbl_Series.Series, [Tbl_Author].[AuthorLastName] & (", "+[Tbl_Author].[AuthorFirstName]) & " " & [Tbl_Author].[AuthorMiddleName] AS Author FROM Tbl_Status INNER JOIN (Tbl_PubPlace INNER JOIN (Tbl_Publisher INNER JOIN (Tbl_MediaFormat INNER JOIN (Tbl_Location INNER JOIN (((Tbl_Binding INNER JOIN Tbl_Library ON Tbl_Binding.Binding_ID = Tbl_Library.Binding_ID) LEFT JOIN (Tbl_Author RIGHT JOIN Tbl_BookAuthor ON Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID) LEFT JOIN (Tbl_BookSeries LEFT JOIN Tbl_Series ON Tbl_BookSeries.Series_ID = Tbl_Series.Series_ID) ON Tbl_Library.Book_ID = Tbl_BookSeries.Book_ID) ON Tbl_Location.Location_ID = Tbl_Library.Location_ID) ON Tbl_MediaFormat.MediaFormat_ID = Tbl_Library.MediaFormat_ID) ON Tbl_Publisher.Publisher_ID = Tbl_Library.Publisher_ID) ON Tbl_PubPlace.PubPlace_ID = Tbl_Library.PubPlace_ID) ON Tbl_Status.Status_ID = Tbl_Library.Status_ID ORDER BY Tbl_Library.Book_ID; Qry_AllLibrarySubject SELECT Qry_AllLibrary.*, Tbl_Subject.Subject FROM Tbl_Subject RIGHT JOIN (Qry_AllLibrary LEFT JOIN Tbl_BookSubject ON Qry_AllLibrary.Book_ID = Tbl_BookSubject.Book_ID) ON Tbl_Subject.Subject_ID = Tbl_BookSubject.Subject_ID; |
#2
|
|||
|
|||
Is a special filter form a reasonable idea?
"Fred Boer" wrote in message
... Hello! I have a library application. It has a form which is used to enter/edit book information. This form uses a tab control and three subforms: one each for author, subject and series. Now, I've begun to realize that I have a problem. On this main form, I can do filters/sorts/finds, and that is great! However, of course, I can't filter the main form based on author, title or series. So, to solve this problem, I've created another form, based on a query which returns *all* of the necessary fields. This query isn't updateable. This form allows for filters/sorts/finds in all fields, author, subject, series included... On this form, there is a command button ("Edit this record") which opens the main form at that particular record for editing... Questions: 1. I *am* right that I can't filter/sort the main form based on subforms, right? Actually you can, but it requires code or the use of the advanced filter grid. The reason is that you have to use a subquery like... BookID In(SELECT BookID FROM Authors WHERE AuthorName = "Joyce") -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Is a special filter form a reasonable idea?
Thanks for setting me straight, Rick! I see now that this is a possibility,
but, in my particular situation it is unlikely the users will be able to manage the advanced filter grid; I just want them to be able to do basic sorts and filters. Still, I am happy to be wrong if I can learn something new! Fred Actually you can, but it requires code or the use of the advanced filter grid. The reason is that you have to use a subquery like... BookID In(SELECT BookID FROM Authors WHERE AuthorName = "Joyce") -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|