Data Validation within Tab Control Change Event

[mcgwn]

I am putting together a form with a tab control for a client. There are text box/combo box controls on each tab page, plus a few sub forms for one to many relationships. There are a number of required fields for a valid entry, but the client wants the controls/subforms to appear in a specific order that makes it awkward to require fields within the table.

For example, there is a subform on tab page 1; if this is clicked before a text box control for a table-required field on tab page 3 is completed, you would be prompted that you must complete the field on page 3 even though you haven't even left page 1 yet.

I am trying to code around this using the tab control's On Change event to check for required entries before leaving each tab. As a simplified example, if the "recorder" field on Tab Page 0 is required:

Private Sub TabCtl49_Change()
        Select Case TabCtl49.Value
            Case Is <> 0
                If IsNull(Me.recorder) Then
                    Dim Msg, Style, Title, Response
                    Msg = "RECORDER is required."
                    Style = vbRetryCancel
                    Title = "Required Fields Blank"
                    Response = MsgBox(Msg, Style, Title)
                    If Response = vbRetry Then
                        TabCtl49.Pages(0).SetFocus
                    End If
                End If                    
        End Select
End Sub

This works fine, but the warning message prints twice. On the second time I click "retry", I am returned to Page 0 as desired. It seems this would be because TabCtl49.Pages(0).SetFocus triggers a second change event. However, if this were the case, wouldn't it happen infinitely?



To get around this, I tried initializing a public boolean (twoKiller) in the general/declarations section for the entire form, changing its value to "True" if validation fails, and then skipping validation if it is "True":

Public twoKiller As Boolean

Private Sub TabCtl49_Change()
     If twoKiller = False Then
        Select Case TabCtl49.Value
            Case Is <> 0
                If IsNull(Me.recorder) Then
                    Dim Msg, Style, Title, Response
                    Msg = "RECORDER is required."
                    Style = vbRetryCancel
                    Title = "Required Fields Blank"
                    Response = MsgBox(Msg, Style, Title)
                    If Response = vbRetry Then
                        twoKiller = True
                        TabCtl49.Pages(0).SetFocus
                    End If
                End If
        End Select
    Else
        twoKiller = False
    End If
End Sub

However, this shows the same behavior (message box appears twice). I have experimented a bit with the structure of this chunk, but results are the same.

Is there a way to avoid the second appearance of the message box and accomplish what I'm trying to do?

[Micron]

If you did not, try stepping through the code after placing a break point at the beginning and see how it flows. AFAIK, the tabcontrol change even only fires once during the transition from one page to another. Not sure if you're saying the exact message appears 2x, or if it's a similar one from another event that you've coded for but forgotten about. Stepping through should answer that as well.

Again, AFAIK, only the page or control double click event can be cancelled, so not sure if that's of any interest. What I don't understand about your posted code, is that it should run regardless of what page someone selects so it won't matter if user navigates from 3 to 1 (if that's an example of what you're describing) or from 2 to 3 - the page value won't be 0, so it will run anyway.