[RESOLVED] sql syntax error

[rochak]

can someone please tell me what is wrong in syntax of this statement

Dim strUser As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim sql As String

strUser = Environ("Username")
sql = "INSERT INTO LogTable(Username,Date,Time,EditFields) VALUES('strUser',date,time,'New Mailbox created');"
dbs.Execute sql


It keeps giving a syntax error in "Insert Into" statement. Thanks

Oh .. forgot to mention.. the LogTable has five fields (the first one is autogenerated number). Can it be the reason for the error?

[dee-u]

Try this...

SQL = "INSERT INTO LogTable(Username,[Date],[Time],EditFields) VALUES('strUser','" & Date & "','" & Time & "','New Mailbox created');"

[rochak]

Brilliant dee-u!! it's working. By the way, as I had expected in my logtable username field it is storing "strUser" instead of my actual strUser which is a variable. How do I modify my syntax for the strUser variable?

Also, if you have time can you please explain why you put [Date] and [Time] in square brackets.. Thanks a lot

[dee-u]

It is because Date and Time are Visual Basic functions.

SQL = "INSERT INTO LogTable(Username,[Date],[Time],EditFields) VALUES('" & strUser & "','" & Date & "','" & Time & "','New Mailbox created')

[rochak]

Thank you. All problem solved.

[dee-u]

You could mark this thread as resolved now. Take a look at my sig on how to do it.