Exit_Procedure advice

[PNGBill]

Hi Forum, accwss 2010 accdb

I am using

GoTo Exit_Procedure

and

Exit_Procedure:
    rst.Close
    dbs.Close
    Exit Sub

within my code.

The problem is if I include rst.Close and rst isn't open, an error results.

How do you include an Exit in your code that can work for all occaisions ??

 

[Galaxiom]

There is no way to test if it is open except to refer to it.
Change the error handling to On Error Resume Next before you Close.

ADO Recordsets and Connections have a State property. This shows the test for setting to Nothing too.

If Not rs Is Nothing Then
        If rs.State = adStateOpen Then: rs.Close
        Set rs = Nothing
 End If

 

[vbaInet]

Just out of interest PNGBill, can we see your entire function/sub?

I suspect the error handling might be wrong. When you GoTo a label, you would normally Resume to an Exit label.

 

[PNGBill]

Thanks vbaInet, Here is the full procedure.

Private Sub CmdEmailFundsTrsfAdvice_Click()
   On Error GoTo CmdEmailFundsTrsfAdvice_Click_Error
On Error GoTo Err_CmdEmailFundsTrsfAdvice_Click
    
    Dim FullName As String                          'Variable to hold Full Name
    Dim FirstName As String                         'Variable to hold first Name
    Dim LoanID As Integer                           'Variable to hold Loan ID
    Dim strSQL As String                            'Variable to hold SQL Statement
    Dim varTo As Variant                            'Variable to hold Email Address
    Dim stSubject As String                         'Variable to hold Enail Subject String
    Dim stText As String                            'Variable to hold Email Body String
    Dim TeamID As String                            'Variable to Hold Team Member ID
    Dim MembID As String                            'Variable to hold Club Member ID
    Dim MembIDFormat As String                      'Variable to hold Member ID formated as 182....
    Dim Response As String                          'Variable to hold response to Message Box Questions
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set dbs = CurrentDb()
    LoanID = Me.LoanID
    
            'check if completed loan documents have been checked as rec'd
    If Me![chkLoanAcceptRep] = 0 Or Me![chkBankXferDoc] = 0 Then
            'Loan Issue is not yet complete..
         MsgBox "Necessary Documents have not been sent out yet. Loan Issue is not yet completed.", vbInformation, "Incomplete Loan Issue"
         GoTo Exit_Procedure
    ElseIf Me![txtRepayMethod] = "Payroll" And Me![chkPayrollDednRep] = 0 Then
            'Payroll Deduction Form not ready yet..
        MsgBox "Payroll Deduction Letter not yet faxed out.", vbInformation, "Incomplete Loan Issue"
        GoTo Exit_Procedure
    ElseIf Me![txtRepayMethod] = "Transfer" Then
        If Me![chkSOMemberSign] = 0 Or Me![chkSOBankSubmit] = 0 Then
            'Standing Order documentation Not Ready..
            MsgBox "Standing Order Documentation Not In Order.", vbInformation, "Incomplete Loan Issue"
            GoTo Exit_Procedure
        End If
    End If
    
            'SQL to Collect Club Member Full Name
    Set rst = dbs.OpenRecordset("SELECT TBLLOAN.LDPK, TBLACCDET.ADPK AS MembID, TBLACCDET.ADFirstname AS FirstName, " & _
            "[ADFirstname] & "" "" & [ADSurname] AS FullName, TBLACCDET.ADEmail AS varTo " & _
        "FROM TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));")
        
    FullName = rst!FullName                         'Put Result of sql as Variable FullName
    FirstName = rst!FirstName                       'Put Result of sql as Variable FirstName
    varTo = rst!varTo                               'Put Result of SQL as Variable varTo
    MembID = rst!MembID                             'Put Result of SQL as Variable MemberID
    TeamID = UCase(TeamMemberLogin)                 'Put Result of SQL as Variable TeamID
   
    MembIDFormat = fncMemberIDFormat(MembID)        'Put Formated Member ID as value for Variable
   
    If VarType(varTo) = 1 Then              'Check if Null Value for Email Address and if so, Exit Sub
        MsgBox "No Email Address Evident. Check your Data and update Email Address"
            'Close database variables
        rst.Close
        dbs.Close
        Exit Sub
    End If
    
    stSubject = "Loan Funds Transfer Advice : " & FullName & " - Member Number " & MembIDFormat & ", Loan Number " & fncLoanNumberFormat(CStr(LoanID))
    stText = FirstName & "," & Chr(10) & Chr(10) & _
             "We advise that the process of Transferring the Loan Funds for your Loan Reference  " & fncLoanNumberFormat(CStr(LoanID)) & ", has commenced." & Chr(13) & Chr(13) & Chr(10) & Chr(10) & _
             "These funds will be sent from either Club Group's ANZ or BSP account into your nominated bank account." & Chr(13) & Chr(10) & Chr(10) & _
             "This process can take from one to two hours or may be an over night transfer." & Chr(13) & Chr(10) & Chr(10) & _
             "Please check your account and confirm to Club Group when the funds have been received." & Chr(13) & Chr(10) & Chr(10) & _
             "Should you have any questions and or require further information regarding this transfer," & Chr(13) & Chr(10) & _
             "please contact a Club Group Team Member. Contact details are:" & Chr(13) & Chr(10) & Chr(10) & _
             ContactDetailBasic & Chr(13) & Chr(10) & Chr(10) & _
             "Kind Regards," & Chr(10) & _
             fncTeamMemberName() & Chr(13) & Chr(10) & Chr(10) & _
             fncSeasonMessage
             
        'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, "Loans", , stSubject, stText, -1
     
        'Sql to add a Loan Communication record regarding Statement Just Emailed
    DoCmd.SetWarnings False         'Turn Warnings Off
    strSQL = "INSERT INTO tblCommunication ( RecordRef, OperatorID, RecordType, CommNotes ) " & _
        "SELECT " & LoanID & " AS RecordRef, " & Chr(34) & TeamID & Chr(34) & " AS OperatorID, ""Loan"" AS RecordType, ""Emailed Loan Funds Transfer Advice."" AS CommNotes " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
    DoCmd.RunSQL strSQL      'Run SQL
    DoCmd.SetWarnings True          'Turn Warnings On
        'Close database variables
    rst.Close
    dbs.Close
