Import Spreadsheet and add other info to same record

[kagoodwin13]

I am trying to do two things at once:

1. Transfer tabs into Excel
2. Add one field to the transferred record

I have a standardized Excel form that is already in use, and my database must take data from an Excel form to develop reports (# forms passed/failed, etc.). The Excel form is already released and cannot be changed. It was not made with database capture in mind.

The primary problem with the form is that the database's references are based on a part number captured from the form. This part number is 10 digits, but there are many ways that people format it on the form:

1234567890 (database format)
12345-67890
12345 67890

Obviously this creates issues when trying to create a reference in Access. Though humans see no difference in "1234567890" and "12345-67890", Access does not see those as a match.

My work-around for this is to insert the standardized database part number on the document submission form. I already have the DoCmd.TransferSpreadsheet acImport command set up the way I want to. What I am trying to add is a SQL string to also insert the standardized part number on the same record. When I have tried before, Access sees two separate commands: transfer spreadsheet, then insert part number. Because of that, Access makes two separate records.

I've attached an example database and spreadsheet. Any ideas?

Insert and Add SQL Example.mdb
Insert and Add SQL Example xls.xls.zip

(Warning: The file browser module and "Browse" may not work on Access 2010 without messing with References)

[ssanfu]

If I understand correctly, the Excel file has part numbers that might contain a dash or a space. You want the number in the field PN to be the same as PART_NUMBER minus the dash and/or space.
Try this:

Private Sub Command17_Click()
    Dim strPathFile As String
    Dim strTable As String
    Dim strBrowseMsg As String
    Dim strFilter As String
    Dim blnHasFieldNames As Boolean
    Dim strSQL As String

    '    If IsNull(Me!txtPART_NUMBER) Then
    '        MsgBox "Please provide a Part Number."
    '        Exit Sub
    '    End If

    If IsNull(Me!txtFILE_PATH) Then
        MsgBox "Please browse to a file."
        Exit Sub
    End If

    blnHasFieldNames = True
    strPathFile = Me.txtFILE_PATH

    strTable = "tblDATA"
    Sheet_Name = "EXAMPLE_USER_INPUT" & "!"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

    ' now update the field PN with the value fron field PART_NUMBER.
    'use the replace command to first, replace dashes with "", then replace spaces with ""
    strSQL = "UPDATE tblDATA SET tblDATA.PN = Replace(Replace([PART_NUMBER],'-',''),' ','')"
    strSQL = strSQL & " WHERE [PN] Is Null;"
    CurrentDb.Execute strSQL, dbFailOnError


    MsgBox ("Done")

End Sub

[kagoodwin13]

We're definitely on the right track here. However I keep getting an error on the SQL command: "Run-time error '3061': Too few parameters. Expected 1."

I put the same SQL code in a separate query, and it runs just fine.

Some quick Google hunting has me believing this is an Access bug, not a code error. If I find a solution, I'll post it here.

[ssanfu]

I ran the code in A2010 last night. Worked fine. Could be a copy/paste error.

Add a line before the Currentdb.Execute line:

    strSQL = "UPDATE tblDATA SET tblDATA.PN = Replace(Replace([PART_NUMBER],'-',''),' ','')"
    strSQL = strSQL & " WHERE [PN] Is Null;"
    
    Debug.Print strSQL

    CurrentDb.Execute strSQL, dbFailOnError

Set a breakpoint on the Currentdb.Execute line. What is the text in the immediate window?

[kagoodwin13]

I think my error is coming from trying to set the value for field PN from the text box on the form. Your code works just fine.

I really need a way to SET the PN field from the Access input form. My end goal is to have Access to ignore the user-inputted part number on the Excel form, and instead take part numbers from a filtered list box that has the part numbers in database format. If there are multiple part numbers per document, a list box and a For loop will handle this just fine.

Unfortunately it isn't quite as simple as a formatting error when users enter part numbers on the Excel form. Sometimes the document is the same for two different part numbers, so the users enter in both part numbers on one document. For example, if there were two gloves (left hand and right hand) which had different part numbers, but similar information (cost, supplier, etc.), they want to use the same form for both parts.

Let's say the parts look like this:

PART_NUMBER	DESCRIPTION
1234567891	GLOVE, LH
1234567892	GLOVE, RH

On the Excel form, users write this as "12345-67891 & 12345-67892" or "12345-67891/2" or "12345 67891&2" or any other of numerous ways that won't work in Access. Despite my instructions, they won't enter it in database format. To make the Excel form import correctly into the database, I have to manually pick through each document, then Save As to make a new document for each part number. This gets pretty time consuming because I am managing hundreds of Excel forms. I need this to go smoother if I want to have any time to accomplish anything else.

Update:

This SQL code works as a query, but not as an executed query in VBA. Even when I tell VBA to run the query that works (all SQL code in the query, not in the VBA commands), I get the same error message: "Run-time error '3061': Too few parameters. Expected 1." It's really infuriating at this point.

UPDATE tblDATA SET tblDATA!PN = [Forms]![frmINPUT]![txtPART_NUMBER] WHERE tblDATA!PN Is Null;

[kagoodwin13]

Solution:

DoCmd.RunSQL rather than using CurrentDb.Execute strSQL

Don't know why that makes a rat's fart of difference, but oh well. Problem solved. Thanks for your help!