Solved Cascading ComboBoxes (1 Viewer)

depawl52

New member
Local time
Yesterday, 20:18
Joined
Feb 8, 2022
Messages
14
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

Active member
Local time
Yesterday, 20:18
Joined
Mar 28, 2020
Messages
609
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

AWF VIP
Local time
Yesterday, 16:18
Joined
Mar 9, 2014
Messages
4,376
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

New member
Local time
Yesterday, 20:18
Joined
Feb 8, 2022
Messages
14
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.
 

Attachments

  • TestDB.accdb
    496 KB · Views: 73

Mike Krailo

Active member
Local time
Yesterday, 20:18
Joined
Mar 28, 2020
Messages
609
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

Active member
Local time
Yesterday, 20:18
Joined
Mar 28, 2020
Messages
609
OK, here is an example using filters.
 

Attachments

  • TestDB_withFilters.accdb
    508 KB · Views: 175

depawl52

New member
Local time
Yesterday, 20:18
Joined
Feb 8, 2022
Messages
14
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 .
 

Users who are viewing this thread

Top Bottom