Syntax error in SQL statement to insert values into designated table

I created a SQL insert statement in VBA (Access Database).

I created a sub to run when a button is clicked, and expect the values to be inserted into the designated table.

I receive a syntax error.

Private Sub addAllocation_Click()
    Dim strSQL, user_id As String
    Dim rs As Recordset
    UserID = Left(Environ("USERNAME"), 15)
    If Me.newEffectiveDate = "" Or Me.newAmount = "" Then
        MsgBox "Please complete all required fields"     
    End If
        
    strSQL = "INSERT INTO Participant_Allocation(Transaction_ID, Participant_ID, Loan_ID, Allocation_Amount, " & _
      "Effective Date, Notes, user_ID) " & _
      "VALUES('" & Me.txtTransactionID & "' , '" & Me.cmbParticipantID.Column(3) & "' , '" & Me.cmbLoan & "' , '" & _
      Me.newAmount & "' , '" & Me.newEffectiveDate & "' , '" & Me.newNotes & "' , '" & UserID & "')"
    Debug.Print strSQL
    CurrentDb.Execute strSQL
        
    MsgBox ("Allocation has been entered.")
    
    Set rs = Nothing
End Sub

Solution

Avoid clumsy SQL and use the power of DAO for much cleaner code:

Private Sub addAllocation_Click()

    Dim strSQL  As String
    Dim user_id As String
    Dim rs      As DAO.Recordset

    If IsNull(Me!newEffectiveDate.Value) Or IsNull(Me!newAmount.Value) Then
        MsgBox "Please complete all required fields."
        Exit Sub
    End If
    
    UserID = Left(Environ("USERNAME"), 15)

    strSQL = "Select * From Participant_Allocation"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)
    rs.AddNew
        rs!Transaction_ID.Value = Me!txtTransactionID.Value
        rs!Participant_ID.Value = Me!cmbParticipantID.Column(3)
        rs!Loan_ID.Value = Me!cmbLoan.Value
        rs!Allocation_Amount.Value =Me!newAmount.Value
        rs![Effective Date].Value = Me!newEffectiveDate.Value
        rs!Notes.Value = Me!newNotes.Value
        rs!user_ID.Value = UserID
    rs.Update
    rs.Close  

    MsgBox "Allocation has been entered."

    Set rs = Nothing

End Sub