Run-time error 3134 - syntax error? (1 Viewer)

[BonnieG]

I am getting an intermittent error with my database running on SQL server with on-click actions written in VB.

The error is...

Run-time error '3134'
Syntax error in INSERT INTO statement

When I go to Debug it highlights the following area of code as a problem:

actioned_by = Environ("username")
SQL = "INSERT INTO dbo_ipms_user_change (ipms_user_id, username, change_type, action_date, actioned_by, user_change_source, user_change_summary, user_change_description, ipms_change_id) SELECT " & ipms_user_id & ", '" & username & "', 'no change', Now(), '" & actioned_by & "', 'leavers list', '" & leavers_list_notes & "', '', " & leavers_list_multichange_id & ""
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Is there anything strange-looking about this piece of code? I've had no troubles with this sort of thing in the past so am at a loss.

It seems to be happening intermittently too, which is weird.

 

[pr2-eugin]

You have the Word SELECT where it should be a VALUES keyword and also needs to be enclosed in parentheses..

SQL = "INSERT INTO dbo_ipms_user_change (ipms_user_id, username, change_type, action_date, actioned_by, " & _
    "user_change_source, user_change_summary, user_change_description, ipms_change_id) " & _
    "[COLOR=Red][B]VALUES([/B][/COLOR]" & ipms_user_id & ", '" & username & "', 'no change', #" & [COLOR=Red][B]Date()[/B][/COLOR] & "#, " & _
    "'" & actioned_by & "', 'leavers list', '" & leavers_list_notes & "', '', " & leavers_list_multichange_id & "[COLOR=Red][B])[/B][/COLOR]"

The other changes I have is replaced the Now() with Date() as I believe you need only the date part.. and have enclosed it inside ##.. As String needs to be placed between single quotes so are dates to be enclosed between ##..

 

[PeterF]

You let the SQL process the now() function, I find most of the times running SQL from VBA with VBA native functions won't work.

Replacing the DoCmd.RunSQL with currentDB.Execute will remove the need for setting the warnings False/True

 

[BonnieG]

Hmmm thanks guys. I have changed my code to this:

SQL = "INSERT INTO dbo_ipms_user_change (ipms_user_id, username, change_type, action_date, actioned_by, user_change_source, user_change_summary, user_change_description, ipms_change_id) VALUES (" & ipms_user_id & ", '" & username & "', 'no change', #" & Date() & "#, '" & actioned_by & "', 'leavers list', '" & leavers_list_notes & "', '', " & leavers_list_multichange_id & ")"
CurrentDb.Execute SQL

Still getting the same error though...

 

[pr2-eugin]

Did you walk through all the values that you are not assigning Text to Number?? Or Date to Text?? Check the type of action_date and see if it actually Date/Time.. Also try a Debug.Print before Execute..

 

[MSAccessRookie]

Your INSERT statement seems to be OK, so there are some things to look into regarding the Table dbo_ipms_user_change:

• Make sure that none of the Fields that are being updated are defined as AutoNumber, because that is not allowed.
• Make sure that all of the required Fields in the Table are updated by the Query.

In the event that you have already checked on both of these items, then the next responder's suggestion will be in order.

-- Rookie

 

[BonnieG]

I've uploaded a screencap of the table structure as it didn't seem to want to let me copy and paste. I think it's okay?

What should Debug.Print do? I've added it into the code and I'm just getting the run-time error still.

 

[pr2-eugin]

Debug.Print will print the statement in the immediate window..

SQL = "INSERT INTO dbo_ipms_user_change (ipms_user_id, username, change_type, action_date, actioned_by, user_change_source, user_change_summary, user_change_description, ipms_change_id) VALUES (" & ipms_user_id & ", '" & username & "', 'no change', #" & Date() & "#, '" & actioned_by & "', 'leavers list', '" & leavers_list_notes & "', '', " & leavers_list_multichange_id & ")"

Debug.Print SQL

CurrentDb.Execute SQL

After executing the above code hit CTRL+G, this will open the immediate window copy what you have in the immediate window and paste it here.. lets see whats happening there..

 

[BonnieG]

INSERT INTO dbo_ipms_user_change (ipms_user_id, username, change_type, action_date, actioned_by, user_change_source, user_change_summary, user_change_description, ipms_change_id) VALUES (25484, 'JENNIFERLA', 'no change', #12/12/2012#, 'BonnieG', 'leavers list', 'Left on 09-Dec-2012.', '', )

 

[pr2-eugin]

As you can see that the last value is left empty..

ipms_user_id              -    25484
username                  -    'JENNIFERLA'
change_type               -    'no change'
action_date               -    #12/12/2012#
actioned_by               -    'BonnieG'
user_change_source        -    'leavers list'
user_change_summary       -    'Left on 09-Dec-2012.'
user_change_description   -    ''
[B]ipms_change_id[/B]            -

So see what value you have to upload there..

 

[BonnieG]

Ahhhhhhhhhhhhhhhh it must just be a case of missing single quotes around " & leavers_list_multichange_id & "

!!!

Just changed it to '" & leavers_list_multichange_id & "' and it works.



Sometimes it's so easy to miss these things!

Thank you for pointing me in the right direction everyone!