Solved

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

Posted on 2007-12-05
22
383 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now