none
Error 13, Type mismatch RRS feed

  • General discussion

  • Hi,

    The following is for import the excel file to the access database.  Got the "error 13, type mismatch" when open the form.  What is missing in my code that causes the error.

    Private Sub CmdImportAcct_Click()

    Dim strImportFile As String
    Dim lngLoopCounter As Long
    Dim db As DAO.Database
    Dim rst As DAO.Recordset


    If Me.lstAvailImports.ItemsSelected.Count > 0 Then
        strImportFile = "folder path" & Me.lstAvailImports    FileCopy strImportFile, "file name"
        'Run Import and Update Macro
        DoCmd.RunMacro ("m Update Account Status")
           'Set the variable MeDbase to represent the current database
        Set db = CurrentDb
        'Set the RStemp variable to represent a recordset defined as the history table
        'in the current database
        Set rst = db.OpenRecordset("history table")
        'With the RStemp recordset
        With rst
            'Add a new record
            .AddNew
            'Enter the file name into the CIP212Filename field in the recordset
            !CIP212Filename = Me.lstAvailImports
            'Enter the current date/time in the Entered field of the recordset
            !Entered = Now()
            'Post the entries
            .Update
            'Close the recordset
            .Close
        'End With RStemp recordset
        End With
        Set rst = Nothing
        Set db = Nothing
        Form_Open (vbFalse)
        Me.Refresh
    Else
        MsgBox "An item is not selected"
    End If

    End Sub

    Private Sub Form_Click()

    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Dim strCurrFileName As String, strHighFileName As String
    Dim lngRowCount As Long, lngRecordCount As Long

    On Error GoTo ErrorHandler

    Me.lstAvailImports.RowSource = ""

    strCurrFileName = Dir("folder path and file name")
    strHighFileName = DLookup("CIP212Filename", "history table", "Entered = #" & _
        DMax("Entered", "history table") & "#")
       
    lngRowCount = 0
    Do While strCurrFileName <> ""
        If strCurrFileName > strHighFileName Then
            DoCmd.TransferSpreadsheet acLink, _
            "link temp table", _
            "folder path\" & strCurrFileName, vbFalse        lngRecordCount = DCount("*", "link temp table")
            Me.lstAvailImports.AddItem strCurrFileName & ";" & lngRecordCount
            End If
        strCurrFileName = Dir()
    Loop

    Exit Sub

    ErrorHandler:
    MsgBox "Error Encountered: " & Err.Number & " - " & Err.DESCRIPTION
    Resume Next

    End Sub

    Monday, December 3, 2012 10:27 PM

All replies

  • Which line causes the error? (If necessary, comment out the line On Error GoTo ErrorHandler)

    BTW, I assume that

                    lngRecordCount = DCount("*", "link temp table")

    is on a separate line in your 'real' code.


    Regards, Hans Vogelaar

    Monday, December 3, 2012 10:38 PM
  • Thanks for the suggestion. After commented out the line "On Error GoTo ErrorHandler".  I able to debug the error the issue is the missing of the "acSpreadsheetTypeExcel8,"

    DoCmd.TransferSpreadsheet acLink, _
    "link temp table", _

    changed to

    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
    "link temp table", _

    Thanks.

    Tuesday, December 4, 2012 4:41 PM