Using Insert SQL in VBA

[Yippiekaiaii]

I am trying to insert a new record directly into a table using INSERT INTO sql. The field data is coming from some text boxes on an unbound form.

I keep getting an error telling me "too few parameters. Expected 5"

This is my code

Dim db As DAO.Database
Set db = DBEngine(0)(0)

Text7.SetFocus
pJobID = Text7
Text1.SetFocus
pFitter = Text1
Text3.SetFocus
pNetValue = Text3
Text5.SetFocus
pInvNotes = Text5
Text9.SetFocus
pBookingRef = Text9

sSQL = " INSERT INTO Invoices " _
& "(JobID,Fitter,NetValue,InvNotes,BookingRef) VALUES " _
& "(pJobID,pFitter,pNetValue,pInvNotes,pBookingRef);"


Debug.Print sSQL
db.Execute sSQL, dbFailOnError

This works if i substitute the line:

& "(pJobID,pFitter,pNetValue,pInvNotes,pBookingRef);"

With specific test data (ie i put specific values in the VALUES section of the SQL). but not when I am using variables.

Any idea where im going wrong?

 

[pr2-eugin]

You need to concatenate the values..

sSQL = "INSERT INTO Invoices (JobID, Fitter, NetValue, InvNotes, BookingRef) VALUES (" & _
        pJobID & " , " & pFitter & ", " & pNetValue & ", '" & pInvNotes & "', '" & pBookingRef & "');"

I have guessed jobID, pFitter, pNetValue to be Numbers.. If they are String, do what I have done to InvNotes i.e. Surround with single quotes.

 

[Yippiekaiaii]

That works perfectly thanks!

 

[pr2-eugin]

No problem ! Good Luck !