referencing columns in combo box in query designed with query builder

[stevenc2]

my first post in tek-tips, my apologies for any inadvertant protocol violations.

i'm running access 97. i have a form which contains a combo box with two columns. at a certain point, i'd like to reference, in a query i'm designing with query builder, both of the fields in the combo box. if i just try using the reference:
[Forms]![formname]![comboboxname]
i am able to successfully retrieve the first(0) column in the combo box.

if i then use the reference
[Forms]![formname]![comboboxname].Column(0) or
[Forms]![formname]![comboboxname].Column(1)
then i get the error message:

Undefined function '[Forms]![formname[comboboxname].Column' in expression

what am i doing wrong here?
thanks in advance,
s.

the wild geese do not intend to cast their shadow
the water has no mind to receive their image

 

[dhookom]

You can't reference a column of a list or combo box in the criteria of a query. I usually place a text box (possibly invisible) next to the combo box with a control source like
=Combobox.Column(1). THen use this text box in your query. I understand that it might also work as:
Eval("[Forms]![formname]![comboboxname].Column(0)&quot

Duane
MS Access MVP

 

[stevenc2]

a fine suggestion, thanks.
s.

 

[scriverb]

This is from ACCESS Help on the Column Property of a ComboBox:
This property setting is only available by using a macro or Visual Basic.

So, this means that you can't access it in a query for your criteria statement but there is a way around this. Create another text control on your form. Name it approrpriate to the field name of the second column or .column(1). Set the Visible property of this textbox to False and the TabStop to False. Also, make it quite small and stick it up in the corner someplace out of the way. In the AfterUpdate and the OnCurrent event procedures of your combobox put the following code updated with the approrpriate name of the new textbox.
Me![txtColumnTwoname] = Me![ComboBox].Column(1)

Now in your query you can reference this field successfully as if it were the .column(1) combobox reference.

Post back with questions if you have any.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

Bob,
Why introduce code where none is required? Just set the control source of the text box to:
=ComboBox.Column(1)
I love coding as much as anyone else but avoid making more maintenance and code then is necessary.

Duane
MS Access MVP

 

[scriverb]

I agree with you. I was posting at the same time as you and didn't see your suggestion before clicking submit. Same basic concept but yours is a little cleaner. Didn't mean to walk over yours.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???