Loop through controls Ms Access 365 (1 Viewer)

Wysy

Registered User.
Local time
Today, 13:21
Joined
Jul 5, 2015
Messages
292
Hi,
I can not figure out how to loop through the controls in a form. The tutorials seems not working with MS Access 365.
PHP:
Dim ctr As Controls
    
For Each ctr in me.controls
    if ctr.TypeName=acTextBox then
        msgbox ctr.name
    end if
next ctr

This is just the testing example. What i want to program is conditional formatting using looping.
Seems the MS Access 365 VBA does not offer this possibility or my version references are not correct.
Any idea?
thanks
 

Auntiejack56

Registered User.
Local time
Tomorrow, 06:21
Joined
Aug 7, 2017
Messages
168
I think, of the top of my headlights, that it should be

Code:
Dim ctr as Control
 

Wysy

Registered User.
Local time
Today, 13:21
Joined
Jul 5, 2015
Messages
292
did that, does not work
 

Wysy

Registered User.
Local time
Today, 13:21
Joined
Jul 5, 2015
Messages
292
Yes i did. I do not see of the option ctr.TypeName. It gives an error.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:21
Joined
May 7, 2009
Messages
16,881
try this:

if Typeof ctr Is TextBox then
...


OR

if ctr.TypeName="TextBox" then
...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:21
Joined
Jul 9, 2003
Messages
14,652
From my Code Library

Using TypeOf:-
Code:
Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is CheckBox Then
            If Ctrl = False Then Form_frmQC.sFrmWinTblFaults.Visible = True
        End If
    Next Ctrl

Using Select Case:-
Code:
Dim strFoundControlName As String
Dim strNamePart As String

Dim Ctrl As Control
Dim X As Integer

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acCheckBox, acComboBox, acLabel, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton
            If Right(Ctrl.Name, Len(Ctrl.Name) - 3) = strNamePart Then
                X = X + 1
                strFoundControlName = Ctrl.Name
            End If
        End Select
    Next Ctrl

And a Video:-

Loop Through a Set of Controls - Nifty Access​

 
Last edited:

Wysy

Registered User.
Local time
Today, 13:21
Joined
Jul 5, 2015
Messages
292
Code library might be the real problem. Controltype or typename is not offered when i type ctr and dot.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:21
Joined
May 21, 2018
Messages
6,450
These properties have never shown in intellisense for some reason, but are supported.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:21
Joined
Sep 21, 2011
Messages
10,879
As far as I can see TypeName is a fictitious property?
Certainly balks in 2007, plus every time I have seen this method, it is ControlType that is tested? as per Uncle Gizmo's video.
Or is TypeName new to 365? or after 2007?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:21
Joined
May 21, 2018
Messages
6,450
@Gasman,
TypeName has been around forever
Most people use controltype

Here is a simple demo with a bunch of controls I saved as 2002 for you.
Code:
Public Function msgType()
  MsgBox "Type Name: " & TypeName(ActiveControl) & vbCrLf & " Control Type Constant: " & ActiveControl.ControlType
End Function
 

Attachments

  • TypeName.mdb
    368 KB · Views: 89
  • TypeName.accdb
    736 KB · Views: 87

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:21
Joined
May 21, 2018
Messages
6,450
TypeName shows up in object explorer
typeName.jpg


However ControlType does not show in object explored. However, the associated constants do.
controlType.jpg
 

Attachments

  • controlType.jpg
    controlType.jpg
    73.6 KB · Views: 97

Gasman

Enthusiastic Amateur
Local time
Today, 21:21
Joined
Sep 21, 2011
Messages
10,879
Hmm, yes, I have typename in Information as per yours.
looking for ControlType gave me the pic below.
However my VBA complained when I copied the O/P's code with TypeName in it?
1652534218016.png

Trying again I get
1652534439356.png

1652534475482.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:21
Joined
Sep 21, 2011
Messages
10,879
Typename appears to want a control name?
? typename("text25")
String

In fact controls with numeric source still show as string?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:21
Joined
May 21, 2018
Messages
6,450
Typename appears to want a control name?
? typename("text25")
No. As stated typename returns the variable type.
TypeName needs a variable and returns the string name. See my demo
?TypeName(me.TextboxOne)
returns "TextBox"

In fact controls with numeric source still show as string?
No but if the value of the textbox is a string it would like so
?typeName(Me.textboxOne)
returns "Textbox"
?typeName(Me.textboxOne.value)
returns "string" if a string is in there, or "date", etc.

The reason it does not work is the OP was mixing and matching the two. One is a property of a control the other is a vba method.

either
Code:
ctr.ControlType = Actextbox  (where actextbox is a long enumerated constant)
or
Code:
typename(ctr) = "TextBox"  (there "textbox" is a literal string)

As @arnelgp points out there is also "type of"

Code:
If TypeOf ctr is Textbox

