Help on a SQL Insert into Question

[sc5502]

I am a beginner and need help with a SQL Insert into question. The INSERT INTO has 15 fields to insert into one record. They are text fields. I am doing a

DoCmd.RunSQL "INSERT INTO [dbo_part shortages] (key, buyer, [date added], [part number], description, supplier, program, customer, line, reason, [nsii prod short], [customer ship to short], qty, [inv number], [inv due date]) "
   & "VALUES(" _
   & "'" & Rkey            & "'," _
   & "'" & Rbuyer          & "'," _
   & "'" & Rdateadded      & "'," _
   & "'" & RPartNumber     & "'," _
   & "'" & RDescription    & "'," _
   & "'" & RSupplier       & "'," _
   & "'" & RProgram        & "'," _
   & "'" & RCustomer       & "'," _
   & "'" & RLine           & "'," _
   & "'" & RReason         & "'," _
   & "'" & RNSIIProdShort  & "'," _
   & "'" & RCustomerSTS    & "'," _
   & "'" & Rqty            & "'," _
   & "'" & RInvNumber      & "'," _
   & "'" & RInvDueDate     & "')"    

but am having trouble with punctuation. Any help will be appreciated.

[Rabbit]

Please use code tags when posting code or formatted data.

What trouble are you having with punctuation?

[twinnyfo]

sc5502,

Only your text strings will require single quotes int he insert query. Numbers just need to have the variable. Date may require the "#" tag preceding and following the variable.

What errors are you receiving when you try to run the code?

[zmbd]

I highly recomend that you build your string first (as follows) and then use the string variable in the function.

Dim zSQL As String
 
zSQL = _
    "INSERT INTO [dbo_part shortages] " & _
        "(key, buyer, [date added]" & _
        ", [part number], description" & _
        ", supplier, program, customer, line, reason" & _
        ",  [nsii prod short], [customer ship to short]" & _
        ",  qty, [inv number], [inv due date])" & _
        " VALUES(" & _
        "'" & Rkey & "'" & _
        ", '" & Rbuyer & "'" & _
        ", '" & Rdateadded & "'" & _
        ", '" & RPartNumber & "'" & _
        ", '" & RDescription & "'" & _
        ", '" & RSupplier & "'" & _
        ", '" & RProgram & "'" & _
        ", '" & RCustomer & "'" & _
        ", '" & RLine & "'" & _
        ", '" & RReason & "'" & _
        ", '" & RNSIIProdShort & "'" & _
        ", '" & RCustomerSTS & "'" & _
        ", '" & Rqty & "'" & _
        ", '" & RInvNumber & "'" & _
        ", '" & RInvDueDate & "')"
'>see, now you can insert a debug code to check how
'>your string is actually resolving.
'debug.print zsql
'>
DoCmd.RunSQL

You also a have " ' ' " around items such as [Rqry] if this isn't a text field then you most likely will incure a type error

as this is an action query, you should consider transactions and the Database.Execute Method.... you can trap any errors with the execute methods and with the transaction method you can rollback. f needed.


BTW: To show you this I had to fix a few typos so you might see if the above works in your full code.

[sc5502]

Thank you. I get a runtime error of 3127- Insert into statement unknown field name '[Customer Ship to Short]. I check this and it is in the database. Do you know what is wrong?

[sc5502]

Got my problem resolved. A misspelling. Thank you very much.

[sc5502]

I got it fixed. Thank you.

[zmbd]

Yea... would you mind sharing the final solution for others that might search and find this thread?