-1
insertString = "INSERT INTO paymentRecord(paymentEventDate,paymentType,paymentRecordNotes,value,clientID,jobID) 
VALUES (recordDate,recordType,recordNotes,paymentValue,clientID,jobID);"
DoCmd.SetWarnings False
DoCmd.RunSQL insertString
DoCmd.SetWarnings True

This code returns a syntax error and I don't know why. All insert values and columns are correct.

The code is being used in a MS Access front-end with a MySQL database back-end.

CertainPerformance
  • 356,069
  • 52
  • 309
  • 320
Samuel Bland
  • 3
  • 1

1 Answers1

0

You must concatenate the values:

" .. VALUES (" & recordDate & "," & recordType & "," & recordNotes & "," & paymentValue & "," & clientID & "," & jobID)"

Also, the values must be formatted and delimited correctly, for example by using my function CSql.

Even better, use parameters.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Ok thankyou, I will try that. Though the confusing thing is that I have the same query working elsewhere in Access, but it's just inputting different values into a different table. – Samuel Bland Aug 15 '18 at 10:08
  • This hasn't worked. But as a general note. The correct concatenated code was `VALUES (" & recordDate & ",'" & recordType & "','" & recordNotes & "'," & paymentValue & "," & clientID & "," & jobID & ")"` - Different delimiters for string variables and anything else (integer, date) Thanks anyway – Samuel Bland Aug 15 '18 at 10:23
  • OK, but the date must be formatted and delimited as well. See how in my link above. – Gustav Aug 15 '18 at 11:11
  • Yeah. My code and your code are both same for parsing the date variable, I was saying that we'd need different formatting for sending string variables. With an apostrophe. – Samuel Bland Aug 15 '18 at 11:13