Loop Through SubForm Controls (1 Viewer)

adickie · Feb 24, 2003

I know this has been convered before, but those thread all seem to stop short of the sort of code that I need.

I have a form (frmSupplyRequestDetail) that has 100 or so textboxes as well as a subform (frmGlovesSubForm). The subform has about 30 textboxes of its own. I need to loop through the textboxes in both the mainform and the subform to check for nulls. Currently I have the following code that works fine to go through the main form.

Dim ctl as Control

For Each ctl In Forms!frmSupplyRequestDetail
If ctl.ControlType = acTextBox Then
If ctl.Visible = True Then
If ctl.Locked = False Then
If Not IsNull(ctl) Then
NullCheck = "No"
End If
End If
End if
End if
Next ctl

I know I could create another loop that goes through the subform, but I would rather incorporate it all into one loop as more subforms are going to be added in the future and I don't want to have to create a loop for each one.

Thanks.

Travis · Feb 24, 2003

Try This:

Example on how to call

Code:

Private Sub BeginCheck_Click()
  Dim sCheck as string

    sCheck=NullCheck(Me)

    Msgbox sCheck

End Sub

==========================================
Add this to a New Module (This will allow you to use this code from any form)

Code:

Public Function NullCheck(frm As Form) As String

    Dim ctl As Control
    
    For Each ctl In frm
        If ctl.ControlType = acTextBox Then
            If ctl.Visible = True Then
                If ctl.Locked = False Then
                    If Not IsNull(ctl) Then
                        NullCheck = "No"
                        Exit Function  '*You have encountered a Null value you don't need to continue
                    End If
                End If
            End If
        ElseIf ctl.ControlType = acSubform Then
            NullCheck = NullCheck(ctl.Form)
            If Null Check="No" Then Exit Function '*
        End If
    Next ctl

End Function

Nouba · Feb 24, 2003

one possibility is calling a custom procedure twice. i. e.

Code:

CheckTextBoxCtl Forms!frmSupplyRequestDetail
CheckTextBoxCtl Forms!frmSupplyRequestDetail!sfrmCtrlName.Form

Private Sub CheckTextBoxes(oForm As Form)
  Dim ctl           As Variant

  For Each ctl In oForm
    If ctl.ControlType = acTextBox Then
      If ctl.Visible Then
        If Not ctl.Locked Then
          If Not IsNull(ctl.Value) Then
            NullCheck = "No"
          End If
        End If
      End If
    End If
  Next ctl
End Sub

adickie · Feb 24, 2003

Travis, I got yours first and it worked like a charm. Nouba thanks for the help, but like I said Travis' code works and I know better than to mess with something that is working.

Thank you both for your help.

Travis · Feb 25, 2003

I am calling the same procedure twice. The difference between Nouba's solution and mine is that my code does the recursive loops automatically (and since Access limits sub forms to three deep there is almost no possibility of an Out-Of-Stack Issue).

The other difference is that the code I provided will look through the controls in this order:

First Control On Main Form
Second Control On Main Form
Third Control On Main Form (This is also a Sub form)
First Control on SubForm
Second Control on SubForm
Fourth Control on Main Form
etc...

Loop Through SubForm Controls (1 Viewer)

adickie

New member

Travis

Registered User.

Nouba

Registered User.

adickie

New member

Travis

Registered User.

Similar threads

Users who are viewing this thread