Combo Box Relationships

Let me start off by saying that I am very new to access. I actually just started last night to teach myself heuristically how to create a form for work. I have these two combo boxes; one is linked to a field of names of lakes of the state of Maine, the other is linked to a field of lake codes(WATCODES) developed by the state to help catalog the lakes. This is what I want to happen.... I am entering data off of scales envelopes, and the lake names are on the scale envelopes but the WATCODE is not. So when I enter the lake name I want the WATCODE combo box to automatically fill itself with the associated code for that lake. Does anybody think that is within my very green access abilities?

First up welcome to the forum.

What you are looking for is a Cascading Combo box set. You will find plenty more info on the subject if you search the forum on the subject.

Thank you. Maybe now that I know what Im looking for I can get it.

Wow. That's pretty beyond me. I don't know anything about coding. Thanks anyway.

OK here's a very simple sample which you should be able to implement, it has limited coding requirements.

Here's how it works;

On you form create your first Combo Box, we'll call that Combo1 (for simplicity) this will populated by your Lakes table.

Now save your form and create a query we'll call that Query1, now this query will use the Lakes table and your lakes code table (linked via a suitable ID), now in the criteria under your lakes ID put;
Now when your form is open this will select only the records related to the selection in Combo1. Save your query.

Now put your form back into design view and create your second combo box, we'll call that one Combo2, now when you get to the point in the combo box wizard where it asks you which table or query should provide the values for this combo, click the queries radio button and select Query1. Then select the fields you want in your combo box. When you get to the step where the wizard is showing your the available columns you will notice that they will all have #Name? don't worry about this as it is due to the fact that the query can not currently work as your form is in design view. Simply adjust your column widths as required and continue to the end of the wizard.

Now with the form still in design view click on Combo1 and Select it's On Change event, select Event procedure from the list and click on the button with the three full stops on it (ellipsis) in the code window that opens put the following code;
This piece of code will requery the row source for Combo2 whenever there is a change in Combo1. You will also need to put the same code in the Form's On Current event (so as to ensure that the two combos are synchronised when you view each record), you can find this by clicking in the top right hand corner of the form (whilst it is in design view) between the vertical and horizontal rulers.

Save your form and you should be in business. Remember to change form and object names to match those in your DB

Thanks for the work around. I got it working with this coding.
Private Sub Water_AfterUpdate()
On Error Resume Next
WATCODE.RowSource = "Select watcodesF.WATCODE " & _
"FROM watcodesF " & _
"WHERE watcodesF.WATER = '" & Water.Value & "' " & _
"ORDER BY watcodesF.WATCODES;"
End Sub
Private Sub WATCODE_OnClick()
On Error Resume Next
Town.RowSource = "Select watcodesF.TOWN " & _
"FROM watcodesF " & _
"WHERE watcodesF.WATCODE = '" & WATCODE.Value & "' " & _
"ORDER BY watcodesF.TOWN;"
End Sub

Or something to that extent. I don't have the database here in front of me. I haven't figgured out how to get it to make the selection automatically for me, but narrowing it down to one selection from a list of 1200 is good enough for my purposes. Pretty amazing program.

joshua.kuester said:

........ I haven't figgured out how to get it to make the selection automatically for me, .........

Click to expand...

Have a look at this article.