call subform validation from main form

[kobiashi]

hi

i have a piece of code that carrys out some form validation, see below

'form Validation


Function CheckForEmpty() As Boolean


    CheckForEmpty = True
    ClearControlFormatting
   
    Dim ctrl As Control
   
    For Each ctrl In Me.Controls
        If ctrl.Tag = "FILL" Then
            If IsNull(ctrl) Or Len(ctrl) = 0 Then
                ctrl.BackColor = RGB(250, 240, 10)
                CheckForEmpty = False
            End If
        End If
    Next
           
End Function


Sub ClearControlFormatting()


    Dim ctrl As Control
   
    For Each ctrl In Me.Controls
        If ctrl.Tag = "FILL" Then
            ctrl.BackColor = vbWhite
        End If
    Next
End Sub




Private Sub form_Current()
    ClearControlFormatting
End Sub

but i dont seem to be able to get it to work on a sub form, i have a button on the main form when i click it, it runs the function CheckforEmpty, but id doesnt check the sub form, how can i get it to check the sub form aswell.

thanks in advance

 

[theDBguy]

I think you either have to modify your function to add a parameter to accept a form object or try setting the focus to the subform first before calling the function.

 

[moke123]

Your code only checks the controls on the main form. In order to get to the subform you need to test for the control on the main form to see if its is a subform control and if it is then cycle the controls on the subform using the proper path.

' ----------------------------------------------------------------
' Procedure Name: ValidateForm
' Purpose: Validate form controls are not null or missing
' Procedure Kind: Function
' Procedure Access: Public
' Parameter frm (Form): Form object ie. Me
' Parameter TagCharacter (String): Used in control tag property to designate required field
' Parameter IncludeSubform (Boolean): boolean to determine if subform included in validation
' Return Type: Boolean
' Author: Moke123
' Date: 2/25/2021
'
'validated controls must have a label for the msgbox . Use a hidden label if needed.
'

'Usage w/o subform:(cannot include subforms when using before update event.
'Private Sub Form_BeforeUpdate(Cancel As Integer)
'    Cancel = ValidateForm(Me, "VD8")
'End Sub
'

'Usage with subform:(use a command button such as close form button)
'Private Sub cmdCloseForm_Click()
'    If ValidateForm(Me, "VD8") = True Then
'        Exit Sub
'    End If
'
'   >>Your code to write data to tables
'
'    DoCmd.Close acForm, Me.Name
'End Sub
'
' ----------------------------------------------------------------
Public Function ValidateForm(frm As Form, TagCharacter As String, Optional IncludeSubform As Boolean = False) As Boolean
'
    Dim ctl As Control
    Dim ctl2 As Control
    Dim flg As Boolean
    Dim strOut As String

    For Each ctl In frm.Controls

        If InStr(1, ctl.Tag, TagCharacter) Then

            If Nz(ctl.Value, "") = "" Then
                flg = True
                ctl.BorderColor = vbRed
                strOut = strOut & Space(10) & "* " & Nz(ctl.Controls.Item(0).Caption, ctl.Name) & vbNewLine

            Else

                ctl.BorderColor = vbBlack

            End If

        End If

        If IncludeSubform Then

            If ctl.ControlType = acSubform Then

                For Each ctl2 In ctl.Form.Controls

                    If InStr(1, ctl2.Tag, TagCharacter) Then

                        If Nz(ctl2.Value, "") = "" Then

                            flg = True
                            ctl2.BorderColor = vbRed
                            strOut = strOut & Space(10) & "* " & ctl2.Controls.Item(0).Caption & vbNewLine

                        Else

                            ctl2.BorderColor = vbBlack

                        End If

                    End If
                Next
            End If
        End If

    Next

    If flg = True Then

        MsgBox "The following field(s) must be completed:" & vbNewLine & strOut

    End If

    ValidateForm = flg

End Function

That said, As Pat points out, you can't cancel the subform so I've only been able to use it on unbound subforms and use code to write subform data to tables.

example file attached

 

[arnelgp]

note:
the "subform" must not be Tagged with "FILL"
only the Controls inside the subform must be tagged as "FILL".
subform's First record (in case continuous/datasheet subform) will be tested.
you call it on BeforeUpdate event of your MainForm or Subform:

private sub form_beforeUpdate(cancel as integer)
cancel = not CheckForEmpty(Me)
end sub

'form Validation


Function CheckForEmpty(ByRef f As Access.Form) As Boolean


    CheckForEmpty = True
    Call ClearControlFormatting(f)

    Dim ctrl As Control
    Dim bolChecked As Boolean
    For Each ctrl In f.Controls
        'arnelgp
        If TypeName(ctrl) = "SUBFORM" Then
            Call CheckForEmpty(ctrl.Form)
        End If
        If ctrl.Tag = "FILL" Then
            If IsNull(ctrl) Or Len(ctrl) = 0 Then
                ctrl.BackColor = RGB(250, 240, 10)
                If CheckForEmpty Then
                    CheckForEmpty = False
                End If
            End If
        End If
    Next
        
