0

I have been facing the below issue while I'm using the VBA code to Insert the data into a Table from an unbounded form:

Syntax 3134 - Runtime Error

On Error GoTo Err_Handler

Dim strSQL As String

If DCount("EmpDBID", "tbl_R&R", "EmpDBID=" & Me!EmpDBID) > 0 And DCount("RR_Emp_ID", "tbl_R&R", "RR_Emp_ID=" & Me!RR_DBID) > 0 And DCount("Rewarded_Date", "tbl_R&R", "Rewarded_Date=" & Me!Rewarded_Date) > 0 Then
    MsgBox "This data has already been entered into the Database"
    Exit Sub
Else
    strSQL = "INSERT INTO tbl_R&R (EmpDBID, Rewarded_Date, RR_DBID, Nominated_by) VALUES (" & Me!EmpDBID & ", #" & Me!Rewarded_Date & "#, " & Me!RR_DBID & ", " & Me!Nominated_by & ");"
    DoCmd.RunSQL strSQL '**Facing the issue while execution**
End If
Exit Sub

Err_Handler:
MsgBox "Error has occurred"

End Sub

I do not have any spaces in field names, but still I face the issues. Just for your reference, I have attached the images of Table, Table Properties & Form.

FormTableTable Properties

If I verify the results using immediate window, following are the results it is showing:

INSERT INTO tbl_R&R (EmpDBID, Rewarded_Date, RR_DBID, Nominated_by) VALUES (2, #12/20/2022#, 2, 6);

Is there any other way to update the data into the table from an Unbounded form? Appreciate your help!

mrk777
  • 117
  • 1
  • 12
  • Can you make a screenshot of that error? The wording is really odd, specifically it starting with "Syntax" then saying "runtime error" without further specification – Erik A Dec 19 '22 at 19:54
  • 1
    By the way, I highly recommend [using parameters](https://stackoverflow.com/q/49509615/7296893). Even though these only appear to be dates and numbers, doing it right never hurts and can help avoid errors, for example, with date settings influencing the SQL – Erik A Dec 19 '22 at 19:56
  • Use parameters or apostrophe delimiters for text field data. `& ", '" & Me!Nominated_by & "');"`. Also, need # delimiters for date/time value in domain aggregate function WHERE CONDITION: `"Rewarded_Date=#" & Me!Rewarded_Date & "#"`. – June7 Dec 19 '22 at 21:05
  • 2
    What happens if you bracket the table name in your insert statement like this? `INSERT INTO [tbl_R&R] (EmpDBID, ...` – HansUp Dec 19 '22 at 22:23
  • Good point. Need brackets because of & character. Need proper delimiters for concatenated data inputs. – June7 Dec 20 '22 at 02:33
  • Advise not to use spaces nor punctuation/special characters in naming convention. – June7 Dec 20 '22 at 03:05
  • Thank you so much @HansUp , the problem is solved! it is because the table name needs the brackets because of & character. from now on, I would not use any special characters in the naming convention. – mrk777 Dec 20 '22 at 05:02

1 Answers1

1

The Nominated_by field is short text. So the SQL code generated should be in single quotes. Otherwise this could be causing the Runtime Error.

On Error GoTo Err_Handler

Dim strSQL As String

If DCount("EmpDBID", "tbl_R&R", "EmpDBID=" & Me!EmpDBID) > 0 And DCount("RR_Emp_ID", "tbl_R&R", "RR_Emp_ID=" & Me!RR_DBID) > 0 And DCount("Rewarded_Date", "tbl_R&R", "Rewarded_Date=" & Me!Rewarded_Date) > 0 Then
    MsgBox "This data has already been entered into the Database"
    Exit Sub
Else
    strSQL = "INSERT INTO tbl_R&R (EmpDBID, Rewarded_Date, RR_DBID, Nominated_by) VALUES (" & Me!EmpDBID & ", #" & Me!Rewarded_Date & "#, " & Me!RR_DBID & ", '" & Me!Nominated_by & "');"
    DoCmd.RunSQL strSQL
End If
Exit Sub

Err_Handler:
MsgBox "Error has occurred"

End Sub
cbk
  • 41
  • 7