Error running DoCmd.RunSQL

[Cyd44]

I am trying to capture a users login ID when entering the dbase and write the ID and NOW() to a log file. My code is as follows:-

 NewDate = Now()
        uDate = NewDate
        ID = Me!cboEmployee
       MsgBox (NewDate & NewName & uDate)(This Shows correct Detail)
        
        strSQL = "INSERT INTO tbleUserLog (ID,uDate)"
        
        DoCmd.RunSQL (strSQL)

I get a runtime errors on the DoCmd statement as follows

Runntime Error 3134 Syntax Errorn in INSERT INTO Statement.

I am quite new to access so my question is probably quite stupid but could someone please advise.

[jimatqsi]

Should be something like this:

INSERT INTO tbleUserLog ( UserID, uDate )SELECT Now() AS Udate, ID AS UserID

Jim

[Cyd44]

Thank you Jimatqsi, the satement runs but I then get a systems parameter box and it is look for me to give the required ID and Date

Does not appear to pass the values in the INSERT statement

[NeoPa]

Let's start with How to Debug SQL String and When Posting (VBA or SQL) Code. We can continue when you've posted your SQL string for the question.

[NeoPa]

I just looked at the code again and you needn't worry about posting the SQL. There are no variables in it to make it anything other than the string included on your line #6. I can happily work with that (It's still an important point to remember generally for working with SQL but we needn't worry overly here).

Your SQL string is half missing. You've told it where to enter the data, but have not supplied any data to enter. There are two alternative ways to supply data to such a query, one is in a value list and the other is in a recordset (FROM clause).

There are also two ways of learning more about this. One is by using the Help system (See Finding Jet SQL Help.) and the other is to use the Query Designer to create such a query for you and see what the resultant SQL is.

PS. That's not right again. Your code is a little bizarre so I misread it again. It appears you are trying to pass values and haven't specified the fields to copy the data to (although the syntax used is the one for specifying that). The same advice will sort you out though. Check either help or an Access created query to see how it should be done. In this case you are looking for the VALUES alternative.

Let us know how you get on with it. I'm sure we can help more if you get stuck but you shouldn't from there.

[Cyd44]

Hi Again,

Jimatqsi had also spotted that and gave me a better syntax which works but it info does not appear to have been passed to INSERT.

[jimatqsi]

Cyd44, the problem is most likely that the column names in the table tbleUserLog are not 'ID' and 'uDate'. I made that assumption from looking at the small piece of code you posted.

You have to know what your table structure looks like in order to properly form the SQL to operate on the table. Make the appropriate name changes to the SQL I gave you, based on your table structure.

As an example, this code is adding a row to the table Users. The column names of that table are "UserID" and "UserEmail "

INSERT INTO Users ( UserID, UserEmail ) SELECT "Jim" AS UserID, "jim@mydomain.com" AS UserEmail;

I have hard-coded the data; your data will come from your form and from the Date function.

Jim

[NeoPa]

Cyd44:
Jimatqsi had also spotted that and gave me a better syntax which works but it info does not appear to have been passed to INSERT.

So at this point you tell us what you tried and what (exactly) happened. The Error Message would be useful and any noticed results (IE. No records added).

[Cyd44]

Hi Jjimasqi

Here is my amended code

 Dim strSQL As String
        newID = Me!cboEmployee
        MsgBox (newID)
        
        strSQL = "INSERT INTO tbluserLog ( ID, uDate )SELECT DATE() AS Udate, newID AS ID"


        
        DoCmd.RunSQL (strSQL)
        MsgBox ("Info" & " " & ID & Date)

The code runs but it is still asking me for a parameter. I have checked back at the log file and we only have 2 fields
1 is the ID field (key field - Autonumber)
2 is uDate Date/Time Short Date

For some reasom the the strSQL is not assigning the Variables

[NeoPa]

You have the fields the wrong way around I'm afraid.

Try either :

strSQL = "INSERT INTO tbluserLog ( [ID], [uDate] ) SELECT " & newID & ", DATE()"

or :

strSQL = "INSERT INTO tbluserLog ( [ID], [uDate] ) VALUES (" & newID & ", DATE())"

Both should be valid. The latter format is provided specifically for entering literal values whereas the former is mainly for transferring data from one table into another (though can be used, in exactly the way Jim does, to handle literal values for a single record too).

[Cyd44]

Have been trying different things and my code is now

MsgBox (Me!cboEmployee)
        
        newdate = Now()
        udate = newdate
        newID = Me!cboEmployee
        MsgBox (newdate & newID & udate)
        
        strSQL = "INSERT INTO tbleUserLog ( ID, uDate )SELECT Now() AS Udate, newID AS ID"


        
        DoCmd.RunSQL (strSQL)

It works with no Syntax errors and the MsgBox Command shows the corect info. However, I still get a parameter box. When I enter the datils required I get an Error "Cant find tbluserLog?

[jimatqsi]

Is there a typo in your code for the name of the table? "Can't find tblusreLog" means exactly that.

In your code I see this
tbleUserLog
and in your report of the error I see this
tbluserLog

Jim

[NeoPa]

Good spot Jim.

Going by the code and results reported in posts #9 & #11 I would deduce that the table is actually called [tblUserLog] (or case variations of that) and not [tbleuserLog] as originally posted.

This makes sense too, as error messages typically need to be copied manually, so if you don't notice the difference you're likely to type what you think you see. One of the main reasons why it is so important to use Copy / Paste wherever possible. Not possible for an error message though unfortunately. An unavoidable oversight in the circumstances.

[Cyd44]

Thanks guys, I had spotted same and am not getting that any more.

Here is the scenario with current coding as follows:-

strSQL = "INSERT INTO tbluserLog ( ID, uDate )SELECT Now() AS Udate, newID AS ID"

ID is a Long and is defined in a Module because I want to retrieve it before a user logs off.
uDate is DATE/TIME now set as General (guess this is what NOW() requires?)
Logon consist on a Combo Box for Name and an unbound Password Box

-- SNIP --

[NeoPa]

@Cyd44
In case you overlooked the edit comment let me say that you need to post any new questions in separate threads.

Now this one is completed may I suggest that post #2 would make a good Best Answer for this thread?