Solved

"Object variable or With block variable not set" error in VB

Posted on 2007-12-05
22
389 Views
Last Modified: 2013-12-20
I get the error below.  I have declared the variables.

"Run time error 91
Object variable or With block variable not set"

It points to the line:
ars3.Close
Set ars3 = Nothing
0
Comment
Question by:Tony789
  • 8
  • 6
  • 3
  • +4
22 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20412949
Without any further information, VBA does not know what ars3 is, which means it's either a typo, or you need to remove those two lines as they are not declared anywhere in your code.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20412997
... or they are not declared anywhere within the scope of the function/sub your are running, or the 'Set ars3 = Something' line is missing.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20413017
ars3 is already Nothing
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20413662
As you can no doubt tell by now, without more explanation and/or code all we can do is hazard a guess as to what is the real problem.
0
 

Author Comment

by:Tony789
ID: 20413884
I have the below code in the program:

Private Sub GetDataClosed()
 
MSHFlexGridC.Visible = True
Timer2.Enabled = True
 
Dim aconn As adodb.Connection 'connection
Dim ars As adodb.Recordset 'record set
Dim ars3 As adodb.Recordset 'record set
Dim ars4 As adodb.Recordset 'record set
Dim ars5 As adodb.Recordset 'record set
Dim ars6 As adodb.Recordset 'record set
Dim astrconn As String 'conn string
Dim astrsql As String
Dim ainti
Dim aintj
Dim ApplicationName As Variant
Dim ApplicationID As Variant
Dim AppIDTemp As Variant
 
 
'===================================================================
'Open connection to Database
'===================================================================
 
astrconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set aconn = New adodb.Connection
aconn.Open astrconn
 
NameID = Environ("UserName")
Set Name1 = New adodb.Recordset
QueueIDTemp = "select UserID from tbl_Users where NTUserID = '" & NameID & "'"
Name1.Open QueueIDTemp, aconn, adOpenDynamic, adLockOptimistic
UserIDTemp = Name1.Fields("UserID")
 
Diff = DateAdd("d", -1, Now())
 
'Open Rs
astrsql = "Select * from tbl_Closed Where Time_Close >= #" & Diff & "# and EnterID = " & UserIDTemp
Set ars = New adodb.Recordset
ars.Open astrsql, aconn, adOpenDynamic, adLockOptimistic
 
'===================================================================
'Grid setup
'===================================================================
 
MSHFlexGridC.Clear
MSHFlexGridC.Rows = 2
'MSHFlexGrid1.Cols = rs.Fields.Count - 4
 
With MSHFlexGridC
 
.TextMatrix(0, 1) = "Case"
.TextMatrix(0, 2) = "Firm Name"
.TextMatrix(0, 3) = "Name"
.TextMatrix(0, 4) = "User ID"
.TextMatrix(0, 5) = "Application"
.TextMatrix(0, 6) = "Problem"
.TextMatrix(0, 7) = "Working Time"
.TextMatrix(0, 8) = "Time Opened"
.TextMatrix(0, 9) = "Time Closed"
.TextMatrix(0, 10) = "Closed By"
 
.Row = 0
.Col = 0
.RowSel = 0
.ColSel = 10
.CellAlignment = flexAlignCenterCenter
.CellFontBold = True
 
.ColWidth(0) = 300
.ColWidth(1) = 2000
.ColWidth(2) = 2300
.ColWidth(3) = 1300
.ColWidth(4) = 2000
.ColWidth(5) = 2300
.ColWidth(6) = 2000
.ColWidth(7) = 2300
.ColWidth(8) = 2000
.ColWidth(9) = 2000
.ColWidth(10) = 2000
 
End With
 
'===================================================================
 
aintj = 1
 
Do Until ars.EOF = True 'do until all records in recordset
 
MSHFlexGridC.Rows = MSHFlexGridC.Rows + 1
 
