Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Delete Event in VBA (Access 2003 SP1)

    I have the following event procedures set for the Delete, BeforeDelConfirm and AfterDelConfirm events in a form.

    Private Sub Form_AfterDelConfirm(Status As Integer)
    MsgBox "Deletion completed"
    End Sub

    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Response = acDataErrContinue
    End Sub

    Private Sub Form_Delete(Cancel As Integer)
    If MsgBox("Do you want to delete?", vbYesNo) = vbNo Then Cancel = True
    End Sub

    When I delete a record from the form, I encounter the following sequence of events (breakpoints are set on each of the three lines of actual code, to help track progress).

    - When Delete is clicked, the message box appears, and No is clicked.
    - After completing the Form_Delete procedure, there is no attempt to proceed to the Form_BeforeDelConfirm procedure (this is behaving as I would expect it to.)
    - If I again click Delete, the Form_BeforeDelConfirm procedure is immediately initiated, and proceeds to the Form_AfterDelConfirm procedure, before THEN running the new Form_Delete procedure.
    - No records are actually deleted during this process.
    - This occurs even if I have moved to a new record, but not if I have closed and re-opened the form.

    As I understand the Help file, if Cancel is set to True in the Delete procedure, the BeforeDelConfirm and AfterDelConfirm procedures will not run. That is what I want; however, the way it is working now will create a somewhat misleading sequence of messages.

    I encountered the problem in a live database, but the code given above is a simplified version set up in an otherwise empty database that has a single table with an autonumber field, a text field and an integer field, with an auto-generated form providing the interface. There are no other objects or code, but the problem remains the same. The same problem occurs if I remove the breakpoints and let it run freely.

    I know I can get around the problem by using a Private variable that flags whether to run the Before and After procedures, but would really like to know if I am doing something wrong, or if this is a bug in Access 2003 VBA.

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    Remove the code in the BeforeDelConfirm event. It have nothing to do there. You just need the code in the AfterDelConfirm event and in the Form_Delete event
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    Ummm - but isn't that the only way to suppressthe generic "You are about to delete 1 record..." prompt? The BeforeDelConfirm event is the only one of the three that has the Response argument that allows the use of the acDataErrContinue constant.

    (And I should perhaps have pointed out that the AfterDelConfirm event in the live database actually goes on to do more than issue a message box.)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    For that you use the DoCmd.SetWarnings statement :

    <pre>Private Sub Form_AfterDelConfirm(Status As Integer)
    DoCmd.SetWarnings True
    MsgBox "Deletion complete"
    End Sub

    Private Sub Form_Delete(Cancel As Integer)
    DoCmd.SetWarnings False
    If MsgBox("Do you want to delete?", vbYesNo) = vbNo Then Cancel = True
    End Sub</pre>

    Francois

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    Thanks, yes, that does seem to work without the problem that I originally encountered after cancelling.

    However, I still feel that my original code conformed with the instructions given in the Help file for suppressing the generic message box, so although I now have two workarounds (and yours is less clumsy than the one I had worked out), I still feel that there is a bit of a gap between Microsoft's intentions and reality!

    Thanks for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    I'd ask the question in the BeforeDelConfirm event:

    Private Sub Form_AfterDelConfirm(Status As Integer)
    MsgBox "Deletion completed"
    End Sub

    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    If MsgBox("Do you want to delete?", vbYesNo) = vbNo Then Cancel = True
    Response = acDataErrContinue
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Delete Event in VBA (Access 2003 SP1)

    Thanks, Hans.

    The reason I'm asking in the Delete event is because I need to get some information about the record being deleted, which is lost by the time I get to the BeforeDelConfirm event. I guess I could gather the data regardless, and then ask the question in the BeforeDelConfirm event, but it just seemed more efficient not to bother doing so if the delete wasn't going ahead.

    Anyway, I now have plenty of workarounds to choose from!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •