Is a special filter form a reasonable idea?

[Fred Boer]

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;

 

[Rick Brandt]

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")

 

[Fred Boer]

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