Exit_Procedure:
  '  rst.Close
  '  dbs.Close
    Exit Sub
Err_CmdEmailFundsTrsfAdvice_Click:
    MsgBox Err.Description
    Resume Exit_Procedure
   On Error GoTo 0
   Exit Sub
CmdEmailFundsTrsfAdvice_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CmdEmailFundsTrsfAdvice_Click of VBA Document Form_frmLoanIssueData"
    
End Sub

 

[spikepl]

Private Sub SomeProcedure()

    If gcfHandleErrors Then On Error GoTo SomeProcedure_Error

' a lot of code here , opening stuff etc.
    

SomeProcedure_Exit:
    On Error GoTo 0  'disable error handling from here onwards,  for closing stuff that  might not be open
    rst.close  'close  - whether it was open or not
    set rst=nothing

    Exit Sub

SomeProcedure_Error:

    Select Case Err.Number

        Case Else

            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SomeProcedure"
            Resume SomeProcedure_Exit

    End Select

End Sub

 

[gemma-the-husky]

what you can do is just this

Exit_Procedure:
on error resume next
rst.Close
on error resume next
dbs.Close
Exit Sub

EXCEPT, that these error handlers won't work within a error handler itself - which I am sure is why I except vbainet wondered about the rest of your procedure ...

 

[ChrisO]

No one can prove that you need to do it; so why do it?

Edit to Add:

It’s a fact, no one can prove it and no one will prove it in this thread.
Just watch the replies in this thread.
There will be words, words and more words but there will be no reproducible proof posted.

One might ask; why then do most people say we should close a database or recordset just before it goes out of scope?

If we ask them they will reply with one or more of the following:-
If I open it then I should close it.
If something has a left it must also have a right.
If something has a top it must also have a bottom.
If there is a yin there must also be a yang.

Oh! the satisfying feeling of symmetry…
Reminds me of a joke:-
I had a friend who was born by caesarean section; every time he left the house he used the window.

So why does this nonsense persist?

Well, it’s a lot like http://en.wikipedia.org/wiki/The_Emperor's_New_Clothes some people have a vested interest in themselves.
(‘vested interest’ is a funny pun don’t you think? )

The more they repeat the rubbish the more they believe it. They have a ‘vested interest’ and can not be seen to be swayed from it. The bigger their perceived reputation of themselves the bigger their ‘vested interest’. Heaven knows it they ever wrote a book and cast their opinion in print. “My God (they might think) I can’t relinquish my opinion on the subject, people will see I was wrong.”

