INSERT Sql problems

[agehoops]

Trying to run an SQL command in the VBA to insert the current date and time into a table but I keep getting a syntax error? Any help?
Thanks

DoCmd.RunSQL "INSERT INTO tblAccessLog " _
             & "User, DateLoggedIn, TimeLoggedIn) VALUES " _
             & "(Username,Date,Time);"

The Username value is a variable declared within the code, and the Date and Time values are just the built in VBA functions.

 

[KeithG]

In the above example you are missing a ( before User

 

[agehoops]

ah yea so i was sorry. my mess up. Ok now it works however it is asking me to type in each of the three values. Why isn't it just automatically getting them?

 

[KeithG]

Because it is looking at all of your variables as a string since they are surrounded in quotes"" .Try the below

& "(" & Username & "," & Date & "," & Time & ");"

 

[agehoops]

Ah ok fair enough. Well that seems to work for the Username and Date however it throws another error to do with the time, saying :

"Syntax Error (Missing Operator) in query expression '16:41:12'."

 

[KeithG]

Try this

& "(" & Username & "," & Date & ",#" & Time & "#);"

 

[KeithG]

Why store the date & time in different fields?

 

[agehoops]

That sorta works, however it's not brining up a prompt box asking the user to enter something for the value which is actually the value of the username

So for example, the username variable is set to Admin, it is asking you to enter data for Admin????


It's for a logging script to log the date and time that the user logs into the system

 

[neileg]

It's for a logging script to log the date and time that the user logs into the system

NOW returns both date and time so you don't need two functions.

 

[KeithG]

It sounds like you are still passing that vairable in a string, so Access thinks it is a parameter instead of a variable holding a value. Can you post your SQL again. Also I would try replacing the variable with the CurrentUser function.

 

[agehoops]

DoCmd.RunSQL "INSERT INTO tblAccessLog " _
             & "(User, DateLoggedIn, TimeLoggedIn) VALUES " _
             & "(" & Username & "," & Date & ",#" & Time & "#);"

That's the code i'm using at the moment which does work except for it asking me to input data still.

I can't really use the CurrentUser function, because i've created a completely custom login script so the users are stored in a table rather than through the built in access login control.

NOW returns both date and time so you don't need two functions.

Yea i know about the NOW function, but I was hoping to have it split so as to make it slightly easier for viewing and sorting, so it can be sorted by time or date more quickly and efficiently at a later date if needed.