Help w/ Combo Boxes

[dbnewbie]

Hi,

I have attached a small version of my database/form. I am trying to display only those records in a subform based on the values selected from two combo boxes. So when the user selects a client from the 1st combo box, the 2nd combo box is populated with the tests that pertain to the client selected. When the user selects a test from the 2nd combo box, I want the records to update with only those records that have the test for that client. So if there is no record for that test, then no records should be displayed (even though they may have records for another test - not sure if that made sense...).

My whole purpose is to allow the user to select a test only once and only have to select the level list based on however many levels are available for that test (I want it to be simple for the user).

Example: Open up my form, select Alabama from the clients list.
The subform has all of the tests for the client and the levels. Select Physics from the tests list. I now want the subform to display only the records were Test=Physics.
Once I get that working, I will make ClientID and Test in the subform not visible (only the Level drop down list will be displayed). So when a user selects a level from the list, the table needs to get updated with all of the information (clientID, testID, levelID).

Have any suggestions? Any help will be much appreciated.

Thanks,
dbnewbie

 

[godofhell]

dbnewbie, I have modified your database and added the functionality you wanted. Now when you select a Client the subform will show all the Tests for the client and the Tests dropdown list will show all the classes for that client. When you select a Test from the Tests dropdown list the subform will update to show only the Test you selected. Noticed that I changed the recordsource for the subform to a query and added the sql statement to the Tests drop down list in the properties instead of doing it from code.

 

[dbnewbie]

You're awesome!! Thanks I like how you used a qry for the subform.

The next thing I want to do is have it so that the user no longer sees ClientID and Test in the subform (set Visible to 'No'). So the user should be able to select a Level (after already selecting client/test) and the other fields should automatically be populated with the ClientID from the client combo box and the TestID from the test combo box. And of course, the table will update with this information.

I'm going to see if I can figure this out, but if you have an ideas that would be great

Thanks,
dbnewbie

 

[dbnewbie]

Well, I figured it out

For the AfterUpdate method for Level, I put Forms!Form![Tests_Clients_PerfLabels_new Subform]!TestID.Value = Forms![Form]!TestsCombo.Value.

This thread helped me out
http://www.access-programmers.co.uk/forums/showthread.php?t=86929&highlight=table+update