Access connection error 1

[pingit]

Hello

I have a form with a drop down box (Combo5). Once selected I want to run sql in code but I keep getting an error at the recordset stage (rst.open...see below). The error says "Run-time error -2 2147217904 No given value for one or more of the required parameters" I have been round and round the net trying different connection settings to no avail
any help much appreciated

Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim sqlResult As String

Set db = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = "SELECT Staff.StaffID, Staff.Lname, Staff.Fname, Staff.Address FROM Staff WHERE Staff.StaffID = forms![Setup]![Combo5];"

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

 

[PHV]

strSQL = "SELECT Staff.StaffID, Staff.Lname, Staff.Fname, Staff.Address FROM Staff WHERE Staff.StaffID = [!][tt]'" & [/tt][/!]forms![Setup]![Combo5][!][tt] & "'[/tt][/!]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hans8823]

It's not your connection:

strSQL = "SELECT Staff.StaffID, Staff.Lname, Staff.Fname, Staff.Address FROM Staff WHERE Staff.StaffID = " & forms![Setup]![Combo5]

 

[Trevil]

Assuming the combobox has alpha values use:
strSQL = "SELECT Staff.StaffID, Staff.Lname, Staff.Fname, Staff.Address FROM Staff WHERE Staff.StaffID = '" & [forms]![Setup]![Combo5] & "';"


Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[pingit]

Thanks you all for your prompt replies

Hans8823's reply did the trick