Solved Cascading ComboBoxes (1 Viewer)

[depawl52]

Greetings all. I have a Customer database with almost 20,000 records. There is a form with 3 cascading combo boxes to select the State, City, and Address in order to locate a specific customer. All the comboboxes work correctly, the only issue I have is that with this many records, some of the customers actually have identical street addresses (for example: 123 Main St.). So when I select an address from the 3rd combobox (which is limited to addresses in the selected city (2nd combobox)), it apparently selects the first address it comes to in the database, which may or may not be the correct customer I'm looking for. So I'm wondering how to best address this?
If need be I can upload a minimized/redacted database to further describe what I'm talking about.

As always, thanks for the expertise.

 

[Mike Krailo]

The combo boxes should simply filter your records down to those records that have the criteria selected and not actually select a record. It doesn't matter if the street addresses are the same or not because the primary key is unique for each Customer. After filtering the records with your combo's, you select the one you need from the continuous form or cycle through the records if it is a single view form. You can also use whatever search capabilities you have built in to further search for a particular record.

 

[June7]

Maybe list customers instead of addresses in third combobox. At least Include customer name/number info in the Address combobox. This can be a calculated field that concatenates fields and/or has the additional info as separate columns that can be viewed in the dropdown list.

 

[depawl52]

Thank you for the input to date, which is always appreciated. I've uploaded a small sample database as an example of the issue I'm having. Due to requirements set by others I am unable to make any changes to the layout or format of the combo boxes on the "Customers" Form. So as you can see, if you select "California" as the state and "Woodland Hills" as the city, the address "1019 Buchanan Rd." appears in the Address combobox, but if you select it, the record that address in Rochester, New York gets selected in the Form.

 

[Mike Krailo]

Your not filtering the records at all. That is not how I would do this. The code in the combo boxes is simply selecting a single record as best it can. It would be much better to filter all the records based on the combo box selections and then pick which record you want from the form. Give me a minute.

 

[Mike Krailo]

OK, here is an example using filters.

 

[depawl52]

Thank you Mike. Your example appears to work perfectly. I haven't had time to import it into my production database yet but I
have no doubt it will be fine.
Thanks again .

 

[Mike Krailo]

Your welcome.