Why do I get a syntax error when importing an excel file?

Lopez, Ahiezer 216 Reputation points
2023-08-01T20:36:02.0566667+00:00

Im not sure why I have this code error when trying to import an excel file. Please help.

User's image

User's image

File TypeUser's image

Im not sure why I have this code error when trying to import an excel file. Please help.

User's image

User's image

File Type0User's image

Code:

Private Sub GetUmFile()
'Get File
    Dim wb As Excel.Workbook
    Dim xlApp As Excel.Application
    Dim db As DAO.Database
    DoCmd.SetWarnings False
    If fTableExists("tblHTSAudit") Then
        DoCmd.RunSQL "Delete From tblHTSAudit"
    End If
    DoCmd.SetWarnings True
    If SheetList.Value = "" Then
        MsgBox "WorkSheet not selected"
        Exit Sub
    End If
Ender:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "tblHTSAudit", ImpFile.Value, True, SheetList.Value & "!"
    strSQL = "DELETE tblHTSAudit.[ITEM NO]" _
        & vbCr & "FROM tblHTSAudit" _
        & vbCr & "WHERE (((tblHTSAudit.[ITEM NO]) Is Null));"
        
        
   Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From tblHTSAudit")
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        
        While (Not rs.EOF)
'            Debug.Print rs.Fields("BUSINESS UNIT")
            
            If rs.Fields("BUSINESS UNIT") <> "Hussmann" And rs.Fields("BUSINESS UNIT") <> "Commercial Insulating Glass" And rs.Fields("BUSINESS UNIT") <> "Commercial Refrigerator Door" Then
            MsgBox "The following data record has an error in the BUSINESS UNIT field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "BUSINESS UNIT ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

Debug.Print sql

DoCmd.SetWarnings False

DoCmd.RunSQL sql
DoCmd.SetWarnings True
GoTo line:
            
            
            End If
'            On Error GoTo line
            If IsNull(DLookup("HTSUS", "_USHTS", "HTSUS = '" & rs.Fields("HTSUS") & "'")) Then
                MsgBox "The following data record has an error in the HTSUS field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "HTSUS ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

            Debug.Print sql
            
            DoCmd.SetWarnings False
            
            DoCmd.RunSQL sql
            DoCmd.SetWarnings True
GoTo line:
            
            'if error about record being deleted go onto next record
            

            End If




            If IsNull(DLookup("ECCN", "dbo_ECCN", "ECCN = '" & rs.Fields("ECCN") & "'")) Then
                MsgBox "The following data record has an error in the ECCN field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "ECCN ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

            Debug.Print sql
            
            DoCmd.SetWarnings False
            
            DoCmd.RunSQL sql
            DoCmd.SetWarnings True
    GoTo line:
            
            'if error about record being deleted go onto next record
            

            End If
line:
            rs.MoveNext
        
        Wend
    
    End If
    rs.Close



    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "Done"
End Sub
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,291 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
270 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,286 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
797 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Tanay Prasad 2,100 Reputation points
    2023-08-02T07:19:28.72+00:00
    0 comments No comments