Runtime error '3075' in VBA

Question

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

In the database is `Result` a text field and `ID` a numeric field? When debugging what is the value of `var` & `myID`? You say _like there is an apostrophe_ - is there? You can place a break-point on that line of code so it pauses there and you can check the values. — Darren Bartrup-Cook, Apr 17 '18 at 08:56

score 1 · Accepted Answer · answered Apr 17 '18 at 08:58

1

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.

answered Apr 17 '18 at 08:58

Sergey S.

6,296
1
14
29

Just to add to this answer: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) @SabrinaMordini – Andre Apr 17 '18 at 09:21
Thank you, I needed to put the single quotes around myID, but when i tried this, I forgot an &. Now it works! Thank you! – Sabrina Mordini Apr 17 '18 at 09:48

score 0 · Answer 2 · answered Apr 17 '18 at 09:02

0

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

answered Apr 17 '18 at 09:02

Aswani Madhavan

816
6
19

Runtime error '3075' in VBA

2 Answers2