Run-time Error '3134'

[avitale]

Hey everybody,
I am new to Access (using it in a class) and have been experiencing Run-time error '3134' while trying to create a macro. It says "Syntax error in INSERT INTO statement" and highlights the line "CurrentDb.Execute ClientSQL"

Private Sub Intake_DblClick(Cancel As Integer)


If Me.Confidentiality_Explained_Date.Value = False Then
MsgBox "Confidentiality Form must be explained before Intake"
ElseIf IsNull(Me.Case_Number.Value) Or Trim(Me.Case_Number.Value) = "" Then
MsgBox "Case number is not optional"
Else
Dim ClientSQL As String
ClientSQL = "INSERT INTO [Client] (" & _
"[First Name], " & _
"[Middle Initial], " & _
"[Last Name], " & _
"[Home Telephone No] " & _
") VALUES (" & _
"'" & Me.First_Name.Value & "', " & _
"'" & Me.Middle_Initial.Value & "', " & _
"'" & Me.Last_Name.Value & "', " & _
"'" & Me.Home_Telephone_No.Value & "', " & _
")"

CurrentDb.Execute ClientSQL

Dim CaseSQL As String
CaseSQL = "INSERT INTO [Case] (" & _
"[Case Number], " & _
"[Caller and Client Relationship], " & _
"[Caller Name], " & _
"[Caller's Home Phone No], " & _
"[Confidentiality Explained Date], " & _
"[Alternatives Pursued], " & _
"[Referral Source Code], " & _
"[Confidentiality Form Sent Method], " & _
"[Confidentiality Form Sent], " & _
"[Confidentiality Form Received], " & _
") VALUES (" & _
"'" & Me.Case_Number.Value & "', " & _


"'" & Me.Caller_and_Client_Relationship.Value & "', " & _
"'" & Me.Caller_Name.Value & "', " & _
"'" & Me.Caller_s_Home_Phone_No.Value & "', " & _
"'" & Me.Confidentiality_Explained_Date.Value & "', " & _
"'" & Me.Alternatives_Pursued.Value & "', " & _
"'" & Me.Referral_Source_Code.Value & "', " & _
"'" & Me.Confidentiality_Form_Sent_Method.Value & "', " & _
"'" & Me.Confidentiality_Form_Sent.Value & "', " & _
"'" & Me.Confidentiality_Form_Received.Value & "', " & _
")"




CurrentDb.Execute CaseSQL

CurrentDb.Execute "DELETE FROM [Potential Clients] WHERE [Work Request Number] = " & Me.Work_Request_Number.Value

MsgBox "Client intake successful, record removed from work request"

End If



End Sub

[June7]

First, that isn't a macro, it is VBA coding. Macros and VBA are two different means of coding within Access.

Not sure what that error number means but the unnecessary final comma at the end of the sql string is definitely a syntax error.

Why are you using INSERT anyway? I hope you aren't duplicating data. Are you not using bound forms?

Also, it is not necessary to type the .Value as it is the default for data controls and will be assumed if no property referenced.

[pbaldy]

This should help you debug it:

http://www.baldyweb.com/ImmediateWindow.htm

You actually have two of the commas mentioned by June7. Further, only text values should be surrounded by the single quotes. Date values by #, numeric values by neither (determined by the data type of the target fields).