Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Cancel update if criteria not met

    Hi there,

    I have a form that I would like to prevent a user from UPDATING if certain criteria is not met, tho the user should be able to exit the form without updating the changes the record if desired.

    here is some sample code:
    ===================
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo err_form_beforeupdate
    If Me.Dirty Then

    If [approval status field] = "Approved" And IsNull([approval date]) Then
    MsgBox "APPROVED Status Set" & Chr(13) & "Please enter an approval date." & Chr(13) & "OR change Approved Status", vbInformation, "ENTER APPROVAL DATE"
    [approval date].SetFocus
    GoTo err_form_beforeupdate
    End If

    If IsNull([date contract received]) Then
    MsgBox "Date Received is required", vbInformation
    [date contract received].SetFocus
    GoTo err_form_beforeupdate
    End If

    err_form_beforeupdate:
    End Sub
    ==========================

    I read something about cancel =1 or something but couldn't nail it down...

    Any help would be mucho appreciato...

    cheers!

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    err_form_beforeupdate:
    Cancel = True
    End Sub



  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Thumbs up

    Perfect, that cancels the update. Now, how do I prevent the form from closing after the msgbox is acknowledged?

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Declare a variable in the declartions section of your form:

    ________________________________________________
    Option Compare Database
    Option Explicit

    Dim Flag1 As Boolean
    ________________________________________________


    Change the modified code you did in the Form'd BeforeUpdate event to:
    __________________________________________
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Flag1 = False
    ________________________________________________

    and then:
    ________________________________________________
    err_form_beforeupdate:
    Flag1 = True
    Cancel = True
    End Sub
    ________________________________________________


    Then if the Form's Unload event place this code:
    ________________________________________________
    Private Sub Form_Unload(Cancel As Integer)
    If Flag1 = True Then Cancel = True
    End Sub
    ________________________________________________

    Last edited by CyberLynx; 10-29-04 at 23:27.

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    That looks good... I think I have to play with it a bit, tho. It keeps locking up the database, probably due to some macros... Thank you for your responses, Cyberlynx!

Posting Permissions

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