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

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
0
zimmer9
Asked:
zimmer9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you have to dim con

dim con as  adodb.connection

set con = new adodb.connection

see this link too
www.connectionstrings.com
0
 
harfangCommented:
After this line:

    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°)
0
 
harfangCommented:
Ah, sorry. You pasted the same code twice. As cap said, make sure that both rst and con have been initialized before.
(°v°)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tomedickCommented:
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
0
 
clarkscottCommented:
Don't close the CON or set it to nothing until the end of your routines.
Your second recordset needs it.
Scott C
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now