MSHFlexGridC.TextMatrix(aintj, 1) = ars.Fields("CaseID").Value
MSHFlexGridC.TextMatrix(aintj, 4) = ars.Fields("UserID").Value
MSHFlexGridC.TextMatrix(aintj, 5) = ars.Fields("ApplicationID").Value
MSHFlexGridC.TextMatrix(aintj, 6) = ars.Fields("ProblemID").Value
MSHFlexGridC.TextMatrix(aintj, 7) = ars.Fields("Time_Working").Value
MSHFlexGridC.TextMatrix(aintj, 8) = ars.Fields("Time_Open").Value
MSHFlexGridC.TextMatrix(aintj, 9) = ars.Fields("Time_Close").Value
MSHFlexGridC.TextMatrix(aintj, 10) = ars.Fields("QueueID").Value
 
'================================================================================
'Application ID Selection
'================================================================================
 
ApplicationID = MSHFlexGridC.TextMatrix(aintj, 5)
Set ars3 = New adodb.Recordset
AppIDTemp = "select Application from tbl_Applications where ApplicationID = " & ApplicationID
ars3.Open AppIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars3.EOF = False And ars3.BOF = False Then
ApplicationName = ars3.Fields("Application")
End If
MSHFlexGridC.TextMatrix(aintj, 5) = ApplicationName

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20413913
>Set ars3 = New adodb.Recordset
Okay, so it's set.

>ars3.Open AppIDTemp, aconn, adOpenDynamic, adLockOptimistic
Are you certain this is actually opening a recordset?  Maybe your SQL bombed.  An error may be preventing it from opening.

Either way, give this a whirl...

If ars3.State <> adStateClosed then ars3.Close
Set ars3 = Nothing
0
 

Author Comment

by:Tony789
ID: 20414009
Where do you want me to put that statement?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20414025
>Where do you want me to put that statement?
Wherever you found this...

ars3.Close
Set ars3 = Nothing
0
 

Author Comment

by:Tony789
ID: 20414058
I tried but got the same error.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20414076
The big piece of code you posted does not contain the two lines that you posted above.

With that in mind, where in your code is...

ars3.Close
Set ars3 = Nothing
0
 

Author Comment

by:Tony789
ID: 20414085

ars.Close
Set ars = Nothing
 
'ars3.Close
'Set ars3 = Nothing
 
If ars3.State <> adStateClosed Then ars3.Close
Set ars3 = Nothing
 
ars4.Close
Set ars4 = Nothing
 
ars5.Close
Set ars5 = Nothing
 
ars6.Close
Set ars6 = Nothing
 
ars7.Close
Set ars7 = Nothing
 
ars8.Close
Set ars8 = Nothing
 
Name1.Close
Set Name1 = Nothing
 
aconn.Close
Set aconn = Nothing
 
 
End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20414095
Is that in the same function / sub?
0
 

Author Comment

by:Tony789
ID: 20414172
Yes
0
 
LVL 10

Expert Comment

by:3_S
ID: 20414490
If ars3.State = adStateOpen Then
        ars3.Close
End If
set ars3 = nothing

if you still get an error than this means that
Set ars3 = New ADODB.Recordset
has not been executed or that ars3 already has been set to nothing

If this still doesn't work please post the complete code
0
 

Author Comment

by:Tony789
ID: 20414572

Private Sub GetDataClosed()
 
MSHFlexGridC.Visible = True
Timer2.Enabled = True
 
Dim aconn As adodb.Connection 'connection
Dim ars As adodb.Recordset 'record set
Dim ars3 As adodb.Recordset 'record set
Dim ars4 As adodb.Recordset 'record set
Dim ars5 As adodb.Recordset 'record set
Dim ars6 As adodb.Recordset 'record set
Dim astrconn As String 'conn string
Dim astrsql As String
Dim ainti
Dim aintj
Dim ApplicationName As Variant
Dim ApplicationID As Variant
Dim AppIDTemp As Variant
 
 
'===================================================================
'Open connection to Database
'===================================================================
 
astrconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"
Set aconn = New adodb.Connection
aconn.Open astrconn
 
NameID = Environ("UserName")
Set Name1 = New adodb.Recordset
QueueIDTemp = "select UserID from tbl_Users where NTUserID = '" & NameID & "'"
Name1.Open QueueIDTemp, aconn, adOpenDynamic, adLockOptimistic
UserIDTemp = Name1.Fields("UserID")
 
