Form Validation (Sub Form inside Tab Control)

[Guest]

I am trying validate a form and I'm attempting to use a very nice 'code
snippet' provided by Dirk Goldar, MS Access MVP, 23/8/04 detailed below.
However my form is actually a sub form that is sitting inside a tab control
and it requires a Form_BeforeUpdate event as a trigger. No matter where I
place my trigger (i.e inside the main form or inside the sub form) my
application ignores it. I'm sure there is a simple explanation but I'm also
sure I will be old and grey before I figure it out. Can anyone assist?

Thanking you brilliant people out there in advance for any comments.

Best regards
David Heywood, Nottingham, UK.

***Code***

Public Function fnRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim boolNoValue As Boolean

lngErrCtlTabIndex = 99999999 ' more than max number of control.

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
boolNoValue = False
If IsNull(.Value) Then
boolNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
boolNoValue = True
End If
End If
End If
If boolNoValue Then
strErrorMessage = strErrorMessage & vbCr & " " &
..Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control.
End Select
End With
Next ctl
If Len(strErrorMessage) > 0 Then
MsgBox "The following fileds are required:" & vbCr & _
strErrorMessage, vbInformation, _
"PPC House Control" & " - Required fields are missing"
frm.Controls(strErrCtlName).SetFocus
fnRequiredFieldsMissing = True
Else
fnRequiredFieldsMissing = False
End If
End Function

*** BeforeUpdate Procedure***

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fnRequiredFieldsMissing(Me!subFrm_InputHouseDetails)

End Sub

 

[Arvin Meyer]

A subform is a control that is really a picture of the subform, until you
actually give it focus. For that reason, you can never refer to the
subform's before update event from the main form. It either hasn't happened
yet, or has already happened. Use the subform's before update event instead
of the form's, and use this line of code:

Cancel = fnRequiredFieldsMissing(Me)

The function: fnRequiredFieldsMissing() should be placed in a standard
module, so you can call it from anywhere.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access