Why is my Me. Reference and INSERT INTO causing error?

I am receiving two errors when attempting to create a query which updates my audit log after an update is made to a record.

First I am prompted to enter a Parameter Value where I believed my ME. code would point to any record with the current value of field [Corrected Med Ed ID].

Secondly I receive message:

Syntax error in INSERT INTO statement.

My form’s code is –

Private Sub Form_AfterUpdate()
    Dim UserName As String

    DoCmd.SetWarnings False
    'updates date
    DoCmd.RunSQL "update tbl_InterfaceLog SET tbl_InterfaceLog.[LastUpdated] = Date() Where [Corrected Med Ed ID] = " & Me.[Corrected Med Ed ID]
    'stamps username
    DoCmd.RunSQL "update tbl_InterfaceLog SET tbl_InterfaceLog.[LastUpdatedBy] = Username() Where [Corrected Med Ed ID] = " & Me.[Corrected Med Ed ID]
    'Adds the record to log
    DoCmd.RunSQL "INSERT INTO tbl_AuditLog (Status, Comments, Owner, [corrected med ed ID], [Upload File Name], [Submission Method], AirfareStatus, GroundStatus, MealsStatus, AccomodationStatus, AirfareComment, GroundComment, MealsComment, AccommodationComment, Coordinator, SupportRequested, LastUpdated, Reviewed, ReviewerComment, RequiredChange, EventDate, ProductsMatch, SubmissionMethod, TravelDestination, LastUpdatedBy" _
    & " SELECT tbl_InterfaceLog.Status, tbl_InterfaceLog.Comments, tbl_InterfaceLog.Owner, tbl_InterfaceLog.[corrected med ed ID], tbl_InterfaceLog.[Upload File Name], tbl_InterfaceLog.[Submission Method], tbl_InterfaceLog.AirfareStatus, tbl_InterfaceLog.GroundStatus, tbl_InterfaceLog.MealsStatus, tbl_InterfaceLog.AccomodationStatus, tbl_InterfaceLog.AirfareComment, tbl_InterfaceLog.GroundComment, tbl_InterfaceLog.MealsComment, tbl_InterfaceLog.AccommodationComment, tbl_InterfaceLog.Coordinator, tbl_InterfaceLog.SupportRequested, tbl_InterfaceLog.LastUpdated, tbl_InterfaceLog.Reviewed, tbl_InterfaceLog.ReviewerComment, tbl_InterfaceLog.RequiredChange, tbl_InterfaceLog.EventDate, tbl_InterfaceLog.ProductsMatch, tbl_InterfaceLog.SubmissionMethod, tbl_InterfaceLog.TravelDestination, tbl_InterfaceLog.LastUpdatedBy" _
    & " FROM tbl_InterfaceLog" _
    & " WHERE [Corrected Med Ed ID] = " & Me.[Corrected Med Ed ID]
    DoCmd.SetWarnings True
End Sub

Answer

Simply use parameterized queries and avoid the need to concatenate SQL string in VBA. Specifically, save below two SQL statements as stored Access queries. Then bind parameters using QueryDefs in VBA for cleaner, readable, and maintainable code.

Note: the two update queries can be combined into one. Also, the table alias, t, is used. When using Query Design, Access will not allow you to save queries with syntax error. Below assumes parameter will be a string/text value.

SQL

Update Query

PARAMETERS idparam TEXT(255);
UPDATE tbl_InterfaceLog t
   SET t.[LastUpdated] = Date(),
       t.[LastUpdatedBy] = Username()
WHERE [CorrectedMedEdID] = idparam;

Append Query

PARAMETERS idparam TEXT(255);
INSERT INTO tbl_AuditLog ([Status], [Comments], [Owner], [corrected med ed ID], 
                          [Upload File Name], [Submission Method], AirfareStatus,
                          GroundStatus, MealsStatus, AccomodationStatus, 
                          AirfareComment, GroundComment, MealsComment, 
                          AccommodationComment, Coordinator, SupportRequested, 
                          LastUpdated, Reviewed, ReviewerComment, RequiredChange, 
                          EventDate, ProductsMatch, SubmissionMethod, 
                          TravelDestination, LastUpdatedBy)

SELECT t.Status, t.Comments, t.Owner, t.[corrected med ed ID], t.[Upload File Name],
       t.[Submission Method], t.AirfareStatus, t.GroundStatus, t.MealsStatus, 
       t.AccomodationStatus, t.AirfareComment, t.GroundComment, t.MealsComment, 
       t.AccommodationComment, t.Coordinator, t.SupportRequested, t.LastUpdated, 
       t.Reviewed, t.ReviewerComment, t.RequiredChange, t.EventDate, t.ProductsMatch, 
       t.SubmissionMethod, t.TravelDestination, t.LastUpdatedBy
FROM tbl_InterfaceLog t
WHERE t.[CorrectedMedEdID] = idparam;

VBA (no concatenation)

Private Sub Form_AfterUpdate()
    Dim UserName As String
    Dim qdef as QueryDef

    ' UPDATE QUERY 
    Set qdef = CurrentDb.QueryDefs("myUpdateQuery")        
    qdef!idparam = Me.[Corrected Med Ed ID]             ' BIND PARAM
    qdef.Execute dbFailOnError                          ' EXECUTE QUERY
    Set qdef = Nothing    

    ' APPEND QUERY
    Set qdef = CurrentDb.QueryDefs("myAppendQuery")        
    qdef!idparam = Me.[Corrected Med Ed ID]             ' BIND PARAM
    qdef.Execute dbFailOnError                          ' EXECUTE QUERY
    Set qdef = Nothing
End Sub

Or DRY-er approach:

Private Sub Form_AfterUpdate()
    Dim UserName As String, qry As Variant
    Dim qdef as QueryDef

    ' RUN ACTION QUERIES
    For Each qry in Array("myUpdateQuery", "myAppendQuery")
        Set qdef = CurrentDb.QueryDefs(qry)  
        qdef!idparam = Me.[Corrected Med Ed ID]         ' BIND PARAM
        qdef.Execute dbFailOnError                      ' EXECUTE QUERY
        Set qdef = Nothing    
    Next qry

    Set qdef = Nothing
End Sub