Dependent Combo Boxes

[Tracyice]

We have 2 fields (combo boxes) in a form which use look-up tables. Based on a selection from the first field, we would like the available selections in the second field to change.

Ex.

Field 1 : Option 1, Option 2
If Option 1 is selected,
Field 2 shows : Option A, Option B, Option C
If Option 2 is selected,
Field 2 shows : Option D, Option E, Option F

Is there a way to do this using Macros or VBA?

 

[batteam]

Try something like this:

Field 1_click()
Select Case Field 1
Case Option 1 '''or whatever the bound column vaue is
Field 2.RowSourceType = "Value List"
Field 2.RowSource = "Option A;Option B;Option C;"
Case Option 2
Field 2.RowSourceType = "Value List"
Field 2.RowSource = "Option D;Option E;Option F;"
End Select
Field 2.Requery '''very important.

End Sub

Hope this steers you in the right direction. Good luck.

 

[Tracyice]

I tried your advice and i'm getting a compile error which says "data member not found" for rowsourcetype and rowsource. This is the code I used...

Private Sub Program_Click()
Select Case Program
Case Program1
Course.RowSourceType = "Table"
Course.RowSource = "Prog1Courses"
Case Program2
Course.RowSourceType = "Table"
Course.RowSource = "Prog2Courses"
Case Program3
Course.RowSourceType = "Table"
Course.RowSource = "Prog3Courses"
Case Program4
Course.RowSourceType = "Table"
Course.RowSource = "Prog3Courses"
End Select
Course.Requery
End Sub

 

[randy700]

The row source type is "Table/Query", not "Table".

Is each set of courses held in a separate table? Or are you attempting to refer to a field in the table that contains all of the courses?



Randy

 

[JJman]

We have several instances of this in our database.
Simply use an expression as a field in the SQL for the second combo box that references the first one as it's criteria. Then, on the afterupdate event of the first combo box, requery the second.

Como1 afterupdate event to requery combo2

Combo2 SQL; Field = [forms!][formname]![combo1], Criteria = [table form is based on].[field of combo1]

Make sense?

 

[Tracyice]

In response to Randy, yes, each set of courses are in a different table. So, that SQL statement from JJman won't work unless all of the courses are in one table, right?

Thanks for all the help!

 

[randy700]

Since you are always using a table as the record source, there is no need to declare the row source type every time. I would simply create the combo box using any one of the tables as the row source, then change just that property when you make a selection from the other combo box. Also, I'd put it in the After_Update event......

Private Sub Program_AfterUpdate()
Select Case Program
Case Program1
Course.RowSource = "Prog1Courses"
Case Program2
Course.RowSource = "Prog2Courses"
etc
End Select
End Sub

This makes the following assumptions....
..the names of the combo boxes are Program and Course
..the names of the tables are Prog1Courses, Prog2Courses, etc

if either assumption is incorrect, change the code accordingly. You might want to consider using standard naming conventions to distinguish the difference between a control, table, query, field name, etc. I've found that, while Access will allow you to give two object identical names, it can become confusing to you and Access.


Randy

 

[Tracyice]

All of your assumptions were correct. Sorry, if it wasn't clear...

Private Sub Program_AfterUpdate()
Select Case cboxProgram
Case Program1
cboxCourse.RowSource = "tblProg1Courses"
Case Program2
cboxCourse.RowSource = "tblProg2Courses"
etc
End Select
End Sub

This leaves me with no options to select in the combo box Course.

 

[randy700]

Sorry....

cboxCourse.RowSource = "SELECT Field FROM tblProg1Courses"

where Field is the field name you want pulled from the table.


Randy

 

[Tracyice]

When I set the rowsource for the course combo box as one of the tables, the choices stay the same no matter what is selected in the program combo box. It seems to be ignorning the after_update procedure and just using the row source for the table I chose to set it to.

 

[PHV]

At the end of the Program_AfterUpdate procedure put this:
cboxCourse.ReQuery

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tracyice]

I did have the requery command in the entire time, but it still keeps the same options in cboxcourse(2nd combo box), no matter what I select in the cboxprogram (1st combo box). Thanks for trying!

 

[randy700]

Time to post your code so we can see what's happening here.

Randy

 

[KenReay]

Hi

Have you looked at the FAq on this subject, in this forum?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tracyice]

I am working on this further. I will Post on Monday if I'm still stuck!