Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL statement in VB code - need help (xp)

    I am not so hot with sql, and I am trying to add a record to a table with the following code. I get an error msg 3134: "Syntax error in INSERT INTO statement." The DoCmd.RunSQL Line is high-lighted:

    <font color=blue>
    Public Function track_after(numRec As Integer, strForm As String, strWhat As String, strAfter As String)
    Dim datToday As Date
    Dim strSQL As String
    Dim strUser As String
    'On Error GoTo Err_Out

    'Get Who
    Call GetUserName
    strUser = GetUserName

    'Get Date
    datToday = Date

    'Write to a record: (use query?)
    strSQL = "INSERT INTO tbl_audit (aud_record_number, aud_date, aud_eng, aud_form, aud_field, aud_before, aud_after) " _
    & "VALUES " & numRec & ", " & datToday & ", " & strUser & ", " & strForm & ", " & strWhat & ", " & strBefore _
    & ", " & strAfter & ";"
    MsgBox strSQL
    DoCmd.RunSQL (strSQL)

    Err_Exit:
    Exit Function

    Err_Out:
    MsgBox Err.Description & " Error Number: " & Err.Number
    Resume Err_Exit
    End Function
    </font color=blue>

    Here is the GetUserName Function:

    <font color=blue>
    Public Function GetUserName() As String
    'On Error GoTo Err_Out

    Dim lR As Long
    Dim sDump As String
    sDump = String(255, " ")
    lR = GetUserID(sDump, 255)
    GetUserName = Left$(sDump, InStr(1, sDump, vbNullChar) - 1)

    Err_Exit:
    Exit Function

    Err_Out:
    MsgBox Err.Description & " Error Number: " & Err.Number
    Resume Err_Exit
    End Function
    </font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL statement in VB code - need help (xp)

    You must enclose date values in "#" characters and string values in single or double quotes. Try
    <pre>strSQL = "INSERT INTO tbl_audit ( aud_record_number, aud_date, aud_eng, aud_form, " & _
    "aud_field, aud_before, aud_after ) " & _
    "VALUES ( " & numRec & ", #" & datToday & "#, '" & strUser & "', '" & strForm & _
    "', '" & strWhat & "', '" & strBefore & "', '" & strAfter & "' );"
    </pre>

    Note the placement of the single quotes; I also put parentheses around the VALUES part.

  3. #3
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement in VB code - need help (xp)

    Perfect Hans! Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •