Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying Multiple Subforms (XP)

    I have a form with 5 subforms. Each subform has 50 to 100 checkboxes. I'm now trying to create a search form with 5 list boxes containing the fields of each subform. I want users to be able to select field names on one or all of the listboxes and then open the main form and limit it to those records with checks in those fields. The subforms are linked through an ID field, so all I really need is a list of ID's of people that meet the criteria and I can then open the form and apply the filter. There are too many fields to create a single query and then build a where clause. Any ideas on the easiest way to approach this? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Querying Multiple Subforms (XP)

    I'm sorry, but I can't envision your design. Are the subforms populated from separate tables? Are they linked to a record on the parent form? What do you mean, they're linked through an ID field, linked to what? And what exactly are you trying to query, the source of 5 different subforms, the source for the parent form, or something else entirely?

    You don't normally limit the records in subforms directly, you limit the parent records in the main form and the subforms are filtered automatically. Five subforms with 50 to 100 checkboxes each sounds like a nightmare for users. Perhaps if you explain what kind of data you're displaying and what you are trying to accomplish with it, someone can be of more help.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Multiple Subforms (XP)

    Sorry about that. We have this monster form that tracks skills for each person by catagory. We have five categories with 50 to 100 skills each. Because there are too many fields to store in a single table or query, they were broken up by category - a separate table for each category, each linked to the others by a person id field. We have a form with a tab for personal information and then a tab for each category. The category tabs are made up of subforms for each category.

    On the search form, I have list boxes for each category. Users can select as many fields as they want in each category. What I then want to do is open the main form and limit it (the parent form) to just those records that have checks (on the child forms) for the skills selected in the listboxes. I hope that makes it clearer.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying Multiple Subforms (XP)

    The table structure is bound to give you headaches.

    Instead of using a separate field for each skill, you should have a table in which each skill for a person is a separate record:

    <table border=1><td>PersonID</td><td>SkillID</td><td align=right>1</td><td align=right>37</td><td align=right>1</td><td align=right>155</td><td align=right>1</td><td align=right>382</td><td align=right>2</td><td align=right>211</td><td align=right>2</td><td align=right>403</td><td align=right>3</td><td align=right>211</td></table>
    This will require a major redesign, but it will make working with the date much easier in the long term.

Posting Permissions

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