Access VBA SQL statement received syntax error when attempting to INSERT values

Question

I'm using Access VBA to insert values into a table based on information from a form. The SQL statement is throwing a syntax error, however, if I pull the SQL into the query builder it will run so I'm not sure where the syntax error is.

INSERT INTO [TBLactionstaken] (RecordNumber, ActionID, ReasonID, ActionBy, LetterTo, LetterAddress, LetterType, MANHType, MANReqAmt, NRReason1, NRReason2, NRReason3, NRReason4, NRReason5, RFActionID, ActionDate, ActionStatus) 

Values (" & Forms![frmActions]![txtrecordNumber2] & ", " & Forms![frmActions]![lstActions] & ", " & Forms![frmActions]![lstReasons] & ", '" & _
    Forms![frmActions]![txtActionBy] & "', '" & apostrophe(Forms![frmActions]![cboLetterTo]) & "', '" & Forms![frmActions]![cboLetterAddress] & "', '" & _
    Forms![frmActions]![cboLetterType] & "', '" & IIf(IsNull(Forms![frmActions]![cboMANHType]), "", Forms![frmActions]![cboMANHType]) & "', " & _
    Forms![frmActions]![cboReqAmt] & ", " & IIf(IsNull(Forms![frmActions]![cboNRReason1]), "", "'" & Forms![frmActions]![cboNRReason1] & "'") & ", " & _
    IIf(IsNull(Forms![frmActions]![cboNRReason2]), "", "'" & Forms![frmActions]![cboNRReason2] & "'") & ", " & _
    IIf(IsNull(Forms![frmActions]![cboNRReason3]), "", "'" & Forms![frmActions]![cboNRReason3] & "'") & ", " & _
    IIf(IsNull(Forms![frmActions]![cboNRReason4]), "", "'" & Forms![frmActions]![cboNRReason4] & "'") & ", " & _
    IIf(IsNull(Forms![frmActions]![cboNRReason5]), "", "'" & Forms![frmActions]![cboNRReason5] & "'") & ", " & _
    IIf(IsNull(Forms![frmActions]![cboActID]), "", "'" & Forms![frmActions]![cboActID] & "'") & ", #" & Forms![frmActions]![txtDatereceived] & "#, 'Pending')"

Answer 1

This type of code is simply not maintainable. As you have (probably painfully) seen when trying to debug it.

I would replace it with RecordSet.AddNew, where you assign form values to recordset values line by line. This is readable and maintainable.
See comments.

Sub DoSave()

    Dim rs As Dao.Recordset
    Dim f As Access.Form
    
    Set rs = CurrentDb.OpenRecordset("TBLactionstaken", dbOpenDynaset, dbAppendOnly)
    Set f = Forms![frmActions]
    
    With rs
        .AddNew
        !RecordNumber = f!txtrecordNumber2
        !ActionID = f!lstActions
        !ReasonID = f!lstReasons
        !ActionBy = f!txtActionBy
        ' no need to check for apostrophes in strings...
        !LetterTo = f!cboLetterTo
        
        ' etc etc
        
        ' no special handling of NULL values necessary...
        !NRReason1 = f!cboNRReason1
        ' ... unless your table does actually want empty strings instead of NULLs, then do:
        !NRReason2 = Nz(f!cboNRReason2, "")

        ' you could also do...
        For i = 1 To 5
            rs("NRReason" & i) = f("cboNRReason" & i)
        Next i
        
        ' etc etc
        
        .Update
        .Close
    End With
    
End Sub