using a form vairable in query

[Goomba79]

Hello,

I'm having a bit of trouble with my sql statement.

I want to take a vairable from a combobox on my form and use it as a search parameter in my query.

my sql is

SELECT *
FROM tblSkills
WHERE (EmpNo=frmMatrix!ComboEmp.Column(0));

when the query runs i get the error undefinied function 'frmMatrix!ComboEmp.Column' in expression

Is it as simple as a syntax error with the SQL statement or am I going about passing the vairable to the sql completely wrong???

 

[pr2-eugin]

It should be..

SELECT *
FROM tblSkills
WHERE (EmpNo=[B] [Forms]![/B][frmMatrix]!ComboEmp.Column(0));

 

[Goomba79]

Hello,
Thanks for the quick reply!!
I tried the above but i still get the same error??

 

[pr2-eugin]

Okay what is the Bound Column of the ComboBox on the Form? Does it have more than one column?

 

[Goomba79]

the bound column is the second column of the table which is the users name. i want to search by EmpNo which is the first column. i believe the first column is referenced as 0 and the second as 1 and so on?

 

[Beetle]

You can't reference combo/list box columns in a query. One option would be to place a hidden text box on the form to hold the value from the combo box column, then reference the hidden text box in your query.

 

[Goomba79]

hmmm that's a bit pants isn't it!
oh well at least i know now!!!
Thank you very much!

 

[Goomba79]

Ok so i have my sql now as follows

SELECT *
FROM tblSkills
WHERE (EmpNo= [Forms ]![frmMatrix]!txtEmpNo.text);

I have a textbox on my form that gets the vairable from the combobox but when the query runs its asks for the parameter [Forms ]![frmMatrix]!txtEmpNo.text in a pop up window!!?

 

[Goomba79]

ahh took the square brackets out and it works......at last! thanks guys!