Solved Cascading ComboBoxes

[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.

 

[KarenWilfong]

OK, here is an example using filters.

I'm so sorry, I realize this is an old post, but it describes the exact problem I am having. I downloaded your example filters and tried to figure out how to use it with only 2 cascading fields but I can not sort it out. My situation isn't with states and cities, but that represents a good example of what I need to do.

The 2 fieldnames I want to filter on (within the header of my single item form) are VAR_ID and size_name. The table name is TBLonesies. I need to make 2 lookup fields that first select a VAR_ID, then based on that, it will show the size_names that are within that VAR_ID. Then I can select a size and view all the data for that size. If I add a new size, it needs to requery to add the new size to the combobox.

I do have all the size names in a combobox field name based on a size_name table to choose the new size I want to add. So the lookup is simply that, to look up the values I have already added.

It seems like a simple task for an experienced person like yourself, but very difficult for me since I know only basic Access, sql and VB.

This is a personal project for my own business. I sell on Amazon and need to update older listings. I already have the form set up and have successfully created 2 listings, but I'm struggling with these comboboxes and doing a lot of unnecessary manual tasks with the form to display the correct VAR_ID set.

Would it be too much to ask if you could make an example for me with only 2 cascading lookup fields?

Warm regards. Karen

 

[Gasman]

Try here.

Cascading Combos