Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: BeforeUpdate_Cancel sub doesn't work

    Hi. I have the following sub in my VBA (A2K3):

    Code:
    Private Sub BOL_BeforeUpdate(Cancel As Integer)
    
        Dim RstCheck As DAO.Recordset
    
        Set RstCheck = CurrentDb.OpenRecordset("SELECT * FROM tblCurrentUnderTest6 WHERE " _
            & "BOL = '" & Me.BOL & "'", dbOpenDynaset)
        If RstCheck.RecordCount > 0 Then
            MsgBox "This Bill of Lading has already been entered.", vbCritical + vbOKOnly, _
                "Duplicate Entry"
            Cancel = True
            Me.Undo
            Me.CARRIER.SetFocus
        Else
            Cancel = False
        End If
        Set RstCheck = Nothing
        
    End Sub
    Not much is working. In testing, the MsgBox comes up and accepts the <OK> response. After that it's all downhill.

    Access doesn't allow me to SetFocus to CARRIER, with an error message that "You must save the field before you execute ... or the SetFocus method."

    Although Cancel is set to True, the entry is still there after the subroutine finishes (I commented out the SetFocus statement in order to allow the sub to finish).

    Undo didn't undo, although it should have removed all entries.

    What did I do wrong?

    Sam

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Access doesn't allow me to SetFocus to CARRIER, with an error message that "You must save the field before you execute ... or the SetFocus method."
    That's logical. You can't move the focus to another field in a Before Update event. To leave the field, the update would have to be resolved FIRST. To set the focus, do it in the After Update event.

    Although Cancel is set to True, the entry is still there after the subroutine finishes (I commented out the SetFocus statement in order to allow the sub to finish).
    From memory, Cancel=True only stops the entry from being updated to the text box and allowing the focus to move on. If you want to reverse the entry, I think you need to Cancel=True then Me.BOL.Undo.

    Undo didn't undo, although it should have removed all entries.
    If you want to remove all the changes to the form, Me.Undo is correct, but I'm not sure if you can do that in a Before Update event for a text box... seeing as you are saying it's not working, I can only assume not

    I would be doing what you are trying to do in the After Update event.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    OK, I trashed the SetFocus, and changed the method to Me.BOL.Undo instead. Now Access gives me my user-defined message, and selects the entire BOL entry. How do I get the text box to clear altogether? According to the Help, it can't be done within the BeforeUpdate event. Where then?

    Sam

Posting Permissions

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