question

Decca2216-1260 avatar image
0 Votes"
Decca2216-1260 asked Decca2216-1260 commented

I can't get ComboBox to filter query

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.
113421-image.png

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?

office-access-dev
image.png (56.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered Decca2216-1260 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think this is what you are asking for?

![113761-image.png][1]


0 Votes 0 ·
image.png (45.3 KiB)
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered Decca2216-1260 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

113752-image.png


113782-image.png

Think these cover what you are asking for? I have put the SQL statement in the comments of the response above

0 Votes 0 ·
image.png (18.9 KiB)
image.png (8.4 KiB)
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered Decca2216-1260 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OK, so I changed the RowSource input and Column requirements as suggested, but just get the following error message

114621-image.png

This is what I have entered:

114574-image.png


0 Votes 0 ·
image.png (20.5 KiB)
image.png (22.6 KiB)
thedbguy avatar image
0 Votes"
thedbguy answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Decca2216-1260 avatar image
0 Votes"
Decca2216-1260 answered Decca2216-1260 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Congratulations! Glad to hear you got it sorted out. Ken and I were happy to assist. Good luck with your project.

0 Votes 0 ·

Thank you for your help. It was all going well until it suddenly told me there were other users in my database (?!) and now it can't open it at all :( Back to square one again, luckily what I am doing is simple

0 Votes 0 ·