SQL Query based off List Box

[kagoodwin13]

I am trying to run an UPDATE SQL command based on items selected in a list box.



The purpose of this form is to submit a document (ex. Purchase Order) based on a part number. The database user browses to the applicable document, and selects the appropriate part numbers. The database then saves the file location of the part number document, so that the link can later be opened with a report. The list box on the form has a database-controlled list of the part numbers, which is the primary key of the database.

The purpose of the SQL command is to overwrite a part number to prevent part number errors. The Excel file that is being imported by this code is too prone to user formatting errors when referring to the part number, so I am trying to create an error-proof way of selecting the applied part number for each document.

When I try to submit part number 1234567890, I am currently having a problem with this code:

For Each varItem In Me.listPART_NUMBER.ItemsSelected
                strPathFile = Me.txtFILE_PATH
                strTable = "tblPCP1_APPROVAL"
                Sheet_Name = "DOCUMENT_APPROVAL" & "!"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name
                DoCmd.RunSQL ("UPDATE tblDOCUMENT_APPROVAL SET tblDOCUMENT_APPROVAL.PN = " & Me.listPART_NUMBER.ItemData(varItem) & " WHERE PN Is Null;")
               Next varItem

Run-Time error '3075': Syntax error (missing operator) in query expression '1234567890'

I also tried this, but it was a syntax error:

For Each varItem In Me.listPART_NUMBER.ItemsSelected
                strPathFile = Me.txtFILE_PATH
                strTable = "tblPCP1_APPROVAL"
                Sheet_Name = "DOCUMENT_APPROVAL" & "!"
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name
                DoCmd.RunSQL ("UPDATE tblDOCUMENT_APPROVAL SET tblDOCUMENT_APPROVAL.PN = " '" & Me.listPART_NUMBER.ItemData(varItem) & "' " WHERE PN Is Null;")
               Next varItem

Any help?

[Dal Jeanis]

If PN is a text string field, This would be the syntax:

 DoCmd.RunSQL ("UPDATE tblDOCUMENT_APPROVAL SET tblDOCUMENT_APPROVAL.PN = '" & Me.listPART_NUMBER.ItemData(varItem) & "' WHERE PN Is Null;")

Also, for what it's worth, I usually build the SQL into a local string variable a few lines before, so when I encounter issues like this, I can debug.print the field and look at the SQL myself.

[rpeare]

Let me back up dal's post. Creating the string before you try to execute it and debug.printing it before you actually issue the command is going to save you a lot of time and aggravation, it's an extra line or two of code but it's well worth it.

You may also be having a problem if PN is a unique key and you are trying to update your table when there is more than one record with a null PN field.

[kagoodwin13]

Dal, your post solved my syntax error. Thanks.

I tried to save the SQL into strSQL and then use the command Currentdb.Execute strSQL, but that also did not work. I kept getting errors about "Too few parameters."

[Dal Jeanis]

OKay, post the SQL from the debug.Print message here.