A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is a special filter form a reasonable idea?



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2004, 03:07 AM
Fred Boer
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 04:16 AM
Rick Brandt
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 03:06 PM
Fred Boer
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.