Referencing a control (drop down) in a query

[CJW]

Hi Folks,

tearing what little hair I have out with this..

I have a form (community_courses) with a combo box upon it (course_desc) that I am trying to reference in a query based upon the value in the combo box when the form is open but then I have read that one cannot reference a combo box value in a query. I have tried creating a unbound text box (CD_refbox) that references the combo box value can be used but that's not working either but the text box does show the combo box value.

I have checked that my syntax is along the right lines by referencing a static text box with value and that works ok but now run out of ideas :-(

My query syntax is as follows:-

[forms]![community_courses]![CDref_box]

I also tried adding .Column(1) to the end but the syntax is not accepted.

Any tips would really be appreciated!

Thank-you,

CJW

 

[bastanu]

might not be the case, but check your spelling, first you say the text box is called CD_Refbox but in the query you use CDref_box.

Cheers,
Vlad

 

[Minty]

You can reference a combo in a query but only the bound column value, you can't refer to a specific column(..), also be aware that column numbering in combo's starts at 0.

So
[forms]![community_courses]![course_desc]
should work.

If you create a simple query and add Expr1: [forms]![community_courses]![course_desc] and run it with the form open you should see the result.

 

[Pat Hartman]

Combo boxes have a bound field. This is usually the first column in the combo's RowSource query/table. Typically this field is numeric and hidden by setting the column width of the first column to 0. The visible field in the control will be the first field with a non-zero width.

When you reference a control from the query, the expectation is that you are referencing the bound control and it is the first column.

Since I never deviate from this, I don't know what happens when you do so start with that.
1. You want the bound field
2. The bound field is the first field in the RowSource query.

 

[sxschech]

I was having a similar experience and as the other's said, you can use a combobox, but only the bound col. An alternative, if you need to get data from another col, if you have ruled out spelling error/typos is to generate the query in vba. I think you can refer to the combo box using this method, otherwise, should work with a text box on the form.

Since i don't know your field/query name this is an example:

stsql ="SELECT * From YourQuery WHERE YourField='" & [forms]![community_courses]![CDref_box].Column(1) & "'"

This will translate to something like:

SELECT *
FROM YourQuery
WHERE YourField='XYZ'

Of course there is more to it than that, but depends on the type of query it is such as make table, update, delete, etc. Can provide more to the code when we know what will be done/used the query with/for.

 

[Pat Hartman]

sxschech - that is correct. You can use VBA to build the SQL string. However, in a WHERE clause why would you ever be looking at anything other than the foreign key unless your table is incorrectly designed?

 

[CJW]

might not be the case, but check your spelling, first you say the text box is called CD_Refbox but in the query you use CDref_box.

Cheers,
Vlad

Many thanks both, yes it was the spelling after all - must have been query blindness!!

 

[sxschech]

Hi Pat,

Perhaps my example query was bad, was trying to interpret what OP was trying to do with getting data from a combo box that is not the bound col. Didn't realize would lead down the path of

your table is incorrectly designed

In my actual case I was not using it in a where clause, rather I was obtaining a date value that was in the combo box in order to add it to an append query so that I could import csv data and use the date in my log table in the detail table since the imported file does not contain dates and thus be able to join back to a status table by code and date.