Why am I getting an error 91 - Object variable or With block variable not set

[Brad Nerbovig]

I am getting this error when connecting to an Excel file from Access 2010. The weird thing is, if I click on okay when the routing fails, and then run it again it works just fine. In fact, if I run it again anytime after I have gotten this error it works just fine. The only time the error crops up is the first time I run the routine after opening up the database. There's a lot of code in this routine which I'll post, but the error occurs at line 65 with the first "Set rstReceiveData = conReceive.Exec ute(strQuery)" statement which is the first time I execute a query against the Excel file. Also, don't laugh at my code. I'm an amatuer. ;P

Private Sub cmdImport_Click()
'Purpose:  To import customer receivings from the pre-formatted receiving spreadsheet
'Description:
 
On Error GoTo Err_cmdImport_Click
 
    'Set all necessary variables
    Dim conReceive As New ADODB.Connection
    Dim rstReceiveData As ADODB.Recordset
    Dim strSheet As String, strQuery As String, strSrcField As String, strDesField As String
    Dim strDesName As String, strSrcName As String, strImportDir As String
    Dim strMessage As String, strGrouping As String
    Dim dbsPDSCWD As Database
    Dim rstImportSchema As Recordset, rstReceiveHeader As Recordset, rstReceiveDetail As Recordset, rstItems As Recordset
    Dim retValue As Variant
 
    Set dbsPDSCWD = CurrentDb
 
    DoCmd.Hourglass True
    'Intialize variables
    Me!txtStatus = "Intializing variables..."
    Me!prbStatus.Value = 10
    Me.Repaint
    strImportDir = DLookup("txtCustDirName", "tblAllCustomers", "pkeyCustNo = '" & Me!cboCustNo & "'")
    strSheet = "Receiving$"
 
    'Create recordsets for the Schema, Header, and Detail tables
    strQuery = "SELECT * FROM tblSchema WHERE txtSchCustNo = '" & Me!cboCustNo & "' AND txtSchType = 'Receiving'"
    Set rstImportSchema = dbsPDSCWD.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)
    Set rstReceiveHeader = dbsPDSCWD.OpenRecordset("tblOEReceivingHeader", dbOpenDynaset)
    Set rstReceiveDetail = dbsPDSCWD.OpenRecordset("tblOEReceive", dbOpenDynaset)
    Set rstItems = dbsPDSCWD.OpenRecordset("tblOEItems", dbOpenDynaset)
 
    'Create the connection to the receiving Excel file
    Me!txtStatus = "Connecting to source file..."
    Me!prbStatus.Value = 20
    Me.Repaint
    With conReceive
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties").Value = "Excel 8.0;IMEX=1"
        .Open strImportDir & Me!cboCustNo & "Receiving.xls"
    End With
 
    strQuery = "SELECT * FROM tblSchema WHERE txtSchCustNo = '" & Me!cboCustNo & "' AND txtSchType = 'Items'"
    Set rstImportSchema = dbsPDSCWD.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)
 
    'Build the SQL string for the summarized list of items
    Me!txtStatus = "Creating list of items..."
    Me!prbStatus.Value = 30
    Me.Repaint
    strQuery = "SELECT "
    rstImportSchema.MoveFirst
    Do While Not rstImportSchema.EOF
        If rstImportSchema!txtSchSrcFieldName = "ItemNo" Then
            strQuery = strQuery & strSheet & "." & rstImportSchema!txtSchSrcFieldName & ", "
            strGrouping = "GROUP BY " & rstImportSchema!txtSchSrcFieldName
        Else
            strQuery = strQuery & "First(" & strSheet & "." & rstImportSchema!txtSchSrcFieldName & ") AS " & rstImportSchema!txtSchSrcFieldName & ", "
        End If
        rstImportSchema.MoveNext
    Loop
 
    strQuery = Left(strQuery, Len(strQuery) - 2)
    strQuery = strQuery & " FROM [" & strSheet & "] " & strGrouping
    Set rstReceiveData = conReceive.Execute(strQuery)
 
    rstImportSchema.MoveFirst
    rstReceiveData.MoveFirst
 
On Error Resume Next
 
    'Add new items to items table
    Me!txtStatus = "Adding new items to database..."
    Me!prbStatus.Value = 40
    Me.Repaint
    Do While Not rstReceiveData.EOF
        With rstItems
            .AddNew
            Do While Not rstImportSchema.EOF
                strDesName = rstImportSchema!txtSchDesFieldName
                strSrcName = rstImportSchema!txtSchSrcFieldName
                .Fields(strDesName).Value = rstReceiveData.Fields(strSrcName).Value
                rstImportSchema.MoveNext
            Loop
            !pkeyItemCustNo = Me!cboCustNo
            !txtItemUser = TempVars!CurrentUser
            !txtItemLoc = "Not assigned"
            !dtmItemDateTime = Now()
            !ysnItemObs = False
            .Update
        End With
        rstImportSchema.MoveFirst
        rstReceiveData.MoveNext
    Loop
 
    rstItems.Close
 
