problem with combo and sql

[BubBob]

I have a combobox that looks up 4 fields of a table. For some reason i can't make a sql-expression that looks up the value from a certain column. Like this:

SELECT xxxxx FROM xxxx WHERE (((brand.pID)=Forms!main!Form!category_combo.COLUMN(1))

Access complains about undeclared function.

 

[Calvin]

it looks like your combo box reference might be over referenced:

Forms!main!Form!category_combo.COLUMN(1))

try changing it to:

Forms![main]![category_combo].COLUMN(1)

Because of the underscore in the name "_" you definitly have to put brackets around the control name [category_combo], try not to do this if you can (not using underscore in the control name), it can cause problems with calling a sub behind the control.
Use standard naming conventions (it's late and I can't think of the name), i.e. cboCategory, txtCategory, lblCategory, tblCategory, qryCategory, frmCategory, strCategory, intCategory, etc...

If your trying to capture in code try:

strSQL = "SELECT xxxxx FROM xxxx WHERE (((brand.pID)=" & Forms![main]![category_combo].COLUMN(1) &");"

in SQL as a saved query, pretty much like you had it except for the extra form reference and the brackets, try:

SELECT xxxxx FROM xxxx WHERE (((brand.pID)=Forms![main]![category_combo].COLUMN(1))


let me know if this helps

 

[Calvin]

By the way, if you are working with a saved query you can right click in the Field location and select the Build function, it can help navigate to and reference different objects and controls like closed tables or forms, controls on those forms or custom user functions embedded in a basic or class module.

 

[Rich]

You can only refer directly to the bound column of the combo, I assume column1 is not the bound column. Either add an unbound textbox with its control source set to =[category_combo].[Column](1) and refer to that textbox in the where clause or use the In operator