Dependent Combo Boxes With a TWIST

[Jables]

I have a form that has two combo boxes. The user selects a street intersection with them. Whenever the user chooses the primary street from the drop-down list, the secondary street combo box becomes enabled and it's drop down lists displays only cross streets of the previously selected Primary Street.

Say you have two (or more intersections)

Cantrell & Mississippi
Cantrell & Kavanaugh
etc.

Where Cantrell is the Primary Street in both intersections. Now, whenever the user chooses the Primary Street combo box, "Cantrell" appears twice because it is listed twice in the table field from which the query (the one that controls the combo box) gets it.

How do I keep this from happening? That is, if Cantrell is the Primary Street for more than one intersection, how do I get it to appear only once in the Primary Street Combo Box?

Any help will be returned with heaps of cash...Or maybe just a warm thank you.

 

[tempclerk]

In your query properties set Unique Values to yes. In the SQL this would be SELECT DISTINCT ...

 

[Jables]

Awesome. Thanks so much. I knew there had to be a simple solution.

 

[HonestTobes]

Here is what I did. I'm not saying it's elegant.

Make a table "streetlist" separate from the table you're using and link it to your table wih a one to many relationship. make "streetlist" the rowsource for your combo box. This will be a single column table with the names of your streets. Index the names and make it a primary key and set unique records property to yes.

Make a query with your original table field and set the unique value property (right-click on tables window for properties)to yes. Run the query. Copy and paste into the streetlist table.

Hope this helps.

 

[Jables]

Thanks for trying to help. Not sure this solution would work though, because the list of Primary Streets and Secondary Streets can change when the user edits them. I wouldn't want to have to copy and past the results of a query everytime somebody updated a record or added a new one.

It's easier just to build a query of the original table that selects distinct values from the Primary Street column and then set this query = to the rowsource for the primary street combo box. I should point out that there are separate forms for entering new data and choosing an intersection to edit existing data.

Anyway, thanks again for trying to help.