How would I avoid "Runtime error 91, Object variable or With Block Variable not set" ?
I am developing an Access application with an ADP file. I use Access as the front end and SQL Server as the Back End database.
How would I avoid "Runtime error 91, Object variable or With Block Variable not set" in the following
VBA code ?
sSQL = "select * from tblPSEmail"
rst.Open sSQL, con
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
sSQL1 = "select * from tblPSEmailAll"
rst.Open sSQL1, con <------------------- This line of code causes the runtime error
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
Set rst = Nothing
con.Close
Set con = Nothing
How would I avoid "Runtime error 91, Object variable or With Block Variable not set" in the following
VBA code ?
sSQL = "select * from tblPSEmail"
rst.Open sSQL, con
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
sSQL1 = "select * from tblPSEmailAll"
rst.Open sSQL1, con <------------------- This line of code causes the runtime error
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
Set rst = Nothing
con.Close
Set con = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ah, sorry. You pasted the same code twice. As cap said, make sure that both rst and con have been initialized before.
(°v°)
(°v°)
It's hard to tell from the code fragment you provided but it looks like your problem is that you are closing the connection between accessing your first and second table. After you are done parsing your first table leave the recordset and connection objects open so you can use them to parse your second table.
When I run the code as written here, I do not get an error:
Dim con As ADODB.Connection ' You probably already have these in your code or you would
Dim rst As New ADODB.Recordset ' have gotten an error much sooner in your code
Dim sSQL As String, sSQL1 as String ' but just to be sure I've included these here and to make sure
Set con = CurrentProject.Connection ' Also make sure you have Microsoft ActiveX Data Objects
' Library selected in References
sSQL = "select * from tblPSEmail"
rst.Open sSQL, con
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
' REMOVED your code that closed the connection and the recordset. This is undesirable as you
' reuse them in the next lines of code, this is probably where your problem lies
sSQL1 = "select * from tblPSEmailAll"
rst.Open sSQL1, con ' <------------------- This line of code causes the runtime error
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
Set rst = Nothing
Set con = Nothing
When I run the code as written here, I do not get an error:
Dim con As ADODB.Connection ' You probably already have these in your code or you would
Dim rst As New ADODB.Recordset ' have gotten an error much sooner in your code
Dim sSQL As String, sSQL1 as String ' but just to be sure I've included these here and to make sure
Set con = CurrentProject.Connection ' Also make sure you have Microsoft ActiveX Data Objects
' Library selected in References
sSQL = "select * from tblPSEmail"
rst.Open sSQL, con
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
' REMOVED your code that closed the connection and the recordset. This is undesirable as you
' reuse them in the next lines of code, this is probably where your problem lies
sSQL1 = "select * from tblPSEmailAll"
rst.Open sSQL1, con ' <------------------- This line of code causes the runtime error
rst.MoveFirst
Do Until rst.EOF
getEmail (rst("EMPNUM"))
rst.MoveNext
Loop
Set rst = Nothing
Set con = Nothing
Don't close the CON or set it to nothing until the end of your routines.
Your second recordset needs it.
Scott C
Your second recordset needs it.
Scott C
I would encourage you to NOT use this syntax:
Dim rst As New ADODB.Recordset
But instead use:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Using the first variety can reusult in non-trappable errors (i.e. the user would see the default, and often confusing, error message) if, for some reason, the ADO library doesn't exist, or there is a reference error.
Also, there is no need to issue a .MoveFirst in your code ... an ADO recordset isn't subject to the same issue as DAO recordset (i.e. were you must issue .MoveLast and .MoveFirst to fully populate the recordset) and will fully populate when built, and your pointer will be at the first record (if there is one). Issuing a .MoveFirst on an empty recordset (i.e. one where your SQL returns no records) can cause an error ... a minor issue, granted, but streamlining code is always a good thing.
Dim rst As New ADODB.Recordset
But instead use:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Using the first variety can reusult in non-trappable errors (i.e. the user would see the default, and often confusing, error message) if, for some reason, the ADO library doesn't exist, or there is a reference error.
Also, there is no need to issue a .MoveFirst in your code ... an ADO recordset isn't subject to the same issue as DAO recordset (i.e. were you must issue .MoveLast and .MoveFirst to fully populate the recordset) and will fully populate when built, and your pointer will be at the first record (if there is one). Issuing a .MoveFirst on an empty recordset (i.e. one where your SQL returns no records) can cause an error ... a minor issue, granted, but streamlining code is always a good thing.
Set rst = Nothing
The object rst doesn't hold anything anymore. It's a null pointer, hence: 'Object variable not set'. Remove the line, it serves no purpose anyway.
(°v°)