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
– Darren Bartrup-Cook, Commented Apr 17, 2018 at 8:56

Sergey S. · Accepted Answer · 2018-04-17 08:58:26Z

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, 2018 at 8:58

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andre Over a year ago

Just to add to this answer: How to debug dynamic SQL in VBA @SabrinaMordini

Sabrina Mordini Over a year ago

Thank you, I needed to put the single quotes around myID, but when i tried this, I forgot an &. Now it works! Thank you!

Aswani Madhavan · Accepted Answer · 2018-04-17 09:02:48Z

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, 2018 at 9:02

Aswani Madhavan

8166 silver badges19 bronze badges

Collectives™ on Stack Overflow

Runtime error '3075' in VBA

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related