But alas, the Emperor continues on his way, pretending his dignity has been maintained.

IT’S RUBBISH! The Emperor is naked and you will not get the reproducible proof of otherwise.

Chris.

 

[Galaxiom]

SomeProcedure_Exit:
    On Error GoTo 0  'disable error handling from here onwards,  for closing stuff that  might not be open
    rst.close  'close  - whether it was open or not
    set rst=nothing

On Error GoTo 0 does not disable error handling. That is done with On Error Resume Next.

On Error GoTo 0 passes the error up to the sub that called the one experiencing the error condition, continuing upwards until if finds an error handling procedure. The error will still be thrown.

 

[Galaxiom]

No one can prove that you need to do it; so why do it?

The practice most obviously comes from Microsoft's Help files. Right or wrongish a lot of people do it because the people who wrote the software say so. Either way, I couldn't get as passionate about it as Chris. One thing is for sure, setting the object to Nothing won't break anything.

Note that the developer should be careful when running a set of procedures that involve an object with scope beyond the procedure where it is created. As a module level object variable the closing could well be overlooked, particularly during error conditions where the procedure intended to close it is not reached. Probably still not the end of the world I guess unless the instance of the object is persisted in a collection and new instances repeatedly appended. But that is another story beyond what Chris is addressing.

However readers should be aware that the practice of not explicitly closing objects should not be extended to Connection objects. Walking away from a Connection leaves the other end hanging until it times out. In a multiuser application this may result in other users being unnecessarily denied a connection to the server.

 

[RainLover]

There’s an old joke, so old that I don’t even know for certain where it originated, that’s often used to explain why people do things the way they do. It involves some monkeys, a cage, a banana and a fire hose.
You build a nice big room-sized cage, and in one end of it you put five monkeys. In the other end you put the banana. Then you stand by with the fire hose. Sooner or later one of the monkeys is going to go after the banana, and when it does you turn on the fire hose and spray the other monkeys with it. Replace the banana if needed, then repeat the process. Monkeys are pretty smart, so they’ll figure this out pretty quickly: “If anybody goes for the banana, the rest of us get the hose.” Soon they’ll attack any member of their group who tries to go to the banana.
Once this happens, you take one monkey out of the cage and bring in a new one. The new monkey will come in, try to make friends, and then probably go for the banana. And the other monkeys, knowing what this means, will attack him to stop you from using the hose on them. Eventually the new monkey will get the message, and will even start joining in on the attack if somebody else goes for the banana. Once this happens, take another of the original monkeys out of the cage and bring in another new monkey.
After repeating this a few times, there will come a moment when none of the monkeys in the cage have ever been sprayed by the fire hose; in fact, they’ll never even have seen the hose. But they’ll attack any monkey who goes to get the banana. If the monkeys could speak English and if you could ask them why they attack anyone who goes for the banana, their answer would almost certainly be: “Well, I don’t really know, but that’s how we’ve always done things around here.”
This is a startlingly good analogy for the way lots of people do things: once a particular process is entrenched (and especially after a couple rounds of employee turnover), there’s nobody left who remembers why they do things this way. There’s nobody who stops to think about whether this is still a good way to do things, or whether it was even a good idea way back at the beginning. The process continues through nothing more than inertia, and anyone who suggests a change is likely to end up viciously attacked by monkeys.
But this is also a really good analogy for the way a lot of programmers think: a function or a class or a library was written, once upon a time, and maybe at the time it was a good idea. Maybe now it’s not such a good idea, and actually causes more problems than it solves, but hey, that’s the way we’ve always done things around here, and who am I to suggest a change?
Should I go get the fire hose?

 

[vbaInet]

I like the monkey joke. It made me laugh

@PNGBill: I've attempted to quickly tidy up your code:

