Run time error 3075 - Can't get it to go thru

dcavaiani

Registered User.
Local time
Today, 14:55
Joined
May 26, 2014
Messages
385
' Run SQL here to insert records into PartMaster

SQL = "INSERT INTO partmaster ( partdesc, uom, averageunitcost, averagequantity, currentunitcost, currentdate, currentsupplier, currentquantity, taxable ) values (" & SavePartNumber & ", " & uomdefault & ", " & averageunitcost & ", " & averageQuantity & ", " & rs!UnitPrice & ", #" & rs!PurchaseDate & "#, " & rs!SupplierName & ", " & rs!Quantity & ", " & rs!taxable & ");"

DoCmd.RunSQL SQL

The rs!SupplierName field has the value "Home Depot" in debug. The currentsupplier field is same text format and the same 25 long.

The error is: Syntax error (missing operator) in query expression 'Home Depot'
 
Debug.Print SQL to the immediate window and you will spot the syntax error.
 
Debug.Print SQL to the immediate window and you will spot the syntax error.

I saw that somewhere, but did not get it - where in the module do I place that?
 
View > Immediate Window

Or

Ctrl + G

Re-run your code with that line added to it and it will show the result in the immediate window.
 
Got that, but don't see an error?

INSERT INTO partmaster ( partdesc, uom, averageunitcost, averagequantity, currentunitcost, currentdate, currentsupplier, currentquantity, taxable ) values ( '', Each, 47.426, 1, 145.06, #4/15/2014#, Home Depot, 1, n);

the partdesc is actually a ditto mark (needs to be scrubbed).
 
Let me jog your memory, how do you handle String in a SQL query? Have a think and then look at the SQL statement again.
 
Let me jog your memory, how do you handle String in a SQL query? Have a think and then look at the SQL statement again.

Awesome! Thank you. "Give a man a fish and he can eat for a day. TEACH him to fish and he can eat forever: ;-)

The """ trick. I need to suppress the "Insert 1 record" msg, but I think I can do that via a Macro step.
 
Good job! :)

If you don't want that warning message, you can use the DAO Execute method:
Code:
CurrentDb.Execute SQL, dbFailOnError
 
vbaInet - Awesome help! Got it all working as I want, except that the last record which should be inserted at rs.EOF is not happening. Is there a simple way to force the final insert at rs.EOF or must I simply copy a section of code and execute it at the end of the loop?
 

Users who are viewing this thread

Back
Top Bottom