Looping through form and subform controls

[zeroaccess]

Hey all,

I am doing a validation check based on null fields and setting the border color if true. The following works well:

Function ValidateInspection()

    Dim ctrl As Control

        For Each ctrl In Forms.frmInspection.Controls
            Select Case ctrl.Tag
                Case "ValidateID", "ValidateSD"
                    If IsNull(ctrl) Then
                        ctrl.BorderColor = vbRed
                    Else
                        Exit Function
                    End If
            End Select
        Next
        MsgBox "Please enter missing data.", vbCritical
End Function

But I needed to also loop through subform controls, so after much wrangling, I came up with this:

Function ValidateInspection(frm As Form)

    Dim ctrl As Control

        For Each ctrl In frm
            Select Case ctrl.ControlType
                Case acComboBox, acTextBox
                    Select Case ctrl.Tag
                        Case "ValidateID", "ValidateSD"
                            If IsNull(ctrl) Then
                                ctrl.BorderColor = vbRed
                            Else
                                Exit Function
                            End If
                    End Select
                Case acSubform
                    ValidateInspection frm(ctrl.Name).Form
            End Select
        Next
        MsgBox "Please enter missing data.", vbCritical
End Function

I'm happy to say this also works, however observing the following quirks:

When there is only 1 subform record, the message box comes up twice - first after setting the subform fields red, then again after it acts on the main form (why in that order, I don't know).
When there are multiple subform records, it will only turn them red if I first click down into one of the subform fields, and then it works, but the message box does not appear.

Strange. I'm sure I have something wonky in my code.

The subform is a continuous form so unfortunately all of the records for a particular field or column will be outlined in red, but it will at least draw the user's attention to the right area.

Anyway, I need to figure out how to get the message to pop only once and I'm going to also try to set focus. It would be nice if I could act on the whole form and not in separate parts - I think that would solve these issues.

 

[Dreamweaver]

Are you running your code in both the main form and subform.
The main form code should not see the controls in the subform as the subform itself is a contol and has it's own set of contols which will only be checked when you click in it.

mick

 

[zeroaccess]

Since it seems to like checking the subform first, I added the following at the beginning of the procedure which helps it run more reliably:

    Forms.frmInspection.subform.SetFocus
    DoCmd.GoToRecord , , acLast

This is only needed when there are multiple subform records, but it solved some issues.

That leaves me with figuring out why the message box sometimes appears, sometimes doesn't, and sometimes appears twice.

 

[zeroaccess]

Are you running your code in both the main form and subform.
The main form code should not see the controls in the subform as the subform itself is a contol and has it's own set of contols which will only be checked when you click in it.

Exactly - but check the code where:

Case acSubform
ValidateInspection frm(ctrl.Name).Form

It is essentially saying when you get to the subform, run the procedure on the controls in the subform. I got that trick from Fionnuala here: https://stackoverflow.com/questions...-in-a-form-including-controls-in-a-subform-ac

 

[Dreamweaver]

just noticed the case But your still running it twice in that case

ValidateInspection frm(ctrl.Name).Form
It's a call back function.

 

[zeroaccess]

just noticed the case But your still running it twice in that case

ValidateInspection frm(ctrl.Name).Form
It's a call back function.

Alright, got a pointer for me on how to sort this?

I've blended this with my View Report - so it's validating, then either marking fields red, ELSE opening the report and exiting the function. But like you said, I'm getting double on the report opening and the message boxes.

 

[Dreamweaver]

Cool glade to have been able to help mick

 

[ebs17]

I think you would have to use conditional formatting for a continuous form, as this is the only way to react in records.

In a bound form, the check used makes more sense when creating or editing a single data record - there is always only one current data record.

I would then specifically filter a data source where content is missing in several existing data records. That gives enough attention.

 

[zeroaccess]

Conditional formatting could be handy. Any way to turn that on/off in VBA, though?

I also need to tie this in with a report. The report should only run if there are no null fields to flag. I know I'm doing this wrong because the report runs no matter what (and pops up twice) but it's close...

Function ValidateInspection(frm As Form)

    Dim ctrl As Control
    
    Forms.frmInspection.sbfSampleDetails.SetFocus
    DoCmd.GoToRecord , , acLast

        For Each ctrl In frm
            Select Case ctrl.ControlType
                Case acComboBox, acTextBox
                    Select Case ctrl.Tag
                        Case "ValidateID", "ValidateSD"
                            If IsNull(ctrl) Then
                                ctrl.BorderColor = vbRed
                            Else
                                ViewSummary
                                Exit Function
                            End If
                    End Select
                Case acSubform
                    ValidateInspection frm(ctrl.Name).Form
            End Select
        Next
        MsgBox "Please enter missing data.", vbCritical
End Function

Sub ViewSummary()
    If Not IsNull(Forms!frmInspection.[Inspection ID]) Then
        TempVars.Add "CurrentRecordID", Forms!frmInspection.[Inspection ID].Value
            If Screen.ActiveForm.Dirty Then
                DoCmd.RunCommand (acCmdSaveRecord)
            End If
        DoCmd.OpenReport "rptInspectionSummary", acViewReport, , , acDialog
    End If
End Sub

 

[ebs17]

Conditional Formatting using vba

 

[Cronk]

All of the designated controls need to be null for the message to be displayed. If any control has a non null value, the Exit function line is executed.

Remove the exit function line and use a flag variable which is set to false before the loop, and change it to true if any designated control is null. Display the message if the flag is true.

I would also set the border back to black (?) if something was entered since a previous validation operation.

 

[zeroaccess]

All of the designated controls need to be null for the message to be displayed. If any control has a non null value, the Exit function line is executed.

Remove the exit function line and use a flag variable which is set to false before the loop, and change it to true if any designated control is null. Display the message if the flag is true.

I would also set the border back to black (?) if something was entered since a previous validation operation.

This sounds really promising, but I'm still getting both the message box and the report opening, or the report opening twice. Removed the report for now...

Function ValidateInspection(frm As Form)

    Dim ctrl As Control
    Dim VFlag As Boolean
  
    VFlag = False
  
    Forms.frmInspection.sbfSampleDetails.SetFocus
    DoCmd.GoToRecord , , acLast

        For Each ctrl In frm
            Select Case ctrl.ControlType
                Case acComboBox, acTextBox
                    Select Case ctrl.Tag
                        Case "ValidateID", "ValidateSD"
                            If IsNull(ctrl) Then
                                ctrl.BorderColor = vbRed
                                VFlag = True
                            End If
                    End Select
                Case acSubform
                    ValidateInspection frm(ctrl.Name).Form
            End Select
        Next
        If VFlag = True Then MsgBox "Please enter missing data.", vbCritical
End Function

If I can't get this to work I might have to play with conditional formatting instead.

 

[Pat Hartman]

Normally records are validated in the form's BeforeUpdate event so that the save can be prevented if there is missing data. Your code does not prevent bad records from being saved. That may be your intention. Sometimes the data comes in in dribs and drabs and so you can't use RI to set fields to required and you have to allow incomplete records to be saved. In that situation, I add a flag to each record to indicate "completeness". The code sets the completeflg after checking all the controls for data and correctness. Queries (except for the one that populates your edit form) only select complete records. Therefore, the report should return no data if the record is incomplete. While the flag technically violates third normal form, without it, the validation would have to run as part of every query and that is too cumbersome.

 

[zeroaccess]

Normally records are validated in the form's BeforeUpdate event so that the save can be prevented if there is missing data. Your code does not prevent bad records from being saved. That may be your intention. Sometimes the data comes in in dribs and drabs and so you can't use RI to set fields to required and you have to allow incomplete records to be saved. In that situation, I add a flag to each record to indicate "completeness". The code sets the completeflg after checking all the controls for data and correctness. Queries (except for the one that populates your edit form) only select complete records. Therefore, the report should return no data if the record is incomplete. While the flag technically violates third normal form, without it, the validation would have to run as part of every query and that is too cumbersome.

You are right - I could simply use brute force to prevent the user from having empty fields. I won't prevent a save as that's needed, but I do want to prevent the report from being generated. I will consider my options - thanks!