CODEWITHSUNDEEP
sqlvbams-access
Sabrina Mordini
Sabrina Mordini

Reputation: 5

Runtime error '3075' in VBA

I know there are plenty of posts with the same title, but I couldn't find anything similar to my problem.

I'm trying to update a record with SQL in VBA. In particular, my code goes to an excel file, extracts a value, asks the user for the ID of the record that needs to be updated, and then should procced to do the update. The thing is i tried running the same code without the user input, and works just fine, so the problem must be with the interpretation of the input. I checked that everything is a string but i don't know how to solve the problem.

Here is the code I am using:

Dim query2 as string
Dim myID as string
myID = InputBox("Insert ID:")
query2 = "UPDATE [Info test] " & "SET RESULT = " & var & "WHERE ID =" & myID
DoCmd.RunSQL (query2)

In this case, var is a string, and is the value i fetch from the excel file. From various tests, i think the problem is with the last & myID, like there is an apostrophe before the value stored by myID, but i don't know what to do about it, or if this is really the problem.

Thanks to everybody who can help

Upvotes: 0

Views: 66

Answers (2)

Aswani Madhavan
Aswani Madhavan

Reputation: 816

If var is a string, then you need to use single quotes and add space before WHERE like shown below

query2 = "UPDATE [Info test] " & "SET RESULT = '" & var & "' WHERE ID =" & myID

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

Insert Debug.Print query2 before DoCmd, run the code and check output in immediate window. This SQl should work in query builder. I believe you need to enclose var in single quotes and add a space before WHERE

query2 = "UPDATE [Info test] SET RESULT = '" & Replace(var, "'", "''") & "' WHERE ID =" & myID

I added Replace in order to avoid errors if var contains single quote.

Upvotes: 1

Related Questions