referring to a combo box column as criteria in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

trevorbee - 01 Dec 2005 07:54 GMT

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 ?

Reply to this Message

Allen Browne - 01 Dec 2005 08:29 GMT

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.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am trying to select records based on the value of a combo box column. I
>am
[quoted text clipped - 9 lines]
> Microsoft DAO 3.6 Object library checked in References. Any suggestions
> please?

Reply to this Message

OfficeDev18 - 01 Dec 2005 14:56 GMT

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

>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:
[quoted text clipped - 6 lines]
>Microsoft DAO 3.6 Object library checked in References. Any suggestions
>please ?

Signature

Sam

Reply to this Message

John Spencer - 01 Dec 2005 16:06 GMT

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

> 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
[quoted text clipped - 16 lines]
>>Microsoft DAO 3.6 Object library checked in References. Any suggestions
>>please ?

Reply to this Message

trevorbee - 01 Dec 2005 22:55 GMT

> Again, I disagree. The columns collection is zero-based. The first column
> is column(0).
[quoted text clipped - 27 lines]
> >>Microsoft DAO 3.6 Object library checked in References. Any suggestions
> >>please ?

Reply to this Message

trevorbee - 01 Dec 2005 23:01 GMT

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

Reply to this Message