referring to a combo box column as criteria in a query

[Guest]

I am trying to select records based on the value of a combo box column. I am
using it as criteria in a query and referring to it as follows:
Forms!frmMain!subform1!combo25.column(0). However, when I attempt to run the
query I get the 'Undefined function in expression error'. I do not get the
error if I leave out the reference to the column but that restricts me to use
only the bound column in the combo box (and I went to use more than one
column as criteria in the query).
I suspect that this is a library references type error. However, I have
Microsoft DAO 3.6 Object library checked in References. Any suggestions
please ?

 

[Allen Browne]

In general the combo's bound column should be the one you are filtering on.
That means you can filter on the number, while actually seeing something
else in the combo (assuming its Bound Column is zero-width.)

If you can't do that for some reason, you could add the combo's RowSource
table to your query, so you can select based on the value in the field from
the lookup table. (You may need to use outer joins for when the combo is
blank.)

 

[OfficeDev18 via AccessMonster.com]

column(0) is where your trouble is. The ColumnCount property is a 1-based
array, not a 0-based array like some other properties. Change the (0) to (X)
where X = the bound column number.

HTH

 

[John Spencer]

Again, I disagree. The columns collection is zero-based. The first column
is column(0).

The problem is that you can't refer to the column by number in a query. The
query expression service doesn't handle this. Onesolution is to use a
hidden text control on the form to hold the value of column(n) and then
refer to that control.

Another solution is to write a custom function to allow you to get the value
of the column if you pass it the control and the column number

 

[Guest]

Again, I disagree. The columns collection is zero-based. The first column
is column(0).

The problem is that you can't refer to the column by number in a query. The
query expression service doesn't handle this. Onesolution is to use a
hidden text control on the form to hold the value of column(n) and then
refer to that control.

Another solution is to write a custom function to allow you to get the value
of the column if you pass it the control and the column number

 

[Guest]

Many thanks for your responses. I thought I had successfully used
combobox.column as criteria in a query before (long time ago) but clearly
that could not have been the case. The suggested hidden text control method
should provide the solution I need. Your help is much appreciated.

Regards
trevorbee