Using two combo boxes to search in a subform (1 Viewer)

[themurph2000]

I'm sure this has been done before, but I can't seem to find it.

I have three tables:
1. A list of training courses (Course ID is primary)
2. A list of dates and times for each course (Session ID is Primary and is linked to the one above it through the Course ID)
3. A list of students in one particular session of a course (linked by the Session ID

After 6 hours of tearing my hair out, I sort of went back to the beginning and built forms for the tables one by one. First, I did a datasheet form for table 3 above. That was inserted as a subform into a form for the second table. Then the form for the 2nd table was inserted as a subform for the primary table (the training courses in the first place)

Now all of this works OK, until I decided to be cute and try to make search combo boxes to select a specific course, which filters the second combo box to look for a course's dates and times. What I can't link is the result from the second search combo box (that has the dates, times, and a session ID) to the roster, which contains the same session ID for a group of students. Basically, the two combo boxes work, but the data doesn't filter down.

Also, in the main form, is the Page Header for the form blocked out when you use subforms in the main form?

Any suggestions?

Thanks.

 

[Aeon.Divine]

What you should be doing is to set up a form where the first combobox load its values from the first table, a second combobox loading its values from a query using the first combobox as filtering criteria for the second table, to this pointh i think you got it correctly. But then, where are you seeing the result of this second combobox selection ?

 

[themurph2000]

What you should be doing is to set up a form where the first combobox load its values from the first table, a second combobox loading its values from a query using the first combobox as filtering criteria for the second table, to this pointh i think you got it correctly. But then, where are you seeing the result of this second combobox selection ?

Unfortunately, now I've screwed this thing up beyond belief. I'll try your suggestion, though: use the actual table for the main form and a query result for the subform. The biggest problems I had were:

1. The combo box would work fine for searching the main table. (easiest part, right?) But then when it came time to set up the second combo box to use values from the subform (date and time in this case), I wasn't getting the proper displays.

2. Once the value was selected from the second combo box, it wasn't being passed on to the subform underneath. (which has a subform datasheet on it for the students in each session of the class)

 

[Aeon.Divine]

I'm that a little bit more complex, so you are trying to take values from the subform onto the combobox. Isn't there a way to avoid the subform extraction ? Theres always a way to make it work, but most of the time, it's better to go the easier way, maybe you arent seeing it yet. Are you sure you have to take the values from the subform ? Cant you take them from another filtered query ? Maybe that could fix it.

Besides, maybe im missunderstanding, but, are you working con the same form to input information and to search it ? If you are, you definitively should make a new form for the searching, and then, to have information extracted from a form wouldnt be so necesary.

About the subtable not responding, did you considered testing just that on an example database ? Create a brand new database with just a couple of tables, a form with a subform and a combobox, and try to make the table respond to the combobox. Programming is always by little steps when youre facing something new, it avoids lots of mistakes.

 

[themurph2000]

I'm that a little bit more complex, so you are trying to take values from the subform onto the combobox. Isn't there a way to avoid the subform extraction ? Theres always a way to make it work, but most of the time, it's better to go the easier way, maybe you arent seeing it yet. Are you sure you have to take the values from the subform ? Cant you take them from another filtered query ? Maybe that could fix it.

Besides, maybe im missunderstanding, but, are you working con the same form to input information and to search it ? If you are, you definitively should make a new form for the searching, and then, to have information extracted from a form wouldnt be so necesary.

Basically, that's what I'm doing now. I'll keep the data entry for the original class and adding to a list of dates and times for each class separate from adding the students to the class. I got the search to work by just dealing with the subform and the roster subtable on their own.

Thanks for your help