• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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

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
Tony789
Asked:
Tony789
  • 8
  • 6
  • 3
  • +4
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
... 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
 
GrahamSkanRetiredCommented:
ars3 is already Nothing
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Anthony PerkinsCommented:
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
 
Tony789Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Tony789Author Commented:
Where do you want me to put that statement?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Where do you want me to put that statement?
Wherever you found this...

ars3.Close
Set ars3 = Nothing
0
 
Tony789Author Commented:
I tried but got the same error.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Tony789Author Commented:

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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Is that in the same function / sub?
0
 
Tony789Author Commented:
Yes
0
 
3_SCommented:
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
 
Tony789Author Commented:

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
 
Tony789Author Commented:
It did not work.  I have attached the full code.
0
 
3_SCommented:
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
 
3_SCommented:
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
 
hiteshgoldeneyeCommented:
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
 
andrewneelyCommented:
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
 
Tony789Author Commented:
3_S:

It works correctly now.  Thanks for your help.
0
 
Anthony PerkinsCommented:
Great!  Now please close the question.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 6
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now