Results 1 to 4 of 4

Detecting a Before Update Cancel

  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    299

    Detecting a Before Update Cancel


    I have some edits in my Before Update event that, if they aren't met, put out a Msgbox and sets Cancel=True. This works fine if I do a straight update. However, in some cases I issue a Refresh in my code and then proceed to close the form, go to another form, etc. In these cases, the Msgbox appears and the update is cancelled, however, the next command, i.e., Close Form, executes anyway. Is there a way I can detect the Update Event Cancel so I can abort the Close Form and require the operator to fix the problem and update. Thanks, Eddie

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    Can you post your code snippet so we can see exactly what you are dealing with?

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    299
    This is the code when the operator hits the "Save and Close" command button:

    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err

    If Me.Dirty Then
    Me.Refresh
    End If

    DoCmd.Close , ""

    cmdClose_Click_Exit:
    Exit Sub

    cmdClose_Click_Err:
    MsgBox Error$
    Resume cmdClose_Click_Exit

    End Sub


    And this is the BeforeUpdate event code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.Description) Then
    MsgBox "Description is Required", vbOKOnly, "Description"
    Cancel = True
    Exit Sub
    End If

    If IsNull(Me.JobNbr) Then
    Me.JobNbr = NextJobNbr()
    End If

    End Sub

    If the operator leaves the Description blank and hits "Save and Close", they get an error message (Msgbox), but before they can correct it, the update is cancelled and the form closes.

    Thanks for your help, Eddie

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    This code makes no sense, just prior to closing the Form:
    Code:
    If Me.Dirty Then
     Me.Refresh
    End If
    It accomplishes nothing. You need to change it to

    If Me.Dirty Then Me.Dirty = False

    Then, in your Form_BeforeUpdate code, add this line

    Code:
    If IsNull(Me.Description) Then
     MsgBox "Description is Required", vbOKOnly, "Description"
     Cancel = True 
     Description.SetFocus
     Exit Sub
    End If
    and see what happens.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Y N cancel message box
    By cantord in forum Forms
    Replies: 8
    Last Post: 11-28-2012, 02:20 PM
  2. Cancel Error
    By Newbie11 in forum Reports
    Replies: 1
    Last Post: 02-13-2012, 09:13 AM
  3. Replies: 8
    Last Post: 04-07-2011, 05:52 AM
  4. Yes/No/Cancel Message Box
    By lynnmc26 in forum Access
    Replies: 0
    Last Post: 04-01-2011, 07:26 PM
  5. detecting a null date
    By tedpottel in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums