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
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?
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.
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”
I’m not 100% sure, but I’d start by checking your references.
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
dim txt as TextBox
For each ctl in Me.Controls
If ctl.ControlType = acTextBox then
set txt = ctl
txt.Value = “xyz”
End If
Next ctl
set ctl = Nothing
set txt = Nothing
You didn’t say which line was being flagged as problematic. Regardless, this would be my approach:
Dim ctl As Control
‘ Dim txt As TextBox
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
‘ Set txt = ctl
‘ txt.Value = “xyz”
ctl.Value = “xyz”
End If
Next ctl
Set ctl = Nothing
‘ Set txt = Nothing
When I use ctl.Value=”abc”, I get this error:
“You can’t assign a value to this object.”
You’d have to post you full code, and give more details, because the basic code is sound. For instance,
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = “some value”
End If
Next ctl
Will populate any textbox with the designated string. So it does work. That said, if you loop through all the form control without checking the ControlType it is quite possible you are trying, or your code is trying to push a value to controls that will not accept them, controls such as labels (which use a caption). I’d need more information to be able to help you more.
what is ACCESS.CONTROL in vba .
where can get vba materials online.
I’m affraid I’m not familiar with what it is you are asking. What is it exactly you are trying to do? Please explain a little more and I will better be able to guide you.
I made 5 versions of this, and I love how easy it makes documenting what I have and where.
The versions are below, in case they help others save time.
AllForms – debug.print the name of every open form (helps when working on a dev version and want a clean slate)
getCombos – gets the name of each form, each combo, and each combo’s rowsource. Helped for documenting each one, so I could ensure they all had notinlist code, and which should have a button for adding values to lists, or not.
CheckForms – I used this, to determine which forms had navigation buttons on or off. I use custom buttons and text box with record counts, because to many forms and sub forms, and the access nav buttons kinda suck. I wanted to have a clear list of which I had already turned off, and which I needed to turn off.
getFormCmds – I’m working through which forms/subforms have delete buttons, and which need them or need something to let users know they can’t delete that level of record. This gave me each form and each command button, so I could have a complete list, without manually going from form to form, hoping I didn’t miss any.
getForms – I used this with the form cmds. Once I filtered those results to the ones with delete buttons, I needed a complete form list, so I could make sure I had forms with no buttons accounted for.
Sub AllForms()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
‘ Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
‘ Print name of obj.
Debug.Print obj.Name
End If
Next obj
End Sub
Public Function getCombos()
On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim ctrl As Control
Dim frm As Form
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
For Each ctrl In Forms(obj.Name).Controls
If ctrl.ControlType = acComboBox Then
Debug.Print obj.Name & “^” & ctrl.Name & “^” & ctrl.RowSource
End If
Next ctrl
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj
End Function
Public Sub CheckForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
‘Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
If Forms(obj.Name).NavigationButtons = True Then
Debug.Print obj.Name & ” Nav Buttons On”
Else
Debug.Print obj.Name & ” Nav Buttons Off”
End If
DoCmd.Close acForm, obj.Name, acSaveYes
Next obj
End Sub
Public Function getFormCmds()
On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim cmd As Control
Dim frm As Form
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
For Each cmd In Forms(obj.Name).Controls
If cmd.ControlType = acCommandButton Then
Debug.Print Chr$(34) & obj.Name & Chr$(34) & “,” & Chr$(34) & cmd.Name & Chr$(34)
End If
Next cmd
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj
End Function
Public Function getForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object
Dim cmd As Control
Dim frm As Form
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
Debug.Print Chr$(34) & obj.Name & Chr$(34)
DoCmd.Close acForm, obj.Name, acSaveNo
Next obj
End Function
Hello
I used the label object and I want to change the text ( text from Table , use DLookup)
But there is no data in the settings label (ctl.Value or ctl.Caption)
Thank you for your help
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
Dim naw As String
naw = ctl.Name
ctl. = DLookup(“ctlTextF”, “tbl1”, “ctlName = ‘” & naw & “‘”)
End If
Next ctl
Set ctl = Nothing
I’d be doing something like:
Dim ctl As Access.Control Dim naw As String For Each ctl In Me.Controls If ctl.ControlType = acLabel Then naw = ctl.Name ctl.Caption = Nz(DLookup("ctlTextF", "tbl1", "ctlName = '" & naw & "'"), "") End If Next ctl Set ctl = Nothingthanks Professor 🌹🌹🌹