MS Access – VBA – Loop Through All The Controls on a Form

Below is come sample VBA which illustrates how one can easily loop through all the controls within a form to identify and work with them.

Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Name 'Get the name of the control
    ctl.Value 'Get or set the value of the control
    ctl.Visible = False 'Control the visibility of the control
Next ctl

Now how can this be put to good use? Well one use for such code would be to setup a Select All, or Select None button for a series of check boxes on a form. Below is what the code could look like for each command button:

'Select All
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> True Then
            ctl.Value = True
        End If
    End If
Next ctl
 
'Select None
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> False Then
            ctl.Value = False
        End If
    End If
Next ctl

Lastly, you could easily adapt the general form specific code and transform it into a generic procedure to which you will supply the form name to loop through the control, rather than working with the current form, you could do something along the lines of:

Function YourProcedureName(ControlName As String, frm As Access.Form)
    Dim ctl As Access.Control
    For Each ctl In frm.Controls
 
 
    Next ctl
End Function

Time Sunday, December 5th, 2010, 8:23 pm   |   CategoryMS Access Forms, MS Access VBA Programming  |  RssRSS feed Pinging is disabled. But you can skip to the end and leave a Commentresponse.

Tags:

4 Comments to “MS Access – VBA – Loop Through All The Controls on a Form”

    
  1. Matt
    July 29, 2011 at 11:37 pm

    When I try ctl.value, I get “Object does not support this property or method”. Do I need to turn on any object library references?

    Reply
    • admin
      August 16, 2011 at 9:37 am

      Matt,

      Not all controls have values. You are probably on a label, command button or some other control which does not support this property. This is why it can be important to use the loop If ctl.ControlType = … to ensure the loop only handles text boxes, check boxes, combo boxes, … those control which can have a value.

      Reply
  2. Rajat
    September 8, 2011 at 7:44 pm

    Every time I try to use this, it gives me an error on the following line. Is there an issue with the declaration?

    “Dim ctl As Control”

    Reply
    

Leave a Reply









Spam protection by WP Captcha-Free