Search code examples
sqlms-access

How come my combo box in access is either showing duplicates or no values at all?

Terms:

[main checks].ID is the auto number generated for every row added

[main checks].Account is the field I want the combo box to take unique values on

When I use the combo box wizard, this is what it generates:

SELECT [main checks].ID, [main checks].Account
FROM [main checks];

Combo Box Wizard

So I tried using what I found on previous questions here as well as from google where I should add SELECT DISTINCT to the SQL query as one option. The other option is to go to the "Row Source" then in the query builder, I make "Unique Values" from "No" to "Yes". I believe this does the same thing because the SQL query automatically adds the SELECT DISTINCT portion.

The revised code is shown below:

SELECT DISTINCT [main checks].ID, [main checks].Account
FROM [main checks];

Same issue As can be seen the combo box still shows duplicates and does not resolve the issue.

My third attempt is to remove the [main checks].ID portion of the SQL statement and just leave the [main checks].Account portion. This makes me combo box blank I don't know why. The code is shown below:

SELECT DISTINCT [main checks].Account
FROM [main checks];

Blank combo box

Screenshot of my Table with values: Table

Below is my property sheet of the combo box for the events:

enter image description here

Data Tab of property sheet:

enter image description here

Format Tab:

enter image description here

Solution

  • Using the existing values as Dropdown content is a valid approach to e.g. avoid spelling mistakes for free-text fields.

    But you can't use the ID in that SQL, that makes each row unique.
    Use this:

    SELECT DISTINCT [main checks].Account
FROM [main checks];

    and change the combo box properties to Column count = 1, and adapt the Column widths to a single value (or empty).