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:
Code:
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":
Code:
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?