End Function


Sub ClearControlFormatting(ByRef f As Access.Form)


    Dim ctrl As Control

    For Each ctrl In f.Controls
        If TypeName(ctrl) = "SUBFORM" Then
            Call ClearControlFormatting(ctrl.Form)
        End If
        If ctrl.Tag = "FILL" Then
            ctrl.BackColor = vbWhite
        End If
    Next
End Sub




Private Sub form_Current()
    ClearControlFormatting
End Sub

 

[arnelgp]

It is too late; the bad record has already been saved.

Never too late to edit it since both blank fields on main and sub is highlighted with yellow background.

 

[arnelgp]

There is no problem if it is saved, that was the intention when you are in New record. As long as there is visual clue as to whcih field need fixing.

call it on BeforeUpdate event of your MainForm or Subform:

Post #5.

 

[vhung]

Your code only checks the controls on the main form.
In order to get to the subform you need to test for the control on the main form
to see if its is a subform control and if it is then cycle the controls on the subform using the proper path.

' Purpose: Validate form controls are not null or missing
' Parameter TagCharacter (String): Used in control tag property to designate required field
' Parameter IncludeSubform (Boolean): boolean to determine if subform included in validation

Nice "Parameter TagCharacter (String):"
to "Validate Form controls are not null or missing"

 

[oxicottin]

Your code only checks the controls on the main form. In order to get to the subform you need to test for the control on the main form to see if its is a subform control and if it is then cycle the controls on the subform using the proper path.

' ----------------------------------------------------------------
' Procedure Name: ValidateForm
' Purpose: Validate form controls are not null or missing
' Procedure Kind: Function
' Procedure Access: Public
' Parameter frm (Form): Form object ie. Me
' Parameter TagCharacter (String): Used in control tag property to designate required field
' Parameter IncludeSubform (Boolean): boolean to determine if subform included in validation
' Return Type: Boolean
' Author: Moke123
' Date: 2/25/2021
'
'validated controls must have a label for the msgbox . Use a hidden label if needed.
'

'Usage w/o subform:(cannot include subforms when using before update event.
'Private Sub Form_BeforeUpdate(Cancel As Integer)
'    Cancel = ValidateForm(Me, "VD8")
'End Sub
'

'Usage with subform:(use a command button such as close form button)
'Private Sub cmdCloseForm_Click()
'    If ValidateForm(Me, "VD8") = True Then
'        Exit Sub
'    End If
'
'   >>Your code to write data to tables
'
'    DoCmd.Close acForm, Me.Name
'End Sub
'
' ----------------------------------------------------------------
Public Function ValidateForm(frm As Form, TagCharacter As String, Optional IncludeSubform As Boolean = False) As Boolean
'
    Dim ctl As Control
    Dim ctl2 As Control
    Dim flg As Boolean
    Dim strOut As String

    For Each ctl In frm.Controls

        If InStr(1, ctl.Tag, TagCharacter) Then

            If Nz(ctl.Value, "") = "" Then
                flg = True
                ctl.BorderColor = vbRed
                strOut = strOut & Space(10) & "* " & Nz(ctl.Controls.Item(0).Caption, ctl.Name) & vbNewLine

            Else

                ctl.BorderColor = vbBlack

            End If

        End If

        If IncludeSubform Then

            If ctl.ControlType = acSubform Then

                For Each ctl2 In ctl.Form.Controls

                    If InStr(1, ctl2.Tag, TagCharacter) Then

                        If Nz(ctl2.Value, "") = "" Then

                            flg = True
                            ctl2.BorderColor = vbRed
                            strOut = strOut & Space(10) & "* " & ctl2.Controls.Item(0).Caption & vbNewLine

                        Else

                            ctl2.BorderColor = vbBlack

                        End If

                    End If
                Next
            End If
        End If

    Next

    If flg = True Then

        MsgBox "The following field(s) must be completed:" & vbNewLine & strOut

    End If

    ValidateForm = flg

End Function

That said, As Pat points out, you can't cancel the subform so I've only been able to use it on unbound subforms and use code to write subform data to tables.

example file attached

@moke123 how can I add validation on a multilistbox? The code works except if I have a multi select listbox it highlights red even though I have items selected.

I tried adding below but I get error 2455 " You entered an expression that has an invalid reference to the property ItemSelected"

If Nz(ctl.Value, "") = "" Or ctl.ItemsSelected.Count = 0 Then