On Error GoTo Err_cmdImport_Click
 
    'Add receiving header record
    Me!txtStatus = "Adding header records..."
    Me!prbStatus.Value = 50
    Me.Repaint
    strQuery = "SELECT * FROM tblSchema WHERE txtSchCustNo = '" & Me!cboCustNo & "' AND txtSchType = 'Receiving' " & _
    "AND txtSchFormat = 'Header'"
    Set rstImportSchema = dbsPDSCWD.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)
 
    'Build strQuery for summarized list of headers
    strQuery = "SELECT "
    rstImportSchema.MoveFirst
    Do While Not rstImportSchema.EOF
        If rstImportSchema!txtSchSrcFieldName = "ReceivingNo" Then
            strQuery = strQuery & strSheet & "." & rstImportSchema!txtSchSrcFieldName & ", "
            strGrouping = "GROUP BY " & rstImportSchema!txtSchSrcFieldName
        Else
            strQuery = strQuery & "First(" & strSheet & "." & rstImportSchema!txtSchSrcFieldName & ") AS " & rstImportSchema!txtSchSrcFieldName & ", "
        End If
        rstImportSchema.MoveNext
    Loop
 
    strQuery = Left(strQuery, Len(strQuery) - 2)
    strQuery = strQuery & " FROM [" & strSheet & "] " & strGrouping
    Debug.Print strQuery
    Set rstReceiveData = conReceive.Execute(strQuery)
 
    rstImportSchema.MoveFirst
    rstReceiveData.MoveFirst
 
    Do While Not rstReceiveData.EOF
        If IsNull(rstReceiveData.Fields("ReceivingNo").Value) Then
        Else
            With rstReceiveHeader
                .AddNew
                Do While Not rstImportSchema.EOF
                    strDesName = rstImportSchema!txtSchDesFieldName
                    strSrcName = rstImportSchema!txtSchSrcFieldName
                    .Fields(strDesName).Value = rstReceiveData.Fields(strSrcName).Value
                    rstImportSchema.MoveNext
                Loop
                !pkeyRecvHdCustNo = Me!cboCustNo
                !txtRecvHdUserID = TempVars!CurrentUser
                !dtmRecvHdDateTimeStamp = Now()
                !intRecvHdStat = 1
                .Update
            End With
        End If
            rstImportSchema.MoveFirst
            rstReceiveData.MoveNext
    Loop
 
    rstReceiveData.Close
    rstReceiveHeader.Close
    rstImportSchema.Close
 
    'Add the detail records
    Me!txtStatus = "Adding detail records..."
    Me!prbStatus.Value = 60
    Me.Repaint
    strQuery = "SELECT * FROM [" & strSheet & "]"
    Set rstReceiveData = conReceive.Execute(strQuery)
 
    strQuery = "SELECT * FROM tblSchema WHERE txtSchCustNo = '" & Me!cboCustNo & "' AND txtSchType = 'Receiving' " & _
    "AND txtSchFormat = 'Detail'"
    Set rstImportSchema = dbsPDSCWD.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)
 
    rstImportSchema.MoveFirst
    rstReceiveData.MoveFirst
 
    Do While Not rstReceiveData.EOF
        If IsNull(rstReceiveData.Fields("ItemNo")) Then
        Else
            With rstReceiveDetail
                .AddNew
                Do While Not rstImportSchema.EOF
                    strDesName = rstImportSchema!txtSchDesFieldName
                    strSrcName = rstImportSchema!txtSchSrcFieldName
                    .Fields(strDesName).Value = rstReceiveData.Fields(strSrcName).Value
                    rstImportSchema.MoveNext
                Loop
                !txtRecvCustNo = Me!cboCustNo
                !txtRecvUser = TempVars!CurrentUser
                !dtmRecvDateTimeStamp = Now()
                .Update
            End With
        End If
                rstImportSchema.MoveFirst
                rstReceiveData.MoveNext
                intRecordCount = intRecordCount + 1
    Loop
 
    Me!txtStatus = "Import Complete!"
    Me!prbStatus.Value = 70
    Me.Repaint
 
    rstReceiveData.Close
    rstReceiveDetail.Close
    conReceive.Close
 
Exit_cmdImport_Click:
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
 
Err_cmdImport_Click:
    Select Case Err
        Case 3022
            strMessage = "Receiving number " & rstReceiveHeader!pkeyRecvHdNo & " already exists for this customer.  " & _
            "The same receiving number cannot be used for a customer twice.  Please correct the receiving number " & _
            "and re-import.  Receiving has not be imported."
            MsgBox strMessage, vbOKOnly + vbCritical, "Import Error"
            Me!txtStatus = "Import failed!"
            Me!prbStatus.Value = 70
            Me.Repaint
            Resume Exit_cmdImport_Click
        Case -2147467259
            Resume Next
        Case Else
            MsgBox Err & " - " & Err.Description
            strActivity = ActivityLog("System error", Me.Name, Err & " - " & Err.Description)
            Me!txtStatus = "Import failed!"
            Me!prbStatus.Value = 70
            Me.Repaint
            Resume Exit_cmdImport_Click
    End Select
 
End Sub

[ADezii]

Just for curiosity, insert this Line after Code Line# 65 to see if the Recordset actually exists after the Initial Opening of the DB.

MsgBox IIf(Not rstReceiveData Is Nothing, "Recordset exists", "Recordset does not exist")

[Brad Nerbovig]

I would, except the error occurs at line 65. If it gets past that line, which it does after it has errored out the first time, then everything is groovy. Inserting the line of code you have above returns the "Recordset exists" msgbox the second time I run the routine. It never gets there on the first run.

For some reason, it is not recognizing the connection to the Excel file when I run through it the first time. I'm wondering if there's a library or reference that is not getting loaded properly until it goes through the routine a second time...