Using combo box as criteria in query not working!!

[sashapixie]

Hi there,

I have created a form with a combo box which looks up a list of course names in a table, I have a criteria set in a query to filter the query by course title how ever this is not working I have tried to type in the combo box but this does not work. Any ideas what the issue is?

The criteria is as follows [Forms]![Training Certificates Course Selector]![Combo 5]

This is the name and location of the combo box.

Any help would be appreciated.

Thanks

 

[Joe4]

If the name of your Combo Box really "Combo 5"?
Usually, when Access creates combo boxes and automatically names them, it does not use spaces in the names (i.e. "Combo5").
Also, if your Form open and combo box selection made when you try to run the Query?

 

[sashapixie]

Sorry it is called Combo5 no spaces, yes the query and the form are open when I run the query.

 

[Joe4]

Can you post the SQL code of your query?

Also, make sure that you have placed the criteria under the correct field, and the values match EXACTLY what is in your table (extra spaces in one but not the other can cause non-matches).

One check you may want to do is temporarily replace your Criteria reference with the hard-coded value that you are trying to select from the Combo Box, to make sure that your query is written properly.

 

[sashapixie]

SELECT [Staff Training].ID, [Staff Training].[Full Name], [Training Courses].[Training Course], [Completion Date]+[Training Courses]![Validation Period]*365 AS [Expiry Date], [Staff Training].Certificate
FROM [Staff Records] INNER JOIN ([Training Courses] INNER JOIN [Staff Training] ON [Training Courses].ID = [Staff Training].[Training Course Selector]) ON [Staff Records].ID = [Staff Training].[Full Name]
WHERE ((([Training Courses].[Training Course])=[Forms]![Training Certificates Course Selector]![Combo5]) AND (([Staff Training].Certificate)=True));

The criteria is under the Training Couse Field which has the course titles in, these are also taken from a table where the combo box is looking up, therefore they should all match.

I have already done the check you mention and this works fine, which is why I think the issue is with the criteria selection or the combo box itself even though it is displaying the course titles in a list as I want.

 

[Joe4]

So, if you remove that one criteria from you query, it returns records, right?

[COLOR=#333333]SELECT [Staff Training].ID, [Staff Training].[Full Name], [Training Courses].[Training Course], [Completion Date]+[Training Courses]![Validation Period]*365 AS [Expiry Date], [Staff Training].Certificate[/COLOR]
[COLOR=#333333]FROM [Staff Records] INNER JOIN ([Training Courses] INNER JOIN [Staff Training] ON [Training Courses].ID = [Staff Training].[Training Course Selector]) ON [Staff Records].ID = [Staff Training].[Full Name][/COLOR]
[COLOR=#333333]WHERE ([Staff Training].Certificate=True);[/COLOR]

And if you look at this list of records, the one you are trying to select from the Combo Box exists there (in this particular list), right?

Two other things to check:
1. In the Combo Box on your Form, if you look at the Properties, what does it list as the Column Count?
2. Your Training Course field in your Table, what Data Type is it? What is its Display Control (see Properties -> Lookup in your Table)?

 

[strive4peace]

shouldn't the criteria be using the ID field for your training course instead of "WHERE ((([Training Courses].[Training Course]) =..." ?

 

[sashapixie]

Yes when I remove the criteria I get records, when I put the criteria in it returns no records.

Yes I have checked I am selecting a course that appears in the list on my query.

The column count on the combo box is 2, the data type for the training course field in the table is short text the display control is text box.

 

[Joe4]

The column count on the combo box is 2

That could be the issue right there!

Why are there two columns in your Combo Box?
You may unknowingly be using the "other" column in the Combo Box in your Criteria.

What is the other column of the Combo Box?
Which is the Bound column (1st or 2nd)?

 

[sashapixie]

ID and Training Course, the bound column is set to column 1, this is the ID column but if I change this to 2 I don't get anything in my drop down list.