multiple combo boxes that are dependent on each other need unique values

[jss787]

Let me start by saying this is my first database. The problem I am having is I have 7 combo boxes on a form that are dependent on each other and only two of them are diplaying unique values. The rest of them display duplicate values. I need all the combo boxes to have unique values. It works perfectly if I choose all the combo boxes in order. I have it so you can the combo boxes blank for a bigger search result. The search still works if you leave drop down boxes blank and choose one of the duplicate values in the other combo boxes. I just want to get rid of all the duplicate values. I have tried using distinct on the row source but that only worked for one of the combo boxes. Any help would be appreciated!

[GKJR]

What do you mean by "only two of them are displaying unique values"?
Is the form bound to a record source, or is it an unbound form you are using to create a filter for another form or report?
Also, are these combo boxes bound or unbound? (in other words do they have a control source?)

[jss787]

For example, the two that are working have only one a,b,c,d in each. The others have a,a,a,a,b,b,b,b ,c,c,c,c,d,d,d, d. But if I choose the boxes in order the next box doesn't display duplicates. It's only when I want to leave the above boxes blank (meaning I want to see all the associated options). The combo boxes are unbound and each combo has a table of the drop down items which then searches the master record table for matches.

[twinnyfo]

First, why do you have duplicate items in a Combo Box?

Second,

But if I choose the boxes in order the next box doesn't display duplicates.

How are you making this work? You mention nothing about any code updating the Row source for these combo boxes, so that might help us, too.

It is still entirely unclear what you are asking here.

[GKJR]

I'm still really confused as to why the list changes after you make selections in other combo boxes, but I think I know why you have duplicate values. Check to see if your row source is set to display a foreign key field in the child table of a one to many relationship. I've run into this issue many times over the past few years. If it is then just change it to the primary key (or identifying field) in the master table. Hope this helps.

[jss787]

I wanted them to change because they are dependent on each other. For example, if I wanted all of one particular series the next combo box is model I didn't want other series models showing up in that combo box. Same goes for the other boxes for product variables.

This is the row source SQL that I have in one of the combo boxes

SELECT DISTINCT [T-Connection Configurations-Drop Down].[Connection Config], [T-DPF Records].[DPF Model]
FROM [T-Connection Configurations-Drop Down] INNER JOIN [T-DPF Records] ON [T-Connection Configurations-Drop Down].[Connection Config] = [T-DPF Records].[Connection Config]
WHERE ((([T-DPF Records].[DPF Model])=[Forms]![F-Find_DPF_Models_Filtering_Criteria]![cboDPFModel])) OR ((([Forms]![F-Find_DPF_Models_Filtering_Criteria]![cboDPFModel]) Is Null))
ORDER BY [T-Connection Configurations-Drop Down].[Connection Config];

I want to be able to skip the above combo boxes and have it default to all records; but when I do this the combo boxes show the same item a,a,a,a,b,b,b,b ,c,c,c,c,d,d,d, d for however many associate records have that criteria. It should show up as a,b,c,d instead of duplicates. But the first two combo boxes don't have the same duplicate problems and show up like they should.

I'm learning as I'm going so I apologize if I'm not being clear. I've attached some pictures to maybe help explain.

[zmbd]

Sounds like you're attempting cascading combobox...
Please read thru the following and see if it helps.
Do not worry if you don't understand everything in the article, we're here to help walk you thru it:
(there are two articles here)

1. Cascaded Form Filtering
2. Multiple Combobox Filter with Clear Option

[jss787]

zmbd-

Yes, that seems like exactly what I am trying to do. I don't understand how to do it that way in VB. Right now each box has an associated row source SQL; which was autogenerated by the row source query I created for each box. Should I get rid of those? I have some VB code started that requeries the boxes after updates and some button codes. Could you walk me through how to do it? I'm guessing once one box is done the rest of the code is very similar with just name changes?

[zmbd]

please read thru the first linked article. it's fairly straight forward and should have you running in a jiffy. If there is something specific in the article you are not following, post that question in this thread for now.

[jss787]

What does this mean?

Private Const conDateSource As String

It's the first line of code in chapter 5. I don't have any date combos but do require the distinct part. I'm still learning how to use code and what it means.

[zmbd]

Neopa uses a constant here as a template with which to build the record sources for each of the cascading controls.

Follow thru the code and you will see the first such use on line 56. It's a pretty slick method of writing the strings while avoiding some of the quote/double-quote issues.