Error: Run-time error '3061'. Too few parameters. Expected 1

Question

FSQL = "INSERT INTO Q_ClientSearch(SName, OName, Add1, Add2, Add3, TelNo, Email, NID) " & _
       " VALUES('" & M_SName & "', '" & M_OName & "', '" & M_Add1 & "', '" & M_Add2 & _
       "', '" & M_Add3 & "', '" & M_Phone & "', '" & M_Email & "', '" & M_IDNo & "') "

DoCmd.RunSQL FSQL

Note: All the fields in table are string variable

Everything looks ok to me. Does any of the variable have ' as a character? — Siddharth Rout
If the field NID is numeric, then you don't need the single quotes around M_IDNo. — Applecore

Kostas K. Kostas K. · Accepted Answer · 2021-03-18T11:35:31

String concatenation will only give you troubles and is difficult to maintain. Please use parameters.

Create a query, copy the SQL below and the call it from VBA.

The query:

PARAMETERS [prmSName] Text(255), [prmOName] Text (255), [prmAdd1] Text(255), [prmAdd2] Text(255), 
[prmAdd3] Text(255), [prmTelNo] Text(255), [prmEmail] Text(255), [prmNID] Text(255);

INSERT INTO Q_ClientSearch(SName, OName, Add1, Add2, Add3, TelNo, Email, NID)
SELECT [prmSName] AS SName, [prmOName] AS OName, [prmAdd1] AS SName, [prmSName] AS Add1,
       [prmAdd2] AS Add2, [prmAdd3] AS Add3, [prmEmail] AS Email, [prmNID] AS NID;

To call it:

With CurrentDb().QueryDefs("YourQueryName")
    .Parameters("[prmSName]").Value = M_SName  
    .Parameters("[prmOName]").Value = M_OName  
    .Parameters("[prmAdd1]").Value = M_Add1  
    .Parameters("[prmAdd2]").Value = M_Add2  
    .Parameters("[prmAdd3]").Value = M_Add3  
    .Parameters("[prmTelNo]").Value = M_Phone  
    .Parameters("[prmEmail]").Value = M_Email  
    .Parameters("[prmNID]").Value = M_IDNo  
    .Execute dbFailOnError
End With

Error: Run-time error '3061'. Too few parameters. Expected 1

1 Answers