DoCmd.Close command does not terminate form code execution

  • Thread starter Drahala via AccessMonster.com
  • Start date

D

Drahala via AccessMonster.com

I am experiencing a problem where a DoCmd.close command that has been coded
into a While Not loop does not terminate the execution of code where it
should.

Background:

I have created a custom progress bar on a form (Access 2003) that keeps track
of records that are being either archived or deleted, by way of a WHILE NOT
END OF FILE loop. The progress bar has a "Cancel" button on it which when
clicked, sets the value of a boolean variable named booCancel to "True". On
each iteration of the loop, the code checks the value of booCancel, and if
true records the cancel event to an audit log, closes and destroys the DAO
record set and closes the form.

Sample Code:

'Loop through records
While Not rs.EOF
'Check if process is canceled
If booCancel = True Then
Select Case intChoice
Case 1
Call AuditLog("Archive Cancel")
Case 2
Call AuditLog("Deletion Cancel")
End Select
rs.Close
Set rs = Nothing
DoCmd.Close
End If
rs.Edit

Problem:

The loop works as intended until the user clicks the Cancel button. When
this happens, the code executes all the code within the If statement
(including the DoCmd.close statement), and continues on to the rs.Edit
statement, which causes an "Error 91, Object Variable or With Block Variable
Not Set" error to occur as there is no record set to edit because the code
closed it.

An interesting point to note is that this error only occurs during run time.
When I step through the code in debug mode, the code executes perfectly; the
audit log is updated and the form closes without error.

I was under the impression that once DoCmd.close was called, all form level
code execution stopped, but this is apprently not the case here. Any insight
that can be offered here would be greatly appreciated.
 
Ad

Advertisements

A

Allen Browne

No. As you discovered, executing a Close does not immediately terminate the
procedure that is running. Access will attempt to complete the procedure
(although this is not reliable, as you also discovered in single-step mode.)

You need to code in such as way that the remainder of the code is explicitly
skipped, e.g.:
Dim bCancel As Boolean
Select Case intChoice
...
bCancel = True
End Select
If bCancel Then
DoCmd.Close acForm, Me.Name
Else
'do the other stuff you want to skip.
End If
 
Ad

Advertisements

D

Drahala via AccessMonster.com

Thanks Allen, that did the trick.

Allen said:
No. As you discovered, executing a Close does not immediately terminate the
procedure that is running. Access will attempt to complete the procedure
(although this is not reliable, as you also discovered in single-step mode.)

You need to code in such as way that the remainder of the code is explicitly
skipped, e.g.:
Dim bCancel As Boolean
Select Case intChoice
...
bCancel = True
End Select
If bCancel Then
DoCmd.Close acForm, Me.Name
Else
'do the other stuff you want to skip.
End If
I am experiencing a problem where a DoCmd.close command that has been coded
into a While Not loop does not terminate the execution of code where it
[quoted text clipped - 52 lines]
insight
that can be offered here would be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top