Reference a ComboBox Column in a Query 1

[BlakeK]

If I want to reference the bound column of a combo box in the criteria of a query, I put the following in the criteria:
[Forms]![form_name]![cbx_name]

Now what if I want to refernce another column of that Combo Box?

I put the following:
[Forms]![form_name]![cbx_name].Column(2)

This is how I would reference it in VBA, but trying to put that in a query results in an error:
"Undefined funtion '[Forms]![form_name]![cbx_name].Column' in expression"

Help! Thanks.

 

[PHV]

You may use an hidden textbox in your form and update it with the Column value just before launching the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BlakeK]

Yeah, I thought about that, but it's a hack.
Does that mean there is not proper way to access that data directly in a query?

Thanks.

 

[PHV]

Another way is to write your own function to grab the column value, something like this:
Public Function getComboCol(strForm, strCombo, intCol)
getComboCol = Forms(strForm)(strCombo).Column(intCol)
End Function
And in the criteria you put this:
=getComboCol("form_name","cbx_name",2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

You can use something like:
=Eval("[Forms]![form_name]![cbx_name].Column(2)")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TheAceMan1]

How are ya BlakeK . . . . .

The problem is that the [blue]Column Property[/blue] is only available to a [blue]Macro[/blue] or [blue]VBA[/blue] at runtime. Not as criteria in query. This is the reason for the error!

With that, [blue]PHV's[/blue] function call is the way to go. . . . .

See Ya! . . . . . .