Diff = DateAdd("d", -1, Now())
 
'Open Rs
astrsql = "Select * from tbl_Closed Where Time_Close >= #" & Diff & "# and EnterID = " & UserIDTemp
Set ars = New adodb.Recordset
ars.Open astrsql, aconn, adOpenDynamic, adLockOptimistic
 
'===================================================================
'Grid setup
'===================================================================
 
MSHFlexGridC.Clear
MSHFlexGridC.Rows = 2
'MSHFlexGrid1.Cols = rs.Fields.Count - 4
 
With MSHFlexGridC
 
.TextMatrix(0, 1) = "Case"
.TextMatrix(0, 2) = "Firm Name"
.TextMatrix(0, 3) = "Name"
.TextMatrix(0, 4) = "User ID"
.TextMatrix(0, 5) = "Application"
.TextMatrix(0, 6) = "Problem"
.TextMatrix(0, 7) = "Working Time"
.TextMatrix(0, 8) = "Time Opened"
.TextMatrix(0, 9) = "Time Closed"
.TextMatrix(0, 10) = "Closed By"
 
.Row = 0
.Col = 0
.RowSel = 0
.ColSel = 10
.CellAlignment = flexAlignCenterCenter
.CellFontBold = True
 
.ColWidth(0) = 300
.ColWidth(1) = 2000
.ColWidth(2) = 2300
.ColWidth(3) = 1300
.ColWidth(4) = 2000
.ColWidth(5) = 2300
.ColWidth(6) = 2000
.ColWidth(7) = 2300
.ColWidth(8) = 2000
.ColWidth(9) = 2000
.ColWidth(10) = 2000
 
End With
 
'===================================================================
 
aintj = 1
 
Do Until ars.EOF = True 'do until all records in recordset
 
MSHFlexGridC.Rows = MSHFlexGridC.Rows + 1
 
MSHFlexGridC.TextMatrix(aintj, 1) = ars.Fields("CaseID").Value
MSHFlexGridC.TextMatrix(aintj, 4) = ars.Fields("UserID").Value
MSHFlexGridC.TextMatrix(aintj, 5) = ars.Fields("ApplicationID").Value
MSHFlexGridC.TextMatrix(aintj, 6) = ars.Fields("ProblemID").Value
MSHFlexGridC.TextMatrix(aintj, 7) = ars.Fields("Time_Working").Value
MSHFlexGridC.TextMatrix(aintj, 8) = ars.Fields("Time_Open").Value
MSHFlexGridC.TextMatrix(aintj, 9) = ars.Fields("Time_Close").Value
MSHFlexGridC.TextMatrix(aintj, 10) = ars.Fields("QueueID").Value
 
'================================================================================
'Application ID Selection
'================================================================================
 
ApplicationID = MSHFlexGridC.TextMatrix(aintj, 5)
Set ars3 = New adodb.Recordset
AppIDTemp = "select Application from tbl_Applications where ApplicationID = " & ApplicationID
ars3.Open AppIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars3.EOF = False And ars3.BOF = False Then
ApplicationName = ars3.Fields("Application")
End If
MSHFlexGridC.TextMatrix(aintj, 5) = ApplicationName
 
'================================================================================
'FirstLast Name Selection
'================================================================================
 
UserID = MSHFlexGridC.TextMatrix(aintj, 4)
Set ars4 = New adodb.Recordset
UserIDTemp = "select [First Name], [Last Name] from [User ID] where UserID = '" & UserID & "'"
ars4.Open UserIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars4.EOF = False And ars4.BOF = False Then
FirstName = ars4.Fields("First Name")
LastName = ars4.Fields("Last Name")
End If
FirstLastName = FirstName & " " & LastName
MSHFlexGridC.TextMatrix(aintj, 3) = FirstLastName
 
'================================================================================
'Problem Name Selection
'================================================================================
 
