Check Box Before Update Event Keeps Firing After Cancel = True

[DittoBird]

I hope the code below is clear - I trimmed out construction of comments (string strM).

This has a linked Jet/ACE back end accdb. The first part of the main If/End if works perfectly, not allowing the user to have three check boxes turned off.



The second part looks to see if there is an active filter, with filterOn off or on. It works if I click OK. However there is odd behaviour when I click vbCancel on the msgbox.

After clicking cancel on the msgbox, the check box remains checked or unchecked - whatever it was, before. It appears to have worked. However, when I go to click or right click anywhere else on my form or subform, the msgbox appears again and continues to do so if I click cancel.

When I OK on second or subsequent appearances of the msgbox stops the darn thing from reappearing. It also does not change the check box. Remember that the check box WILL change if I press OK on the first go.

Have I messed this up by having a if msgbox ("choices", vbokcancel) in the before_update event? Or have I done something even more obviously stupid?

Thanks in advance for any suggestions.

Private Sub chkShowBldg_BeforeUpdate(Cancel As Integer)

    On Error GoTo Err_Proc
    Dim strM As String

    If Me.chkShowBldg = 0 And Me.chkShowEqpt = 0 And Me.chkShowSpace = 0 Then
        MsgBox "At least one of Show Equipment, Show Building Components, or Show Space " & _
            "must be checked.", vbInformation, "All Three Check Boxes Cannot Be Turned Off"
        Cancel = True
    ElseIf Forms!frmMain.subData.Form.Filter <> "" Then
        If Forms!frmMain.subData.Form.FilterOn = True Then
            strM = "You have active filters that are turned on in the main screen (see the ""Filtered"" "
'More msg stuff snipped

        Else
            strM = "You have filters active on the main screen, but they are turned off (see the ""Unfiltered"" "
'More msg stuff snipped

        End If

'Other msg stuff snipped        
        If MsgBox(strM, vbInformation + vbOKCancel + vbDefaultButton2, _
            "Please Take Note! Click OK to Continue") = vbCancel Then
            
            Cancel = True
        End If
    End If
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, "Error frmMain chkShowBldg_BeforeUpdate" _
                , Err.HelpFile, Err.HelpContext
            Resume Exit_Proc
    End Select
End Sub

[Micron]

You seem to have several msgbox functions at play so it's not clear which one seems to cause repeat messages. I'd start by separating logic decisions. For example, it would seem that the second decision has nothing to do with the first, so why not have an IF block for the checkboxes and close that? Then one for the filter, and so on, separated blocks where they are not really related. The way you have it now, the filter decision will not process if one checkbox is checked. Also, the user choice of the first message has no bearing on anything since the msgbox is not returning a value. Not sure you need one - depends on what you want to do with the first decision. As it is, if I cancel, Cancel = True will run whether I Cancel or not on the msgbox. As for the last msgbox, if I cancel, what do you think you are cancelling? That would be the update of the checkbox for this code, yes? If you have 1 sub for 3 checkboxes, you only need one if you pass the name of the control (checkbox) to a procedure. Then when you must modify, you edit 1 procedure, not 3.

EDIT - OK I re-read your post and it seems more obvious to me now. Anyway, the reason for the behaviour is that cancelling the event doesn't take the record out of edit mode (assuming the control is bound). By clicking anywhere else you're making it fire again because a change has not been committed or removed (undone). If your form is showing the pencil icon for the record row, you'll see it's still there. You would have to Me.myControlName.Undo after the Cancel = True to get rid of the uncommitted edit/change for that control. That may leave the form in edit mode but note that if you Me.Undo you would cancel the updates for all controls.

[DittoBird]

Thank you for the reply, Micron, apologies for my late acknowledgement. I ended up getting frustrated and removing the filter any time a user chooses to limit the recordset by changing the value of building check mark (and/or the check marks for equipment and/or space).

All the best from the rock.
--
Tim

EDIT - OK I re-read your post and it seems more obvious to me now. Anyway, the reason for the behaviour is that cancelling the event doesn't take the record out of edit mode (assuming the control is bound). By clicking anywhere else you're making it fire again because a change has not been committed or removed (undone). If your form is showing the pencil icon for the record row, you'll see it's still there. You would have to Me.myControlName.Undo after the Cancel = True to get rid of the uncommitted edit/change for that control. That may leave the form in edit mode but note that if you Me.Undo you would cancel the updates for all controls.