Why wont my code work?

[Guest]

I have a program that I want to open a recordset that only contains
information on people in a particular department. The user selects which
department from a combo box on a form. My SQL code works fine in a query but
not when I code it. The lines are

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=[forms]![frmDepartmentUpdate].[Combo2]))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Why does the code work in a query nut not in code?

I keep getting the error message saying to few parameters expecting 1

 

[Guest]

Ther reference to your form control needs to be outside the quotes.
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)= '" & [forms]![frmDepartmentUpdate].[Combo2]
& "'))"

 

[Guest]

Hi there I have tried what you have sugested but it hasn't made any
difference. The code now looks like this:

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=" & [Forms]![frmDepartmentUpdate].[Combo2] &
"))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Ther reference to your form control needs to be outside the quotes.
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)= '" & [forms]![frmDepartmentUpdate].[Combo2]
& "'))"

I have a program that I want to open a recordset that only contains
information on people in a particular department. The user selects which
department from a combo box on a form. My SQL code works fine in a query but
not when I code it. The lines are

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=[forms]![frmDepartmentUpdate].[Combo2]))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Why does the code work in a query nut not in code?

I keep getting the error message saying to few parameters expecting 1

 

[Guest]

Is department a text field or a numeric field?

Hi there I have tried what you have sugested but it hasn't made any
difference. The code now looks like this:

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=" & [Forms]![frmDepartmentUpdate].[Combo2] &
"))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Ther reference to your form control needs to be outside the quotes.
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)= '" & [forms]![frmDepartmentUpdate].[Combo2]
& "'))"

I have a program that I want to open a recordset that only contains
information on people in a particular department. The user selects which
department from a combo box on a form. My SQL code works fine in a query but
not when I code it. The lines are

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=[forms]![frmDepartmentUpdate].[Combo2]))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Why does the code work in a query nut not in code?

I keep getting the error message saying to few parameters expecting 1

 

[Guest]

Yes it is a text field. I have now managed to sort it out using chr$(34) to
create the quotes.

Thanks for your help.

Keith

Is department a text field or a numeric field?

Hi there I have tried what you have sugested but it hasn't made any
difference. The code now looks like this:

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=" & [Forms]![frmDepartmentUpdate].[Combo2] &
"))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Ther reference to your form control needs to be outside the quotes.
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)= '" & [forms]![frmDepartmentUpdate].[Combo2]
& "'))"


:

I have a program that I want to open a recordset that only contains
information on people in a particular department. The user selects which
department from a combo box on a form. My SQL code works fine in a query but
not when I code it. The lines are

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=[forms]![frmDepartmentUpdate].[Combo2]))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Why does the code work in a query nut not in code?

I keep getting the error message saying to few parameters expecting 1

 

[Guest]

I know the font is small and hard to read, but if you look back at my
previous post, you will see how the single quote can be used for enclosing
text fields.

Glad you got it working.

Yes it is a text field. I have now managed to sort it out using chr$(34) to
create the quotes.

Thanks for your help.

Keith

Is department a text field or a numeric field?

Hi there I have tried what you have sugested but it hasn't made any
difference. The code now looks like this:

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=" & [Forms]![frmDepartmentUpdate].[Combo2] &
"))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

:

Ther reference to your form control needs to be outside the quotes.
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)= '" & [forms]![frmDepartmentUpdate].[Combo2]
& "'))"


:

I have a program that I want to open a recordset that only contains
information on people in a particular department. The user selects which
department from a combo box on a form. My SQL code works fine in a query but
not when I code it. The lines are

Set db = CurrentDb()
strStaff = "SELECT [tblStaff Data].EmpNo FROM [tblStaff Data]WHERE
((([tblStaff Data].Department)=[forms]![frmDepartmentUpdate].[Combo2]))"
Set recStaff = db.OpenRecordset(strStaff)
recStaff.MoveFirst

Why does the code work in a query nut not in code?

I keep getting the error message saying to few parameters expecting 1