Multiple subforms (Access 2K SR1)

[Howard Hillman]

I am creating a db for a person that gives speeches and writes articles. Each speech is one record and would be the Mainform. The speech can be on one of 25 or so categories (Retirement, Estate Planning, Media Reference, etc.) and each category can have 10 to 20 subcategories. Subcategories for Retirement might be Pensions, 401(k), Social Security, etc. Can I put 25 subforms on one mainform? Is there a better way? We want to enter a lot of "old" data from past speeches, as well as new ones. A major feature is to allow the user to query the db to find out "When did I write that piece for Newsweek on 401(k) contributions?"

[Drew]

Why would you want 25 subforms? Just have one form, with two combo boxes. One combo box would be the category, and the other would be the sub category. On the OnClick event of the Category, just refresh the subcategory list.

I would recommend the following table structure:

tblCategories:
CatID (AutoNumber)
CatName (Text)
CatDescription (Text....if you want another place to 'comment' on the category)

tblSubCategories
SubCatID (AutoNumber)
CatID (Number (foriegn key from tblCategories)
SubCatName (Text)
SubCatDescription (Same as above)

tblSpeeches:
SpeechID (AutoNumber)
CatID (Number (foriegn key from tblCategories))
SubCatID (Number (foriegn key from tblSubCategories))
SpeechDate (Date/Time)
SpeechText (Memo)

(You'll probably have more field to put in tblSpeeches.

[Howard Hillman]

Drew, thanks for clarifying. I was originally thinking about Combo Boxes, but wasn't sure how to link them. I looked at Northwind for hints and was led down the subform path. I should have known better.
I created the tables and linked them. Then I added data for two main categories and two subcategories. However, I am having trouble getting the second combo box to update when the first is changed. I followed your example and put the following code in the On Click event of the Category :
Private Sub Form_Click()
SpecificSubject1.Requery .......(SpecificSubject1 is the name of the the SubCat combo box on the Form.)
End Sub
I used Requery instead of Refresh after reading the Help files. This is a single-user db.
Any suggestions on why the subcategory doesn't update when I select the second item in the Category Combo Box?

[Drew]

What is the Rowsource for that combo box. It should be a query that shows the subcategory with a criteria of the category in the first combo box.

[Howard Hillman]

We're getting closer. There is a query called qrySubcat, but when I run it it only shows the data for the first Category. It looks like it needs a Where clause in SQL, or another field with criteria. How do I tell it to pick up the correct Category?