in my query criteria grid, can i reference a column in a combo box? 1

[ruthcali]

i am using Access97.

in my query criteria grid, can i reference a specific column in a combo box?

on my form (frmName) I have a combo box called cboName with 3 columns (the first one visible, the other two hidden): Name, phone number and city.

in my query, i have a field called City_Code. i would like to put [Forms]![frmName]![cboName].[Column](2) in the criteria grid, but Access gives me the error:

"Undefined function [Forms]![frmName]![cboName].[Column] in expression."

is it possible to reference a column in a combo box?
thanks,
ruth

ruth.jonkman@wcom.com

 

[rbowes]

You may not like this answer, but as far as I can tell you can not reference that other columns in your saved query unless one of the hidden columns is the bound column. My suggestion is that you put the query into a sql statement in code behind the form. I believe that you can only access these properties through VBA.

 

[sdk]

Hi,

You may prefer this answer though.

My guess is that your problem is solvable through code, however a (messy) alternative is placing a control (field) on the form which references the column with the criteria (eg txtControl.ControlSource = Forms![frmTest]![cmbCritereaList].column(2) ). Your query can then reference this second control ( Forms![frmTest]![txtControl] ) in the grid. You can even hide this control if you don't wish it to be seen.

Works for me.

 

[rbowes]

sdk has a very good idea. I would suggest that you use it if you are not very confident with code.

bb

 

[ruthcali]

i like both your answers, mainly because you took the time to help me.

and at least now i know that i can't do what i want. not knowing is the hard part. (and trying to figure out why it isn't working).

I will use SDK's suggestion since i have many queries that i have to do this to. it is exactly like you said, a bit messy, but a simple solution.

rbowes--the coding is a good idea too, but in this instance, creating a hidden text box is the best way to go.

thanks to both of you!
Ruth ruth.jonkman@wcom.com

 

[jgoodman00]

Surely if you have a combo-box, you could simply make reference to its source....i.e if it is displaying fields from tblAddress then simply reference it in directly......

 

[ruthcali]

jgoodman,

you mean, by using Dlookup? ruth.jonkman@wcom.com