Private Sub CmdEmailFundsTrsfAdvice_Click()
On Error GoTo Err_CmdEmailFundsTrsfAdvice_Click
    
    Dim FullName As String                          'Variable to hold Full Name
    Dim FirstName As String                         'Variable to hold first Name
    Dim LoanID As Integer                           'Variable to hold Loan ID
    Dim strSQL As String                            'Variable to hold SQL Statement
    Dim varTo As Variant                            'Variable to hold Email Address
    Dim stSubject As String                         'Variable to hold Enail Subject String
    Dim stText As String                            'Variable to hold Email Body String
    Dim TeamID As String                            'Variable to Hold Team Member ID
    Dim MembID As String                            'Variable to hold Club Member ID
    Dim MembIDFormat As String                      'Variable to hold Member ID formated as 182....
    Dim Response As String                          'Variable to hold response to Message Box Questions
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set dbs = CurrentDb()
    LoanID = Me.LoanID
    
            'check if completed loan documents have been checked as rec'd
    If Me![chkLoanAcceptRep] = 0 Or Me![chkBankXferDoc] = 0 Then
            'Loan Issue is not yet complete..
         MsgBox "Necessary Documents have not been sent out yet. Loan Issue is not yet completed.", vbInformation, "Incomplete Loan Issue"
         Exit Sub
    ElseIf Me![txtRepayMethod] = "Payroll" And Me![chkPayrollDednRep] = 0 Then
            'Payroll Deduction Form not ready yet..
        MsgBox "Payroll Deduction Letter not yet faxed out.", vbInformation, "Incomplete Loan Issue"
        Exit Sub
    ElseIf Me![txtRepayMethod] = "Transfer" Then
        If Me![chkSOMemberSign] = 0 Or Me![chkSOBankSubmit] = 0 Then
            'Standing Order documentation Not Ready..
            MsgBox "Standing Order Documentation Not In Order.", vbInformation, "Incomplete Loan Issue"
            Exit Sub
        End If
    End If
    
            'SQL to Collect Club Member Full Name
    Set rst = dbs.OpenRecordset("SELECT TBLLOAN.LDPK, TBLACCDET.ADPK AS MembID, TBLACCDET.ADFirstname AS FirstName, " & _
            "[ADFirstname] & "" "" & [ADSurname] AS FullName, TBLACCDET.ADEmail AS varTo " & _
        "FROM TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));")
        
    FullName = rst!FullName                         'Put Result of sql as Variable FullName
    FirstName = rst!FirstName                       'Put Result of sql as Variable FirstName
    varTo = rst!varTo                               'Put Result of SQL as Variable varTo
    MembID = rst!MembID                             'Put Result of SQL as Variable MemberID
    TeamID = UCase(TeamMemberLogin)                 'Put Result of SQL as Variable TeamID
   
    MembIDFormat = fncMemberIDFormat(MembID)        'Put Formated Member ID as value for Variable
   
    If IsNull(varTo) Then                           'Check if Null Value for Email Address and if so, Exit Sub
        MsgBox "No Email Address Evident. Check your Data and update Email Address"
        Exit Sub
    End If
    
    stSubject = "Loan Funds Transfer Advice : " & FullName & " - Member Number " & MembIDFormat & ", Loan Number " & fncLoanNumberFormat(CStr(LoanID))
    stText = FirstName & "," & Chr(10) & Chr(10) & _
             "We advise that the process of Transferring the Loan Funds for your Loan Reference  " & fncLoanNumberFormat(CStr(LoanID)) & ", has commenced." & Chr(13) & Chr(13) & Chr(10) & Chr(10) & _
             "These funds will be sent from either Club Group's ANZ or BSP account into your nominated bank account." & Chr(13) & Chr(10) & Chr(10) & _
             "This process can take from one to two hours or may be an over night transfer." & Chr(13) & Chr(10) & Chr(10) & _
             "Please check your account and confirm to Club Group when the funds have been received." & Chr(13) & Chr(10) & Chr(10) & _
             "Should you have any questions and or require further information regarding this transfer," & Chr(13) & Chr(10) & _
             "please contact a Club Group Team Member. Contact details are:" & Chr(13) & Chr(10) & Chr(10) & _
             ContactDetailBasic & Chr(13) & Chr(10) & Chr(10) & _
             "Kind Regards," & Chr(10) & _
             fncTeamMemberName() & Chr(13) & Chr(10) & Chr(10) & _
             fncSeasonMessage
             
        'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, "Loans", , stSubject, stText, -1
     
        'Sql to add a Loan Communication record regarding Statement Just Emailed
    strSQL = "INSERT INTO tblCommunication ( RecordRef, OperatorID, RecordType, CommNotes ) " & _
        "SELECT " & LoanID & " AS RecordRef, " & Chr(34) & TeamID & Chr(34) & " AS OperatorID, ""Loan"" AS RecordType, ""Emailed Loan Funds Transfer Advice."" AS CommNotes " & _
        "FROM TBLLOAN " & _
        "WHERE (((TBLLOAN.LDPK)=" & LoanID & "));"
    DoCmd.SetWarnings False         'Turn Warnings Off
    DoCmd.RunSQL strSQL             'Run SQL
    DoCmd.SetWarnings True          'Turn Warnings On
        
        'Close database variables
    rst.Close
    dbs.Close
    
