need distinct values in multiple drop down boxes

[Jss787]

Hi,

I'm new to using Access. I have a form that has multiple combo boxes that filter out depending on criteria choosen in other combo boxes. The problem I am having is only one of the combo boxes shows distinct values and the others show multiple of the same values. I have distinct values choosen on each drop down box but for whatever reason it only works on one combo box. Any suggestions to fix this?

[orange]

Show us any related code.

[Jss787]

I have something like this in each combo box and it obviously changes depending on the combo box references:
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];

[Jss787]

The combo box code that works correctly looks like this:

SELECT DISTINCT [T-DPF Model List-Drop Down].[DPF Model], [T-DPF Records].Series
FROM [T-DPF Model List-Drop Down] INNER JOIN [T-DPF Records] ON [T-DPF Model List-Drop Down].[DPF Model] = [T-DPF Records].[DPF Model]
WHERE ((([T-DPF Records].Series)=[Forms]![F-Find_DPF_Models_Filtering_Criteria]![CboSeries])) OR ((([Forms]![F-Find_DPF_Models_Filtering_Criteria]![CboSeries]) Is Null));

[orange]

In plain English, what is the data base about? What exactly is the purpose of the combobox(es)?
Can you post a jpg of your tables and relationships?

[Jss787]

Attachment 19182Attachment 19183The purpose of the database is so people can search for products from a table based on the criteria they know they need. Each criteria combo box filters out the next but it can also be left blank if they don't know or don't care about that criteria. But I only want unique values in each combo. Right now the unique values work at the DPF Model Combo and will continue to work as long as you go in order. But if someone leaves a combo blank and skips to the next combo all the values are duplicated.

[Jss787]

any ideas?

[cbende2]

Bump, I'm curious to see a solution to this because I can definitely use something similar to this!

[Jss787]

I'm not sure why there are links and and also displayed pictures. But the displayed pictures is what I wanted. Here are a couple more pictures with what is going on with the drop downs. But if I choose every box in order it's fine with no duplicates.

[June7]

Need to review all of the queries and code. This will be much faster and easier if you provide the db.

[Jss787]

I have attached a copy of the database I'm creating for you to look at. Maybe it will help figure out what I am doing wrong. Thanks!

[June7]

You are not using values from designated PK fields as FK data. You are saving descriptive text. If the ID fields are not used in the PK/FK associations, should not be designated as PK. They aren't even required to be in the tables.

There is no need to include the lookup tables in the combobox RowSource if you don't need other fields. Use LIKE and wildcard to deal with empty comboboxes.


SELECT DISTINCT [DPF Model] FROM [T-DPF Records] WHERE Series LIKE [CboSeries] & "*";

SELECT DISTINCT [T-DPF Records].[Connection Config] FROM [T-DPF Records] WHERE ((([T-DPF Records].[DPF Model]) Like [cboDPFModel] & "*") AND (([T-DPF Records].Series) Like [cboSeries] & "*")) ORDER BY [T-DPF Records].[Connection Config];


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Jss787]

June7- Thank you soooo much!! That fixed it and now works like a charm! I really appreciate your help with this!