SQL string in access VBA not behaving as expected

I have a bit of access VBA that for the life of me, I cant work out what is wrong.

First off, here is the offending section of code, and I’ll explain what I want and what it’s doing below.

        loguser = Environ$("USERNAME")
        sqlstr = "INSERT INTO bypass_logins ([timestamp], [selected_user], [logged_in_user]) VALUES (" & Format(Now(), "dd/MM/yyyy") & "," & gstrUser & "," & loguser & ")"
        Debug.Print sqlstr
        'runs sql string above to write back to db
        DoCmd.SetWarnings False
        DoCmd.RunSQL sqlstr
        DoCmd.SetWarnings True

The first line is meant to capture the username logged into the PC running the code, which it does and is confirmed with a debug.print monitoring this. the sqlstr is meant to then write a date, the selected username from a box on the form in front of this code (gstrUser) and the above username to the respective fields in the database table “bypass logins”.

However, what I am seeing happen when this string computes, is a popup box with the username returned by loguser as the only text, and then an entry box with an ok button. Whatever I enter into the entry field on this popup is what is entered into the sqlstr and then processed by DoCmd.RunSQL sqlstr and written back to the database. If I leave the box blank and hit ok, then nothing is written in this field and the string becomes INSERT INTO bypass_logins ([timestamp], [selected_user], [logged_in_user]) VALUES (13/08/2020,43,) (for example)

Not quite sure whats going on here, or where I’ve gone wrong but I’m thinking possibly the special characters used in the string creation to return the variables?

Thanks in advance

Answer

You cannot go without delimiters for text and datetime, and your date format must be adjusted, so try:

sqlstr = "INSERT INTO bypass_logins ([timestamp], [selected_user], [logged_in_user]) VALUES (#" & Format(Date(), "yyyy/mm/dd") & "#,'" & gstrUser & "','" & loguser & "')"
    D

Advertisement

Answer