[RESOLVED] Error Code 91 - what's the deal?

[dogwomble]

Hey guys,

I'm having a problem with the code below. When it gets to the following line:

        lstTeam.ListIndex = rs!fldTeamNum

I get the following error:

Run-time error 91: Object variable or With block variable not set.

Yes, the objects exist. Remming out that particular line just causes it to occur at the next line. Remming out both those .ListIndex lines avoids the problem altogether. I've set the variables required further up the food chain, so I know that's all fine. I've also used this quite successfully in the past.

I'm scratching my head trying to work this out. (Oh and BTW I know this is probably not the best way of doing it, and I still have some debugging to do, so bear with me on this one...)

Private Sub cmdSearch_Click()
    If txtSurname.Text = "" Or txtFirstName.Text = "" Then
        MsgBox "Please enter first and last names to search for.", vbOKOnly
        Exit Sub
    End If
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(DatabasePath & "ao.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM tblAuthOfficers WHERE fldSurname = '" & txtSurname.Text & "' AND fldFirstName = '" & txtFirstName.Text & "'")
        If rs.RecordCount = 0 Then
            MsgBox "Not found."
            Set rs = Nothing
            Set db = Nothing
            Set ws = Nothing
            Exit Sub
        End If
        OfficerNum = rs!fldKey
        lstDepartment.ListIndex = rs!fldDepID
        lstTeam.ListIndex = rs!fldTeamNum
        If rs!fldPhone <> "" Then
            txtPhone.Text = rs!fldPhone
        End If
        Set ws2 = DBEngine.Workspaces(0)
        Set db2 = ws2.OpenDatabase(DatabasePath & "ao.mdb")
        Set rs2 = db2.OpenRecordset("SELECT * FROM tblSubDepartment WHERE fldDepID = " & Left$(lstDepartment.Text, Val(Left$(lstDepartment.Text, InStr(lstDepartment.Text, vbTab)))))
            While Not rs2.EOF
                lstSubDepartment.AddItem rs!fldSubDepID & vbTab & rs!fldSubDepName
                rs2.MoveNext
            Wend
        Set rs2 = Nothing
        Set db2 = Nothing
        Set ws2 = Nothing
        lstSubDepartment.ListIndex = rs!fldSupDepID
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(DatabasePath & "ao.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM tblAuthFor WHERE fldKey = " & OfficerNum)
        If rs.RecordCount > 0 Then
            chkSecurityNet = rs!fldSecurityNet
            chkSecurityOracle = rs!fldSecurityOracle
            chkHrdSoftPurchases = rs!fldHrdSoftPurchases
            chkChangeRequests = rs!fldChangeRequests
            chkVoiceRequestsNew = rs!fldVoiceRequestsNew
            chkVoiceRequests = rs!fldVoiceRequests
            chkACTNetProg = rs!fldACTNetProg
            chkACTNetPurchase = rs!fldACTNetPurchase
            chkAcquisitionsHardware = rs!fldAcquisitionsHardware
            chkAcquisitionsOffice = rs!AcquisitionsOffice
        End If
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing
End Sub

[danasegarane]

Do you have the listbox added added with name "lstteam" with some values added to that?

[dogwomble]

lstTeam and lstDepartment are populated with values from the database on form load.

[dogwomble]

OK, I think I've found the source of the problem. But not necessarily the solution.

The cause is the values it's happening to pull in happen to be Null. However, even trying to do a comparison to see if they're Null seems to be giving me the same error message.

[danasegarane]

Something like this

lstTeam.ListIndex =IIf(IsNull(rs!fldTeamNum), -1, rs!fldTeamNum)

[dogwomble]

Something like this

lstTeam.ListIndex =IIf(IsNull(rs!fldTeamNum), -1, rs!fldTeamNum)

Looks like it hasn't helped, doing it a number of ways. And it looks like I was barking up the wrong tree.

Still got the above code now. It seems that anything I put below the following code gives me the error.

Private Sub cmdSearch_Click()
    If txtSurname.Text = "" Or txtFirstName.Text = "" Then
        MsgBox "Please enter first and last names to search for.", vbOKOnly
        Exit Sub
    End If
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(DatabasePath & "ao.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM tblAuthOfficers WHERE fldSurname = '" & txtSurname.Text & "' AND fldFirstName = '" & txtFirstName.Text & "'")
        If rs.RecordCount = 0 Then
            MsgBox "Not found."
            Set rs = Nothing
            Set db = Nothing
            Set ws = Nothing
            Exit Sub
        End If
        OfficerNum = rs!fldKey
        If rs!fldDepID <> -1 Then
            lstDepartment.ListIndex = rs!fldDepID
        End If

[bgmacaw]

My general suggestion is to not use the collection lookup operator, the exclamation point (!). Instead, declare exactly what you want returned, for example: rs.Fields("fldDepID").Value. This helps you avoid or better recognize weird errors like you're encountering and it's faster as well.

Another thing to look at is the results from your query when you run it in Access. Are the results what you want and are the column names correct? Often simple but not obvious typos can throw you off.

[dogwomble]

OK, this is now resolved. To be honest, I don't know why it originally occurred, what caused it, or why this fix worked, but changing the code to this helped:

Private Sub cmdSearch_Click()
    Dim ListTemp As Long
    If txtSurname.Text = "" Or txtFirstName.Text = "" Then
        MsgBox "Please enter first and last names to search for.", vbOKOnly
        Exit Sub
    End If
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(DatabasePath & "ao.mdb")
    Set rs = db.OpenRecordset("SELECT fldKey, fldDepID, fldSubDepID, fldPhone, fldTeamNum, fldPhone FROM tblAuthOfficers WHERE fldSurname = '" & txtSurname.Text & "' AND fldFirstName = '" & txtFirstName.Text & "'")
        If rs.RecordCount = 0 Then
            Set ws = Nothing
            Set db = Nothing
            Set rs = Nothing
            Exit Sub
        End If
        OfficerNum = rs!fldKey
        If rs.Fields("fldTeamNum").Value <> -1 Then
            lstTeam.ListIndex = rs.Fields("fldTeamNum").Value
        End If
        If rs.Fields("fldPhone").Value <> "" Then
            txtPhone.Text = rs.Fields("fldPhone").Value
        End If
        ListTemp = rs.Fields("fldSubDepID").Value
        If rs.Fields("fldDepID").Value <> -1 Then
            lstDepartment.ListIndex = rs.Fields("fldDepID").Value
        End If
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(DatabasePath & "ao.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM tblAuthFor WHERE fldKey = " & OfficerNum)
        If rs.RecordCount > 0 Then
            chkSecurityNet = Abs(Int(rs!fldSecurityNet))
            chkSecurityOracle = Abs(Int(rs!fldSecurityOracle))
            chkHrdSoftPurchases = (Abs(Int(rs!fldHrdSoftPurchases)))
            chkChangeRequests = Abs(Int(rs!fldChangeRequests))
            chkVoiceRequestsNew = Abs(Int(rs!fldVoiceRequestsnew))
            chkVoiceRequests = Abs(Int(rs!fldVoiceRequests))
            chkACTNetProg = Abs(Int(rs!fldACTNetProg))
            chkACTNetPurchase = Abs(Int(rs!fldACTNetPurchase))
            chkAcquisitionsHardware = Abs(Int(rs!fldAcquisitionsHardware))
            chkAcquisitionsOffice = Abs(Int(rs!fldAcquisitionsOffice))
        End If
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing
    lstSubDepartment.ListIndex = ListTemp
End Sub