Run-time error '3' - Help Please 2

[LittleNick]

Greeting,
TheAceMan1 once showed me how to run command to insert a record into SQL table. I implemented and the codes ran fine. Now I have a very similar process that is attached to a different control, and when I run it I get a "run-time error '3': Closing delimiter not found for the string beginning at position250 in the command. The string begins with: ')}."
This is the code:

Dim db As DAO.Database, strSQL As String
    Dim CurrentUser As String
    
    CurrentUser = DLookup(……)
    Set db = CurrentDb

    strSQL = "Insert into tblUpdateTracking (Userid, datestamp, ValueBefore, ValueAfter, FileID, Description) " & _
             "Values ('" & CurrentUser & "', '" & Now() & "', '" & varBefore & "', '" & Me!OrderNumber & "', " & _
"'" & Me!FileID & "', '" & Me!Description & "')"
        DoCmd.RunSQL (strSQL)

The thing is not everytime I receive this message and when the message pops up, I notice it still write the record into SQL table. However, I just want to see what did I do wrong so that I can correct it.
Thanks for your suggestions or helps

 

[dhookom]

I expect the issue is the delimiter for the date field. You might also have an issue if a text field contains an apostrophe.

Dim db As DAO.Database, strSQL As String
    Dim CurrentUser As String
    
    CurrentUser = DLookup(......)
    Set db = CurrentDb

    strSQL = "Insert into tblUpdateTracking (Userid, datestamp, ValueBefore, ValueAfter, FileID, Description) " & _
             "Values ('" & CurrentUser & "', #" & Now() & "#, '" & varBefore & "', '" & Me!OrderNumber & "', " & _
"'" & Me!FileID & "', '" & Me!Description & "')"
        DoCmd.RunSQL (strSQL)

Duane
Hook'D on Access
MS Access MVP

 

[LittleNick]

Thanks for your reply Duane(dhookom).
You are probably right. In the description field, there may contain an apostrophe. I will take out the date and the description file and run tests on it then I'll add the date back first and if I still do not encounter any problem, then I will also put the description back. That way, I can see what causes the error message.
I really appreciate your helps and thoughs

 

[PHV]

Replace this:
[tt]'" & Me!Description & "')"[/tt]
with this:
[tt]'" & Replace(Me!Description, "'", "''") & "')"[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LittleNick]

It works Great! Thanks PHV for your helps.