call subform validation from main form (1 Viewer)

kobiashi

Registered User.
Local time
Today, 11:41
Joined
May 11, 2018
Messages
258
hi

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

Code:
'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’m here to help
Staff member
Local time
Today, 04:41
Joined
Oct 29, 2018
Messages
21,358
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,981
You need to run the code in the subform's BeforeUpdate event and cancel the update to prevent the record from being saved if any errors are found. You CANNOT run this code from the main form. When you switch focus from the subform back to the main form, Access AUTOMATICALLY saves the subform record so it is like closing the barn door after the horses have escaped. The bad data has already been saved and you can't get it back.

As it is, the code is not protecting the main form record from being saved saved either unless you are calling it from the main form's BeforeUpdate event and cancelling the save there.

BTW - "Me" ALWAYS refers to the form/report in which the code is running so that is another reason you can't run this code from the main form and have it apply to the subform.

If you want to have a generic code module, move the code to a standard module and pass in the form reference. Then change the code to replace "me." with "frm."

Code:
Function CheckForEmpty(frm AS Form) As Boolean
Code:
If CheckForEmpty(Me) = False Then
    Cancel = True
    Exit Sub
End If
 

moke123

AWF VIP
Local time
Today, 07:41
Joined
Jan 11, 2013
Messages
3,852
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.

Code:
' ----------------------------------------------------------------
' 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
 

Attachments

  • ValidationDB.accdb
    528 KB · Views: 465

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:41
Joined
May 7, 2009
Messages
19,175
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
Code:
'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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,981
Moke and arnel,

It was nice of you to retype the procedure rather than just indicate what needed to be changed as I did. That is pretty much the suggestion I made to make the code generic EXCEPT that you need to run the code from the subform.

Once the code is running in the main form, the main form has the focus and that means that if the subform had been dirty, the record HAS ALREADY BEEN SAVED so validate until you're blue in the face. It is too late; the bad record has already been saved.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:41
Joined
May 7, 2009
Messages
19,175
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,981
Sure, you can edit it but the record has already been saved. If you don't believe me, check it out yourself. Moving focus from the subform to the main form so you can push the validation button forces the subform record to be saved (if it is dirty) before the validation code runs which makes the validation useless. Yes, you can use the same code procedure to validate ALL forms as long as you set the tag properties BUT, the code has to run from the form you want to check and preferably from the Form's BeforeUpdate event.

Just to disagree with me, I'm sure you will be able to find a way to make code in formA validate a record in formB (as long as formB is NOT a subform on formA). Why you would ever want to do that is beyond me but do it if you want to.

The situation at hand is a button on a main form that the user wants to use to validate a subform. That will NOT work unless you are using unbound forms (why again) because Access is very helpful and will save the subform record before the button runs the validation code.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:41
Joined
May 7, 2009
Messages
19,175
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

Member
Local time
Today, 04:41
Joined
Jul 8, 2020
Messages
235
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"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,981
There is no problem if it is saved,
We're going to have to agree to disagree on the wisdom of allowing bad data to be saved. Hope your employer/client never makes a poor business decision based on the bad data you allowed to be saved.
 

oxicottin

Learning by pecking away....
Local time
Today, 07:41
Joined
Jun 26, 2007
Messages
851
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.

Code:
' ----------------------------------------------------------------
' 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
 

Users who are viewing this thread

Top Bottom