MS Access combo box to match existing records if another combo box matches as well

Question

There is a StreetNumber field and a StreetName field in the same table. I would like to have the user start typing in either one of the fields, and when they see an existing record in the combo box drop down list, they can select it (this much I can do so far). Then, when they click on the other combo box, it will show all the existing records that also share the same information in the first combo box.

For example, with these records:

--------------------------------------
ID |StreetNumber |StreetName   |Category
1  |10           |Main Street  |House
2  |20           |Main Street  |Apartment
3  |10           |King Street  |Store
4  |11           |Queen Street |Factory
-----------------------------------

In the StreetNumber combobox when user types "1", it will show "10" and "11". User clicks on 10. In StreetName, user clicks on combo box drop down arrow, and it shows "Main Street" and "King Street" only. Would also like (but optional) it to work the other way as well; if the user types in "Main Street" first then the StreetNumber should show "10" and "20" in drop down.

Extra: If they then select "Main Street" in the combo box, ID=1 is selected and the Category field automatically populates itself with "House", and it updates ID=1 when saving instead of creating a new one. How can this be accomplished?

This seems like something that's probably been done before, but either I'm not using the correct search terms, or no one used Access for it.

Thanks for your assistance!

Johnny Bones Johnny Bones · Accepted Answer · 2015-09-25T14:16:35

The way this is normally done is in the AfterUpdate event of a combobox. Once the box is updated, it fires off a command that updates the source for the following combobox.

In theory, you could possibly use some sort of If/Then statement to check and see if the other combobox value is empty, and either update the source or not. The issue is, if you don't use an If/Then statement, then every time you choose something from either combo, it will refresh the other combo and you'll lose your selected value.

So, let's assume 2 combos; cboStreetNumber and cboStreetName. In the Form_Load() event you would load up both combos:

cboStreetNumber.RowSource = "SELECT distinct StreetNumber FROM MyTable ORDER BY StreetNumber"
cboStreetNumber.Requery
cboStreetName.RowSource = "SELECT distinct StreetName FROM MyTable ORDER BY StreetName"
cboStreetName.Requery

Then, for cboStreetNumber_AfterUpdate() put this:

If IsNull(cboStreetName.Value) Then
    cboStreetName.RowSource = "SELECT distinct StreetName FROM MyTable WHERE StreetNumber = '" & Me.cboStreetNumber.Value & "' ORDER BY StreetName"
    cboStreetName.Requery  
Else
    'No need to change the other combo, it's already been selected.
End If

and for cboStreetName_AfterUpdate() put this:

If IsNull(cboStreetNumber.Value) Then
    cboStreetNumber.RowSource = "SELECT distinct StreetNumber FROM MyTable WHERE StreetName = '" & Me.cboStreetName.Value & "' ORDER BY StreetNumber"
    cboStreetNumber.Requery  
Else
    'No need to change the other combo, it's already been selected.
End If

Now, this is entirely "aircode" and might need some tweaking, but it should be pretty close. The only thing I'll point out is that if your street numbers aren't stored as text, you don't need the single-quotes around the variable Me.StreetNumber.

The drawback is, if you've selected one of them you can't change it or it won't update the other box. You'd need a reset button or something, which is a bit clunky. And that's why cascading dropdowns don't normally work in both directions. ;o)

MS Access combo box to match existing records if another combo box matches as well

1 Answers