-
Mar 17th, 2007, 03:39 AM
#1
Thread Starter
Lively Member
[RESOLVED] Error Code 91 - what's the deal?
Hey guys,
I'm having a problem with the code below. When it gets to the following line:
Code:
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...)
Code:
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
-
Mar 17th, 2007, 03:44 AM
#2
Re: Error Code 91 - what's the deal?
Do you have the listbox added added with name "lstteam" with some values added to that?
Please mark you thread resolved using the Thread Tools as shown
-
Mar 17th, 2007, 03:56 AM
#3
Thread Starter
Lively Member
Re: Error Code 91 - what's the deal?
lstTeam and lstDepartment are populated with values from the database on form load.
-
Mar 17th, 2007, 04:08 AM
#4
Thread Starter
Lively Member
Re: Error Code 91 - what's the deal?
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.
-
Mar 17th, 2007, 04:23 AM
#5
Re: Error Code 91 - what's the deal?
Something like this
vb Code:
lstTeam.ListIndex =IIf(IsNull(rs!fldTeamNum), -1, rs!fldTeamNum)
Please mark you thread resolved using the Thread Tools as shown
-
Mar 17th, 2007, 09:07 PM
#6
Thread Starter
Lively Member
Re: Error Code 91 - what's the deal?
Originally Posted by danasegarane
Something like this
vb Code:
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.
Code:
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
-
Mar 18th, 2007, 12:53 AM
#7
Re: Error Code 91 - what's the deal?
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.
-
Mar 18th, 2007, 07:08 PM
#8
Thread Starter
Lively Member
Re: Error Code 91 - what's the deal?
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:
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|