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

[dcavaiani]

' 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'

 

[vbaInet]

Debug.Print SQL to the immediate window and you will spot the syntax error.

 

[dcavaiani]

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?

 

[vbaInet]

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.

 

[dcavaiani]

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).

 

[vbaInet]

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.

 

[dcavaiani]

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.

 

[vbaInet]

Good job!

If you don't want that warning message, you can use the DAO Execute method:

CurrentDb.Execute SQL, dbFailOnError

 

[dcavaiani]

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?