Runtime Error 3134 - Syntax Error in INSERT INTO Statement (1 Viewer)

[kiss.shoremishra]

I actually created a user form with 5 input controls first 2 Combo box then 2 text box and last combo box. The first 2 and last combo box are mandatory fields where user had to mandatory select the value only then query will be executed and record gets added. Code works properly for first time but in second time (As a test, I tried again to miss the mandatory field & click) but instead of message it gives "Runtime Error 3134" Here below is my code.

Private Sub Update_Button_Click()
If IsNull(Me.[cbo_WRINPrefix]) Or Me.[cbo_WRINPrefix] = "" Then
MsgBox "WRIN Prefix is mandatory", vbOKOnly, "Input Error"
Me.[cbo_WRINPrefix].SetFocus
ElseIf IsNull(Me.[cbo_GlobalAI]) Or Me.[cbo_GlobalAI] = "" Then
MsgBox "Global AI is mandatory", vbOKOnly, "Input Error"
Me.[cbo_GlobalAI].SetFocus
ElseIf IsNull(Me.[cbo_Updated_By]) Or Me.[cbo_Updated_By] = "" Then
MsgBox "It is important to update your name, Please select your name from the Drop down", vbOKOnly, "Input Error"
Me.[cbo_Updated_By].SetFocus
Else
Me.[McD_Comments_textbox].SetFocus
End If
'End If
'Exit Sub
'End If
'Add data to Table tbl_GLOBAL_AI_WRIN_PREFIX_MAP_MASTER
CurrentDb.Execute "INSERT INTO tbl_GLOBAL_AI_WRIN_PREFIX_MAP_MASTER ([WRIN PREFIX], [CORE_AI_ID], [HAVI Comments], [McD Comments], [Insert_Date], [Update_Date], [Updated_By]) " & _
" VALUES(" & Me.[cbo_WRINPrefix] & ",'" & Me.[cbo_GlobalAI] & "','" & Me.[Text_HAVI_Comments] & "','" & Me.[McD_Comments_textbox] & "','" & Date & "','" & Date & "','" & Me.[cbo_Updated_By] & "')"

MsgBox "Record added."
Me.[cbo_WRINPrefix] = ""
Me.[cbo_GlobalAI] = ""
Me.[Text_HAVI_Comments] = ""
Me.[McD_Comments_textbox] = ""
Me.[cbo_Updated_By] = ""
Me.cbo_Updated_By.Requery
Me.Refresh
Exit Sub
End Sub

 

[isladogs]

Using code tags (# on advanced editor toolbar) makes it easier for others to read your code. I've done this below and indented each part:

Private Sub Update_Button_Click()

If IsNull(Me.[cbo_WRINPrefix]) Or Me.[cbo_WRINPrefix] = "" Then
    MsgBox "WRIN Prefix is mandatory", vbOKOnly, "Input Error"
    Me.[cbo_WRINPrefix].SetFocus
ElseIf IsNull(Me.[cbo_GlobalAI]) Or Me.[cbo_GlobalAI] = "" Then
    MsgBox "Global AI is mandatory", vbOKOnly, "Input Error"
    Me.[cbo_GlobalAI].SetFocus
ElseIf IsNull(Me.[cbo_Updated_By]) Or Me.[cbo_Updated_By] = "" Then
    MsgBox "It is important to update your name, Please select your name from the Drop down", vbOKOnly, "Input Error"
    Me.[cbo_Updated_By].SetFocus
Else
    Me.[McD_Comments_textbox].SetFocus
End If

'Add data to Table tbl_GLOBAL_AI_WRIN_PREFIX_MAP_MASTER

CurrentDb.Execute "INSERT INTO tbl_GLOBAL_AI_WRIN_PREFIX_MAP_MASTER" & _
    " ([WRIN PREFIX], [CORE_AI_ID], [HAVI Comments], [McD Comments], [Insert_Date]," & _
    " [Update_Date], [Updated_By]) " & _
    " VALUES(" & Me.[cbo_WRINPrefix] & ",'" & Me.[cbo_GlobalAI] & "','" & Me.[Text_HAVI_Comments] & "'," & _
    " '" & Me.[McD_Comments_textbox] & "','" & Date & "','" & Date & "'," & _
    " '" & Me.[cbo_Updated_By] & "')"

    MsgBox "Record added."

    Me.[cbo_WRINPrefix] = ""
    Me.[cbo_GlobalAI] = ""
    Me.[Text_HAVI_Comments] = ""
    Me.[McD_Comments_textbox] = ""
    Me.[cbo_Updated_By] = ""

    Me.cbo_Updated_By.Requery
    Me.Refresh

Exit Sub

End Sub

You need to modify the date delimiters as below:

CurrentDb.Execute "INSERT INTO tbl_GLOBAL_AI_WRIN_PREFIX_MAP_MASTER" & _
    " ([WRIN PREFIX], [CORE_AI_ID], [HAVI Comments], [McD Comments], [Insert_Date]," & _
    " [Update_Date], [Updated_By]) " & _
    " VALUES(" & Me.[cbo_WRINPrefix] & ",'" & Me.[cbo_GlobalAI] & "','" & Me.[Text_HAVI_Comments] & "'," & _
    " '" & Me.[McD_Comments_textbox] & "',[COLOR="DarkRed"]#[/COLOR]" & Date & "[COLOR="darkred"]#[/COLOR],[COLOR="darkred"]#[/COLOR]" & Date & "[COLOR="darkred"]#[/COLOR]," & _
    " '" & Me.[cbo_Updated_By] & "')"

If not based in USA, you'll also need to format the dates as mm/dd/yyyy in the SQL statement

The delimiters for

" & Me.[cbo_WRINPrefix] & "

are fine if this is a number
Otherwise its

'" & Me.[cbo_WRINPrefix] & "'

Other suggestions:
1. Use Nz to simplify your code.
For example, replace

If IsNull(Me.[cbo_WRINPrefix]) Or Me.[cbo_WRINPrefix] = "" Then

with

If Nz(Me.[cbo_WRINPrefix],"") = "" Then

or if that's a number value replace with

If Nz(Me.[cbo_WRINPrefix],0) = 0 Then

2. If you get rid of all spaces and underscores in field and control names, the [] are no longer needed and your code will be much more concise / easier to read