BeforeUpdate and Cancel keep getting Messagebox though

[KyleB]

I have a series of checkboxes, used to classify query criteria, such that you can run a report for 1st Quarter, 2nd Quarter etc, or by year. These are located on a form; however, when choosing a start and end date, I'm wanting to restrict the check box selection such that if your start period is 3rd Quarter of 2007, then your end period can't be 1st Quarter of 2007. Within my code I attempt to put up a messagebox, and cancel the event, which seems to work, unfortunately, though, I cannot click on anything else until I hit the escape key, otherwise it repeats the messagebox every time I click.

Attached image is the form as it looks at the moment, where you have a start and end time period, and The code below is comparing the existing checks and time periods to determine validity.

Private Sub chkE1Qtr_BeforeUpdate(Cancel As Integer)
    If (Me.EndYear = Me.YearofForecast And (Me.chk2Qtr = True Or Me.chk3Qtr = True Or Me.chk4Qtr = True)) Then
        msgbox ("You may not choose a quarter that precedes your starting quarter. (Ie: End Date must be after Start Date))")
        Cancel = True
        Else
            Me.chkE2Qtr = False
            Me.chkE3Qtr = False
            Me.chkE4Qtr = False
            Me.chkEYr = False
    End If
End Sub

 

[Karma]

I have a series of checkboxes, used to classify query criteria, such that you can run a report for 1st Quarter, 2nd Quarter etc, or by year. These are located on a form; however, when choosing a start and end date, I'm wanting to restrict the check box selection such that if your start period is 3rd Quarter of 2007, then your end period can't be 1st Quarter of 2007. Within my code I attempt to put up a messagebox, and cancel the event, which seems to work, unfortunately, though, I cannot click on anything else until I hit the escape key, otherwise it repeats the messagebox every time I click.

Attached image is the form as it looks at the moment, where you have a start and end time period, and The code below is comparing the existing checks and time periods to determine validity.

Private Sub chkE1Qtr_BeforeUpdate(Cancel As Integer)
    If (Me.EndYear = Me.YearofForecast And (Me.chk2Qtr = True Or Me.chk3Qtr = True Or Me.chk4Qtr = True)) Then
        msgbox ("You may not choose a quarter that precedes your starting quarter. (Ie: End Date must be after Start Date))")
        Cancel = True
        Else
            Me.chkE2Qtr = False
            Me.chkE3Qtr = False
            Me.chkE4Qtr = False
            Me.chkEYr = False
    End If
End Sub

You need to clear the contents of the field to continue and that is what esc does otherwise every time the control loses the focus it will run the before update event and find the same problem. You can do this automatically by adding

SendKeys "{ESC}"

or

Me.YearofForecast = null

HTH

K

 

[Rich]

Me. Undo should work instead of SendKeys

 

[KyleB]

The sendkeys option works, and allows the function to operate as desired.

As a matter of fact, me.undo did not work. I get the same problem, as if the command wasn't there at all. Setting the text box to null, while it does allow me to pick a different box without the error message showing again, would also cause queries to fail to function, as they reference that text box for query controls in sorting, and filtering operations.

I am curious though as to why the Cancel=True doesn't automatically remove focus from the control though. I would think that would be part of the default function for that command. Just the way it was programmed I suppose.

Thank you for the help though, I was about to go about it a different way, by setting case statements in each check box to enable/disable various checkboxes based on the criteria present.