CODEWITHSUNDEEP
vbams-accessvba6
mrk777
mrk777

Reputation: 167

Insert the data into a Table from an unbounded form

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!

Upvotes: 0

Views: 97

Answers (1)

cbk
cbk

Reputation: 41

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

Upvotes: 1

Related Questions