ACCESS FEED: Re: [MS_AccessPros] run-time error '91' object variable or with block variable

Rabu, 06 Februari 2013

Re: [MS_AccessPros] run-time error '91' object variable or with block variable

Hi John,

>"Dim db As DAO.Database, rst As DAO.Recordset"

the value of public variables is lost on an unhandled error. This would cause an error when the object variable is not defined If you are going to use this method to persist a connection, make sure that all code has error handlers. If you don't feel like going through every procedure to add error handling, you can use something like Wayne Phillips vbWatchdog, which is a Global Error Handler and Tracing utility that you enable in code that runs when the database opens. www.everythingaccess.com/vbwatchdog.htm

this is my standard error handling code:

'~~~~~~~~~~~~~~~~~~~~~~~~~
Sub ProcedureName

   On Error GoTo Proc_Err
   '
   ' statements of the procedure
   '
Proc_Exit:
   On Error Resume Next
   'release object variables
   if Not rs is Nothing then
      rs.close
      set rs = Nothing
   end if
   set db = nothing
   Exit Sub ' or Function

Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ProcedureName (CHANGE THIS) "

   Resume Proc_Exit
   Resume
'~~~~~~~~~~~~~~~~~~~~~~~~~

http://www.AccessMVP.com/strive4peace/Code.htm

Warm Regards,
Crystal

*
   (: have an awesome day :)
*

________________________________
From: jfakes.rm jfakes@rocketmail.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, February 6, 2013 1:10 PM
Subject: [MS_AccessPros] run-time error '91' object variable or with block variable

When I open one of my databases normally (not shift enter), when I click on the exit access command on the menu, I get a "run-time error '91' object variable or with block variable."

I finally figure out that it has something to do with a form I use to maintain a permanent connection to the back end.

Following is the code for the form. Note: tblKeepOpen is a linked table in the backend used to maintain the connection.

Can someone please explain what I've done wrong here?

Option Compare Database
Option Explicit
Dim db As DAO.Database, rst As DAO.Recordset

Public rsAlwaysOpen As Recordset
Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing

End Sub
Private Sub Form_Open(Cancel As Integer)
'Set rs = db.OpenRecordset("tblKeepOpen", dbOpenDynaset)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("tblKeepOpen")
End Sub

John F.

[Non-text portions of this message have been removed]