The typeof is not a method, it is a comparitive operator. The thing following the "IS" is not a constant or a string but the actual class or datatype.
So the following all return true
Typeof CTR is Object
Typeof CTR is Control
TypeOf CTR is Textbox

that is because control is a subclass of object and textbox is subclass of control
 
Last edited:

LarryE

Active member
Local time
Today, 13:21
Joined
Aug 18, 2021
Messages
217
Hi,
I can not figure out how to loop through the controls in a form. The tutorials seems not working with MS Access 365.
PHP:
Dim ctr As Controls
   
For Each ctr in me.controls
    if ctr.TypeName=acTextBox then
        msgbox ctr.name
    end if
next ctr

This is just the testing example. What i want to program is conditional formatting using looping.
Seems the MS Access 365 VBA does not offer this possibility or my version references are not correct.
Any idea?
thanks
This Public Function will loop through all controls on any active form and print to the Immediate window:
Code:
Public Function ScrollFormControls()
Dim ActiveFrm As Form
Dim ActiveCntrl As Control
    Set ActiveFrm = Screen.ActiveForm
    DoCmd.OpenForm ActiveFrm.Name, acDesign, , , acFormPropertySettings
    With ActiveFrm
        For Each ActiveCntrl In ActiveFrm
            Debug.Print ActiveCntrl.Name
        Next
    DoCmd.OpenForm ActiveFrm.Name, acNormal, , , , acWindowNormal
    End With
Exit Function
End Function
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:21
Joined
Jan 23, 2006
Messages
14,443
This works with O365 2204 15128.20224 click to run
Code:
' ----------------------------------------------------------------
' Procedure Name: docForms
' Purpose: To document all controls on all forms in this database and indicate Date
'                and store results in table tblzzFormDocumentation
' Procedure Kind: Function
' Procedure Access: Public
' based on material from Tektips (2017)
' Author: Jack
' Date: 14-May-22
' ----------------------------------------------------------------
Public Function docForms()
10        On Error GoTo docForms_Error
20        On Error Resume Next
          Dim obj As AccessObject, dbs As Object
          Dim ctrl As Control
          Dim frm As Form
          Dim FName As String
          Dim cName As String
          Dim PName As String
          Dim pValue As String
          Dim prp As Property
          Dim ctrlPropSQL As String
          
30        Set dbs = Application.CurrentProject
          
          'FOR EACH FORM
40        For Each obj In dbs.AllForms
              'OPEN THE FORM IN DESIGN VIEW
              'NEEDS TO BE OPEN, TO GET AT EACH CONTROL - DESIGN VIEW SO WE DON'T INITIALIZE ANY CODE
50            DoCmd.OpenForm obj.name, acDesign
                  'FOR EACH CONTROL ON THE FORM
60                For Each ctrl In Forms(obj.name).Controls
                      'IF THE CONTROL TYPE IS NOT A LABEL
70                    If ctrl.ControlType <> acLabel Then
                          'LOOP THROUGH EACH PROPERTY ON THE CONTROL
80                        For Each prp In ctrl.Properties
90                            pValue = Nz(prp.value, "")
                              'IF PROPERTY VALUE ISNT BLANK OR NULL
100                           If pValue <> "" Then
110                               FName = obj.name
120                               cName = ctrl.name
130                               PName = prp.name
                                  'MUST HAVE A TABLE WITH THOSE COLUMN NAMES - THIS IS IN SQL AND IS A LINKED TABLE, IN MY SET UP
                                  'I ALSO HAVE AN ID COLUMN, WHICH IS AN IDENTITY, AND THE PROPERTYVALUE IS VARCHAR(MAX)
                                      
                                      'PREPARE SQL INSERT STATEMENT WITH EACH COLUMN VALUE
140                                   ctrlPropSQL = "INSERT INTO tblzzFormDocumentation ([FormName],[ControlName],[PropertyName],[PropertyValue], DateChecked) VALUES ('" & FName & "','" & _
                                                      cName & "','" & PName & "','" & pValue & "', Date() );"
                                      'EXECUTE SQL
150                                   CurrentDb.Execute ctrlPropSQL, dbSeeChanges
160                           End If
170                       Next prp
180                   End If
190               Next ctrl
              'CLOSE THE FORM, ONCE DONE LOOPING THROUGH ALL CONTROLS
200           DoCmd.Close acForm, obj.name, acSaveNo
210       Next obj
220   MsgBox "Forms have been documented @ : " & Now
          
230       On Error GoTo 0
docForms_Exit:
240       Exit Function

docForms_Error:

250       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure docForms, line " & Erl & "."
260       GoTo docForms_Exit
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:21
Joined
Sep 21, 2011
Messages
10,879
We are on post 20, and the O/P last responded on post 10, and I think we have established that they have the syntax completely wrong as confirmed by @MajP
Several links have been shown to use what I would call the 'standard' method, which can be found with a quck google, or watching a supplied video in this thread? :(
 

Users who are viewing this thread

Top Bottom