CODEWITHSUNDEEP
sqlms-accessvba
VictoriaJay
VictoriaJay

Reputation: 391

Error: too few Parameters. Expected 3...while executing SQL statement in Access

I've done this before, just a test as to whether a record already exists in a table before I insert a new record. This was basically what I did, yet this time I get an error

Error: "3061 Too few parameters. Expected 2."

I tried to be very specific (tblfromICPMS.Woid instead of just Woid) thinking that might help, but it didn't really make a difference. I get the specifically at the line 

If rs.RecordCount = 0 Then

This is the code, where ThisWoid, Analyte and ThisTestId are local variables: 

DIM rs As Object
strSQL = "SELECT tblfromICPMS.Woid, tblfromICPMS.Analyte, tblfromICPMS.TestID FROM tblFROMICPMS WHERE (tblFROMICPMS.woid = " & ThisWoid & ") AND (tblFROMICPMS.analyte = " & Analyte & ") AND (tblfromICPMS.Testid=" & ThisTestID & ") "
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
   "insert record"
End IF

UPDATE: error message changed to 1 expected after updating sqlstr in comments, also included declaration of rs. For clarification tblFromICPMS is a table which contains the columns Woid, Analyte and TestId. I have retrieved three values and stored them in local variables ThisWoid, Analyte and ThisTestId. I want to insert a record but first I must see if it already exists in tblfromICPMS. Also ThisWoid and Analyte are strings

Upvotes: 1

Views: 1366

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

First of all, I'd try DIMing the rs as a RecordSet, not an Object. Also, stick in a Message Box just to make sure your SQL string makes sense:

DIM rs As Recordset
strSQL = "SELECT Woid, Analyte, TestID FROM tblFROMICPMS WHERE (woid = " & ThisWoid & ") AND (analyte = " & Analyte & ") AND (Testid=" & ThisTestID & ") "
msgbox strSQL
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
   "insert record"
End IF

See if the message box results give you any clues as to what's wrong. Typically, that error happens when Access doesn't understand one of your variables.

Don't forget, text needs to be surrounded by single quotes in your SQL string, integers do not.

Upvotes: 2

Related Questions