Error 3601

[sda7bobp]

I have a table (tblInvUsed) with a field called CaseNum which contains multiple records for one CaseNum. In a Class module from a form (frmCaseEntryDr) I want to open this table and check the set of records with the same CaseNum that is active on the form for certain drugs ordered for a procedure. I have used the following code and have received "Error 3061: Too few parameters: Expected 1"

Dim db as Database, rst as Recoreset, lngCaseNum as Long
lngCaseNum = Me.CaseNum
strSQL = "SELECT * FROM tblInvUsed [COLOR=red]WHERE '[CaseNum] = ' & lngCaseNum [/color red]"
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)

Access reads the lngCaseNum correctly in the Debug Window. The strSQL appears to be written just like the help file states, but I must be doing something wrong - I suspect it is in the WHERE statement.

Thanks for your help.
Bob

 

[mjk9564]

Is your problem witth the way you did you single quotes?
Try this if casenum is numeric:
strSQL = "SELECT * FROM tblInvUsed WHERE [CaseNum] = " & lngCaseNum

Try this if casenum is a string:
strSQL = "SELECT * FROM tblInvUsed WHERE [CaseNum] = '" & lngCaseNum & "'"

Hope it helps!
Matt

 

[sda7bobp]

Thanks, Matt, for replying so quickly. Actually CaseNum is a long number. Trying your solution resulted in a syntax error.
Bob

 

[PHV]

And this ?
strSQL = "SELECT * FROM tblInvUsed WHERE CaseNum=" & lngCaseNum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sda7bobp]

PHV, That did it. Thanks so much!!
Bob

 

[mjk9564]

Are you sure you typed it in right?

Run this query in Access's:
SELECT * FROM tblInvUsed WHERE CaseNum = (any casenum)
Then just change the casenum you used to lngcasenum when you figure out the syntax error.
You can also get rid of the strsql and do this to see if it works:
Set rst = db.OpenRecordset("SELECT * FROM tblInvUsed WHERE CaseNum = " & lngCaseNum)