Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Multiple subforms (Access 2K SR1)

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

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple subforms (Access 2K SR1)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Multiple subforms (Access 2K SR1)

    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?

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple subforms (Access 2K SR1)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Multiple subforms (Access 2K SR1)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •