none
Runtime error 3127 RRS feed

  • General discussion

  • This is a piece of audit trail code I got online:

    ' If this was not a new record, save the old values.
        If Not bWasNewRecord Then
            sSQL = "INSERT INTO " & sAudTmpTable & " ( Message_Type, Date_Created, User_ID ) " & _
                "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailOnError

            DoCmd.SetWarnings False
            DoCmd.RunSQL sSQL
            DoCmd.SetWarnings True

    MsgBox "Job was Submitted"

        End If

    The field names here exist in my temp table and audit trail table:

    "INSERT INTO " & sAudTmpTable & " ( Message_Type, Date_Created, User_ID ) "

    I am getting this error:

    The INSERT INTO statement contains the following unknown field name: <field name>. Make sure you have typed the name correctly, and try the operation again.(Error 3127)

    and the "<field name>" referred to is Message_Type.

    The Temp table is like this:

    ID                         Autonumber {PK}

    Message_Type       Text

    Date_Created        Date/Time

    User_ID                Text

    I am using a subform in case it has something to do with it. This error does occur when I try to enter the subform.

    Should I use a parameterized Query?
    • Edited by MarcusKgosi Wednesday, June 28, 2017 12:48 PM
    Tuesday, June 27, 2017 10:41 AM

All replies

  • Can you show the value of sSQL just before the db.Execute statement? To do that, add this line immediately before the execute statement:

    Debug.Print sSQL

    Run the procedure, then post the results back here.

    Also, be sure to check (and double check) spelling. 


    -- Scott McDaniel, Microsoft Access MVP

    Tuesday, June 27, 2017 10:45 AM
  • Here's the result Scott:

    INSERT INTO [audTemp table] ( Message_Type, Date_Created, User_ID ) SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, SLD.* FROM SLD WHERE (SLD.SLD_ID = 29);

    • Edited by MarcusKgosi Tuesday, June 27, 2017 11:47 AM
    Tuesday, June 27, 2017 11:31 AM
  • Try using this:

    sSQL = "INSERT INTO " & sAudTmpTable & " ( Message_Type, Date_Created, User_ID ) " & _
                "SELECT 'EditFrom' AS Expr1, '" &  Now() & "' AS Expr2,'" & NetworkUserName() & "'AS Expr3, " & lngKeyValue & " AS Expr4 


    -- Scott McDaniel, Microsoft Access MVP

    Tuesday, June 27, 2017 11:58 AM
  • Why do you have 4 values in the SELECT clause and only 3 fields in the INSERT INTO clause?

    Tuesday, June 27, 2017 1:31 PM
  • Your solution is getting a 3346 error "Number of query values and destination fields are not the same. (Error 3346)".
    Wednesday, June 28, 2017 7:17 AM
  • Sorry - try this again:

    sSQL = "INSERT INTO " & sAudTmpTable & " ( Message_Type, Date_Created, User_ID ) " & _
                "SELECT 'EditFrom' AS Expr1, '" &  Now() & "' AS Expr2,'" & NetworkUserName() & "'AS Expr3


    -- Scott McDaniel, Microsoft Access MVP

    Wednesday, June 28, 2017 11:36 AM
  • Error 3067 

    Query input must contain at least one table or query. (Error 3067).

    Why is the tables primary key not included in this INSERT statement?


    • Edited by MarcusKgosi Wednesday, June 28, 2017 12:29 PM Improved Question
    Wednesday, June 28, 2017 12:05 PM
  • You can certainly add the Primary Key if needed. Remember we're here to HELP you, not do it for you.

    In your original query, you are inserting into 3 fields - MessageType, DateCreated, and UserID. Your original query was trying to insert 4 values, however:

    EditFrom << MessageType
    Now() << DateCreated
    NetworkUserName << UserID

    You then had a fourth item:

    SLD.* FROM SLD WHERE SLD.SLD_ID=29

    I'm not sure what the fourth item was, so I remove it. If you could define exactly what values are to be inserted into the Field, we could help you to create the correct query.


    -- Scott McDaniel, Microsoft Access MVP

    Thursday, June 29, 2017 10:06 AM
  • Here are values for one row:

    Message_Type = 'EditFrom' ,

    Date_Created = '2017-07-03 11:11:07 AM' ,

    User_ID = 21.

    Monday, July 3, 2017 10:41 AM