Exit_Procedure:
    Exit Sub
    
Err_CmdEmailFundsTrsfAdvice_Click:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CmdEmailFundsTrsfAdvice_Click of VBA Document Form_frmLoanIssueData"
    Resume Exit_Procedure
    
End Sub

 

[RainLover]

Funny how most close the RS but only some set to nothing.

I think I might get one of those Bananas.

 

[vbaInet]

Funny how most close the RS but only some set to nothing.

I think I might get one of those Bananas.

I have the hose ready. Or maybe I will join the monkeys that have been cautioned.

I set to Nothing most times and sometimes I don't, but I never Close a recordset without setting to Nothing. It all depends on the situation. Just preference

 

[gemma-the-husky]

when you exit access, sometimes it won't shut down, and you have to use task manager to kill the process

in my experience, this behaviour seemed to be caused by a persistent recordset and carefully making sure they are all closed solved the problem. I try to remember to close them all. I am not so bothered about setting them to nothing, as I understood they get destroyed when the procedure closes, but anythnig that helps the "garbage collector" can't be a bad thing.

 

[Galaxiom]

when you exit access, sometimes it won't shut down, and you have to use task manager to kill the process

in my experience, this behaviour seemed to be caused by a persistent recordset

Allen Browne concures with this observation in Point 9 on this page.

It is poor programming to open anything without explicitly closing it. This problem is particularly acute in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.

As Dave has suggested this scenario is due to persistent recordsets since its scope is the Application and won't be descoped like a recordset opend in a procedure.

Something to remember if you are someone's monkey. When a problem is found with a project you are working on and they call in a consultant monkey who believes in closing recordsets, they are very likely to tell the head monkey that you are a bad monkey.

Having paid the consulting monkey a lot of bananas, the head monkey might prefer to accept their judgement over yours.

 

[ChrisO]

I didn’t want to get into the situation of 'my guru is better than your guru' but let’s have a look at Allen Browne’s solution:-

>>Always close recordsets and set objects to Nothing in the error recovery of your procedure. Use this construct:<<

Sub MyProc()
On Error GoTo Err_MyProc
    Dim db As Database
    Dim rst As Recordset
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("MyTable")
    [color=green]'Useful code here.[/color]
    rst.Close            [color=green]'Close what you opened.[/color]

Exit_MyProc:
    Set rst = Nothing    [color=green]'Deassign all objects.[/color]
    Set db = Nothing
    Exit Sub

Err_MyProc:
    [color=green]'Error handler here.[/color]
    Resume Exit_MyProc
End Sub

There is no proof in the need to Close in that solution.
In fact that solution does not Close under an error condition.

So if Allen Browne uses what he writes then he does not Close under an error condition.

Therefore we need to be careful when reading the writings of gurus who supply no proof.

Chris.

 

[Galaxiom]

That is a good point about the Close.
I have never been one for gurus either.

One other point I will make. We all seem to assume that the leftovers from failing to destroy an object are cleaned up by garbage collection some time after the procedure ends.

There are two things to consider.
1. Is garbage collection applied immediately after each procedure closes or is it something that just runs intermittently?

If the latter than it would mean that some objects could persist in memory for a siginificant time after the procedure closes. The loop test you have published previously at this site might not find a problem because the cleanup would be limiting the amount of debris and thus preventing memory from critical overload.

2. How efficient is the process of destroying the object during garbage collection compared to explicitly removing it from memory?

It is very likely that neither of these issues would noticably affect performance in a modern computer but that certainly doesn't mean failing to destroy objects is a good practice.

 

[ChrisO]

>> It is very likely that neither of these issues would noticably affect performance in a modern computer but that certainly doesn't mean failing to destroy objects is a good practice.<<
Point taken but is Closing better practice?

We have seen that Closing can raise an error and that we can prove.
But we have no proof that not Closing can cause a problem.

Given the balance of proof, not hearsay, the scales are tipped towards not Closing just before going out of scope.

Chris.