I can't get ComboBox to filter query

Question

Firstly I am a new Access user - so please bear in mind when replying.

I have built a simple database which has 7 columns (including ID). I want to be able to search on these individually so I have set up a query for each one.

However, other than 1 column, I would like to be able to choose from a drop down menu to be able to select from a defined list and then search on that.

As such, I have followed instruction online and have created a blank form and on there created a ComboBox, which I have linked to the table to get inputs. Side problem, I can't get it to stop showing duplicates even when I click the 'unique inputs' tick box. In case it's relevant.

I then on the table have used [Forms]![Search]![Combo17] in Criteria under the relevant column to link it to Combobox.

On the Form I have then added a Command Button, which I have linked to Run Query. However the return query is always blank when it returns.

I have re-done all the links to make sure that I am referencing the correct locations. Plus checked the query work when using a text entry e.g [Enter Country] in the Criteria field. When you type it in it shows correctly.

What is going wrong that it doesn't link with the ComboBox?

Answer 1

Hi. Can you show us the SQL statement of the query you're trying to run from your button?

Answer 2

Also, what are the RowSource, BoundColumn, ColumnWidths, and ColumnCount properties of the combo box? If the RowSource property is the name of a query, what is the query's SQL statement?

Answer 3

The combo box's BoundColumn, and hence is value, is the hidden ID column, not the Country column. Consequently referencing it as a parameter will not give a match. You need to change the RowSource property to:

SELECT DISTINCT Country
FROM [Vendor Information]
ORDER BY Country;

The ColumnCount property should also be changed to 1, and the ColumnWidths property's value deleted.

Answer 4

Looks like there are commas before FROM and before ORDER. Try taking them out.

Answer 5

Seemed to of fixed it by added the required field using the method in this tutorial

https://www.techonthenet.com/access/tutorials/combobasics2010/basics08.php

It now returns search results