Full Version: VBA Loop Check If All Controls on Form are Null?
UtterAccess Forums > Microsoft® Access > Access Forms
Hi UA!
I have a main form where users fill out some required info at the top, then this form also has a tab control on it (5 tab pages). There are a dozen or so controls on each Tab page. I want to make sure a user has filled out at least one of the controls on each (none are required, but want to make sure they've filled something out - in other words - all controls on the page can't be null when they click the submit command button.
It would be time consuming to type "If IsNull(me.ctr1) And IsNull(me.ctr2) etc. etc. Then..."
Oknow there must be a way in VBA to loop through the page's control collection and quickly determine if all are null then something happens.
Can someone point me to an example or code snippet that does this sort of thing?
This should give you an example:
' Call SetTextBoxProperties procedure.
SetTextBoxProperties Me

Sub SetTextBoxProperties(frm As Form)
    Dim ctl As Control

    ' Enumerate Controls collection.
    For Each ctl In frm.Controls
        ' Check to see if control is text box.
        If ctl.ControlType = acTextBox Then
            ' Set control properties.
            With ctl
                .Enabled = True
                .Height = 400
                .SpecialEffect = 0
            End With
        End If
    Next ctl
End Sub
Thanks Walter! This looks like it'll do the trick. Much appreciated!
Hope all is well.
NP. thumbup.gif
Hi Walter,
I've been banging away at this for awhile, and feel it's pretty close, but I get the following error when it gets to the "ctlCurrent.Value = Null" line:
Error #438 Object doesn't support this property or method
Can you see what might be going wrong?
Private Sub chboxAcctLineItemHold_AfterUpdate()
    Dim tabCtl As TabControl
    Dim pagCurrent As Page
    Dim ctlCurrent As Control
    Set tabCtl = Forms!EU_HOLDREQ_MAIN!TabCtl1
    Set pagCurrent = Me.Page1
    For Each ctlCurrent In pagCurrent.Controls
        ctlCurrent.Value = Null
    Next ctlCurrent
End Sub
Some control types would not accept that line of code. For instance, labels cannot be assigned a value of Null, because they don't hold a value. One thing that would help would be to do some checking of what ControlType you are dealing with, as in Mr. Niesz's example where he is confirming that the current control is a Textbox before changing its properties.
I agree with rsmccli's assessment. There are many control types:

acBoundObjectFrame Bound object frame
acCheckBox Check box
acComboBox Combo box
acCommandButton Command button
acCustomControl ActiveX (custom) control
acImage Image
acLabel Label
acLine Line
acListBox List box
acObjectFrame Unbound object frame or chart
acOptionButton Option button
acOptionGroup Option group
acPage Page
acPageBreak Page break
acRectangle Rectangle
acSubform Subform/subreport
acTabCtl Tab
acTextBox Text box
acToggleButton Toggle button

You will need to test for the type before setting it's value to Null.

Another method would be to set a Tag property for the controls you want to check. The Tag property is simply a "free text" property that you can enter anything you want.
OK - thanks guys. That would definitely do it. I'll give it a whirl!
Thanks again for all your help!
Have a great weekend,
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.