ProblemID = MSHFlexGridC.TextMatrix(aintj, 6)
Set ars5 = New adodb.Recordset
ProblemIDTemp = "select Problem from tbl_Problems where ProblemID = " & ProblemID
ars5.Open ProblemIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars5.EOF = False And ars5.BOF = False Then
ProblemName = ars5.Fields("Problem")
End If
MSHFlexGridC.TextMatrix(aintj, 6) = ProblemName
 
'================================================================================
'Queue Name Selection
'================================================================================
 
QueueID = MSHFlexGridC.TextMatrix(aintj, 10)
Set ars6 = New adodb.Recordset
QueueIDTemp = "select UserName from tbl_Users where UserID = " & QueueID
ars6.Open QueueIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars6.EOF = False And ars6.BOF = False Then
UserName = ars6.Fields("UserName")
End If
MSHFlexGridC.TextMatrix(aintj, 10) = UserName
 
'================================================================================
'Firm Name Selection
'================================================================================
 
UserID = MSHFlexGridC.TextMatrix(aintj, 4)
Set ars7 = New adodb.Recordset
FirmIDTemp = "select FirmSub from [User ID] where UserID = '" & UserID & "'"
ars7.Open FirmIDTemp, aconn, adOpenDynamic, adLockOptimistic
If ars7.EOF = False And ars7.BOF = False Then
FirmSub = ars7.Fields("FirmSub")
End If
 
Set ars8 = New adodb.Recordset
FirmNameTemp = "select [Firm Name] from Correspondents where FirmSub = '" & FirmSub & "'"
ars8.Open FirmNameTemp, aconn, adOpenDynamic, adLockOptimistic
If ars8.EOF = False And ars8.BOF = False Then
FirmName = ars8.Fields("Firm Name")
End If
MSHFlexGridC.TextMatrix(aintj, 2) = FirmName
 
'================================================================================
 
aintj = aintj + 1
 
ars.MoveNext
 
Loop
 
'===================================================================
 
ars.Close
Set ars = Nothing
 
ars3.Close
Set ars3 = Nothing
 
ars4.Close
Set ars4 = Nothing
 
ars5.Close
Set ars5 = Nothing
 
ars6.Close
Set ars6 = Nothing
 
ars7.Close
Set ars7 = Nothing
 
ars8.Close
Set ars8 = Nothing
 
Name1.Close
Set Name1 = Nothing
 
aconn.Close
Set aconn = Nothing
 
 
End Sub

Open in new window

0
 

Author Comment

by:Tony789
ID: 20414573
It did not work.  I have attached the full code.
0
 
LVL 10

Accepted Solution

by:
3_S earned 250 total points
ID: 20415014
109: If ars3.EOF = False And ars3.BOF = False Then
110:      ApplicationName = ars3.Fields("Application")
111:End If
112:ars3.close
113:MSHFlexGridC.TextMatrix(aintj, 5) = ApplicationName

add the ars3.close immediately after you used the recordset.
at the end of your program you just put
set ars3 = nothing
Mind you use ars3 in a loop so Set ars3 = New adodb.Recordset could be place before the loop
open again.  Do the same for ars4,5,6,7,8.
0
 
LVL 10

Expert Comment

by:3_S
ID: 20415126
to clarify you question
if you never enter you loop then you haven't performed
Set ars3 = New adodb.Recordset

resulting in error Object variable or With block variable not set, since you object variable has not been set
So just placing Set ars3 = New adodb.Recordset before your loop would also do the tric
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 20417464
try using a hard coded query for as3 instead of using variables in your query.I think reInstalling MDAC is the solution if  problem persists.

Regards
Hitesh
0
 
LVL 2

Expert Comment

by:andrewneely
ID: 20419610
Basically, this error means that an object has not been instantinated.  There are two ways to instantinate an object in vb6.  First, you can declare the recordset as new (example "dim ars3 = New adodb.Recordset"), the second is to declare the object variable in one statement, and use the set command to instantiate it (example set ars3= new adodb.recordset").

If you reference the object before it is instantinated or you set it to nothing and then reference it, you'll get this error.


0
 

Author Comment

by:Tony789
ID: 20424418
3_S:

It works correctly now.  Thanks for your help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20424985
Great!  Now please close the question.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question