INSERT INTO syntax error

[mafhobb]

I am struggling with this:

I get a syntax error in these lines of code:

'insert values into error table
    CurrentDb.Execute "INSERT INTO Error_Table [(User[, Error Number[, Error Description[, Error Form[, Error Control]]]])]" & _
    " VALUES (CurrentUser()[, errornumber[, errorcode[, ErrorForm[, ErrorControl]]]])"

Anyone see anything wrong?

mafhobb

 

[mafhobb]

Ok, I have edited this to the following:

    CurrentDb.Execute "INSERT INTO Error_Table (User, [Error Number], [Error Description], [Error Form], [Error Control])" & _
    " VALUES ('" & CurrentUser() & "', " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & ")"

Now I am getting "error 3075, Syntax error(missing operator)"

mafhobb

 

[mafhobb]

This:

    CurrentDb.Execute "INSERT INTO [Error Table] (User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ('" & CurrentUser() & ", " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & "')"

Now returns "error 3346. Number of query values and Destination fields are not the same"

My guess is that this is happening because the error table has autonumber and time fields not included in this code (autonumber is automatic and time is set to now() as default in the table). Does that mean that this code needs to include all field in the table? If so...How to handle Autonumber??

mafhobb

 

[mafhobb]

Trying to add the time field now, but running into more syntax errors

    CurrentDb.Execute "INSERT INTO [Error Table] (Time,User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ('#Now()#, " & CurrentUser() & ", " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & "')"

I am guessing the the time/date delimiter is causing the problem.

??

mafhobb

 

[namliam]

This:

    CurrentDb.Execute "INSERT INTO [Error Table] (User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ([B][I]'[/I][/B]" & CurrentUser() & ", " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & "[B][U]'[/U][/B])"

Now returns "error 3346. Number of query values and Destination fields are not the same"

My guess is that this is happening because the error table has autonumber and time fields not included in this code (autonumber is automatic and time is set to now() as default in the table). Does that mean that this code needs to include all field in the table? If so...How to handle Autonumber??

mafhobb

No, the values part is only having 1 value, it is all inside '

You want something like:

    CurrentDb.Execute "INSERT INTO [Error Table] (User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ([B][U]'[/U][/B]" & CurrentUser() & "[B][U]'[/U][/B], " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & ")"

Putting only the quotes around the text fields

 

[namliam]

Trying to add the time field now, but running into more syntax errors

    CurrentDb.Execute "INSERT INTO [Error Table] (Time,User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ('#Now()#, " & CurrentUser() & ", " & errornumber & ", " & errorcode & ", " & ErrorForm & " , " & ErrorControl & "')"

I am guessing the the time/date delimiter is causing the problem.

??

mafhobb

You have to
1) Substitute in the NOW like your doing the other values
2) Make sure the now is in US or ISO format

Something along these lines
....values (#" & Format(now(), "YYYY-MM-DD HH:NN:SS") & "# , '" & CurentUser....

 

[JANR]

If you have set the "Time" fields (RESERVED WORD) default value to now() in your table, then there is no need to include it in you insert statement.

It will automaticcaly be added to your table at the moment of insert.

If you insist on include it you must atleast put square brakets around it.

CurrentDb.Execute "INSERT INTO [Error Table] ([Time],User,[Error Number.....

JR

 

[mafhobb]

Namlian, your earlier post made me change the code to this:

    CurrentDb.Execute "INSERT INTO [Error Table] (User,[Error Number],[Error Description],[Error Form],[Error Control])" & _
    " VALUES ('" & CurrentUser() & "', " & errornumber & ", '" & errorcode & "', '" & ErrorForm & "' , '" & ErrorControl & "')"

The values Currentuser, errorcode, errorform and errorcontrol are all text. I surrounded them with ' xxx ' and that took care of the problem

Also, I tried it first without the time field and it worked. i am assuming that this is because the time field int he table has a Now() default value, so if no value is passed on with this code, it simply goes to the default. the same with the autonumber. I did not need to worry about it; it just worked.

JANR, not needing to pass on the Time field basically sdolved the problem you mention. Still, I need to be more vigilant not to use reserved words

Thanks to all!

mafhobb

 

[namliam]

Still, I need to be more vigilant not to use reserved words

while being vigilant...
- Dont use spaces in column/table/query/etc names
- prefix table/query/form/etc names with tbl/qry/frm/etc

ie. tblErrorCode