Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cancel a Close event (2003)

    At the close of a property review form I display a message advising the User if they click OK the data will no longer be available for editing. They can choose cancel and return to the form. That part is good. However, at the close event I also check certain fields to validate data. If a correction has to be made I want to cancel the closing and return to the field in question. Problem is, as you can see in the code, at the end of the IF statements validating data is another IF statement with the warning message mentioned above. If the validation finds an error it displays instructions, but when you click OK my warning message displays. If Cancel is chosen at that point it returns to the form and field as I want, but I would like to do that without displaying the warning message first. Worse, if they choose OK the data is moved and can't be corrected.

    The Users do not follow my tab order or I could do the validating after the update of the last field. Any suggestions?

    Private Sub CmdCloseForm_Click()
    On Error GoTo CmdCloseForm_Click_Error
    Refresh
    Dim bytChoice As Byte, db As DAO.Database, Cancel As Integer
    Dim strSQL3 As String, strSQL4 As String, strWarning As String, parAsset As String, parAO As String
    Set db = DBEngine(0)(0)
    parAsset = "'" & Forms!frm_ANNUAL_REVIEW.ASSET_NUMBER & "'"
    parAO = "'" & Forms!frm_ANNUAL_REVIEW.AO & "'"
    strSQL3 =
    strSQL4 =
    If Me.NEW_INSPECTION_DATE = #1/1/2007# And _
    IsNull(Me.cbo_I_EXCEPTION) Then
    MsgBox strName & ", " & "when using 1/1/2007 for the Inspection Date " _
    & "an Exception value must also be entered." & vbCrLf & _
    vbCrLf & "Please select an Exception value from the dropdown list."
    Cancel = True
    Me.cbo_I_EXCEPTION.BorderColor = 255
    Me.lblAttentionI.Visible = True
    Me.cbo_I_EXCEPTION.SetFocus
    ElseIf Me.NEW_APPRAISAL_DATE = #1/1/2007# And _
    IsNull(Me.cbo_A_EXCEPTION) Then
    MsgBox strName & ", " & "when using 1/1/2007 for the Appraisal Date " _
    & "an Exception value must also be entered." & vbCrLf & _
    vbCrLf & "Please select an Exception value from the dropdown list."
    Cancel = True
    Me.cbo_A_EXCEPTION.BorderColor = 255
    Me.lblAttentionA.Visible = True
    Me.cbo_A_EXCEPTION.SetFocus
    ElseIf IsNull(Me.NEW_COMMENTS) Then
    MsgBox strName & ", " & "you have not entered New Comments. "
    Cancel = True
    Me.NEW_COMMENTS.SetFocus
    End If
    strWarning = "Is this step completed?" & vbCrLf & "If you choose OK " _
    & "this record will no longer be accessible in step one." & vbCrLf & _
    vbCrLf & "If you Cancel your work will be saved."
    bytChoice = MsgBox(strWarning, vbQuestion + vbOKCancel, conAppName)
    If bytChoice = vbOK Then
    [MARK] = 1
    Refresh
    db.Execute strSQL3, dbFailOnError
    db.Execute strSQL4, dbFailOnError
    DoCmd.Close acForm, "frm_ANNUAL_REVIEW", acSaveYes
    MsgBox db.RecordsAffected & " record moved to table 2."
    Else
    Cancel = True
    End If
    Set db = Nothing
    On Error GoTo 0
    Exit Sub
    CmdCloseForm_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CmdCloseForm_Click of VBA Document Form_frm_ANNUAL_REVIEW"
    End Sub
    Thanks
    chuck

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cancel a Close event (2003)

    Hi Chuck,

    I would take a bit different approach to this, and force the user to save the changes and the like using a button. Doing it on the form close creates several issues that are difficult to deal with. Using the button approach, you can control whether or not the form gets closed, and you can do the validity checking without jumping through hoops. The code you have should work on the button click event and be pretty much OK. The other approach would be to use an unbound form and control the user completely, but that's a good deal more work.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cancel a Close event (2003)

    Of Course!! Why didn't I think of that? (Don't answer that). Great idea Wendall!
    Thanks
    chuck

Posting Permissions

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