Enumerate (List) All Controls, All Forms

After searching and many attempts, I am going to ask for help, if missed a solution on the site I apologize.

What I would like to do is loop thru all the controls on all the forms in my DB, and then write the form name, control name, control type, and hopefully the locked and enabled properties to a table.

In searching the web I have attempted to modify what I found into a function to do this, it will give me the form name and control names, but I can't get the locked and enabled properties to show.

Also I can only open the forms in design view, since I would like to do this with out having to enter any parameters to open the forms.

The hope is to use this information to better document the database and audit the security of the fields on all the forms

Attempt 1

Function test2()
Dim accobj As AccessObject
Dim ctl As Control
Dim strDoc As String

For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
DoCmd.OpenForm strDoc, acDesign
For Each ctl In Forms(strDoc).Controls
Debug.Print strDoc & vbTab & ctl.Name

DoCmd.Close acForm, strDoc

End Function

Attempt 2 (Not Working)

Function DisplayFormInformation()

Dim frm As Form
Dim strObject As String, aob As AccessObject, obj As Object
Dim strTemp As String, strList As String
Dim strTempForm As Variant
Dim myObject As Object
Dim k As Long
Dim frmControl As Control
Dim frmControls As Controls
Dim controltype As String
Dim intcnt As Integer
Dim i As Integer

Dim ConnectToDB As New ADODB.Connection
Set ConnectToDB = CurrentProject.Connection

Dim rTarget As New ADODB.Recordset
rTarget.Open "ObjectDocumenter", ConnectToDB, adOpenKeyset, adLockOptimistic

Set obj = CurrentProject.AllForms

On Error Resume Next

For Each aob In obj
strTemp = aob.Name

DoCmd.OpenForm strTemp, acDesign

strTempForm = "forms!" & strTemp

Set frm = strTempForm

For Each frmControl In frm.Controls

ReDim astrCtlName(0 To intcnt - 1, 0 To 1)

For i = 0 To intcnt - 1
astrCtlName(i, 0) = frm(i).Name

'Use ControlType to determine the Type of Control
Select Case frm(i).controltype
Case acLabel: astrCtlName(i, 1) = "Label"
Case acRectangle: astrCtlName(i, 1) = "Rectangle"
Case acLine: astrCtlName(i, 1) = "Line"
Case acImage: astrCtlName(i, 1) = "Image"
Case acCommandButton: astrCtlName(i, 1) = "Command Button"
Case acOptionButton: astrCtlName(i, 1) = "Option button"
Case acCheckBox: astrCtlName(i, 1) = "Check box"
Case acOptionGroup: astrCtlName(i, 1) = "Option group"
Case acBoundObjectFrame: astrCtlName(i, 1) = "Bound object frame "
Case acTextBox: astrCtlName(i, 1) = "Text Box"
Case acListBox: astrCtlName(i, 1) = "List box"
Case acComboBox: astrCtlName(i, 1) = "Combo box"
Case acSubform: astrCtlName(i, 1) = "SubForm"
Case acObjectFrame: astrCtlName(i, 1) = "Unbound object frame or chart"
Case acPageBreak: astrCtlName(i, 1) = "Page break"
Case acPage: astrCtlName(i, 1) = "Page"
Case acCustomControl: astrCtlName(i, 1) = "ActiveX (custom) Cotrol"
Case acToggleButton: astrCtlName(i, 1) = "Toggle Button"
Case acTabCtl: astrCtlName(i, 1) = "Tab Control"
End Select

controltype = astrCtlName(i, 1)

rTarget("FormName") = strTemp
rTarget("FormCaption") = Eval("forms!" & strTemp & ".caption")
rTarget("ObjectName") = frmControl.Name
rTarget("ControlTipTextValue") = frmControl.ControlTipText
rTarget("Type") = controltype

'Next i

Next frmControl

DoCmd.Close acForm, strTemp


End Function

Thanks for any help

Post your answer or comment

comments powered by Disqus
What I am trying to do is write a routine that will not only list all the forms that in access but then open the form and list all the controls in them.
This code works to a point but it doesn't list all the forms nor all the controls. ultimately I would like to write this out to tables as I have a customer that wants to lock and maintain controls per from. A pain I know but it pays

Sub GetAllFormsAndControls()
On Error Resume Next
Dim objAccObj As AccessObject
Dim objFormAs Object
Dim strForm As String
Dim ctl As Control
Dim objActiveForm As Form

Set objForm = Application.CurrentProject
For Each objAccObj In objForm.AllForms
strForm= objAccObj.Name
Debug.Print strForm
DoCmd.OpenForm strForm, acDesign
Set objActiveForm = Application.Screen.ActiveForm
For Each ctl In objActiveForm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox, acOptionButton, acToggleButton, acSubform, acCommandButton
Debug.Print " " & ctl.Name
End Select
Next ctl
DoCmd.close acForm, strForm
Next objAccObj
End Sub

Thanks for any help you can shed on this.

I have a form with a mixtue of controls on, Combo Boxes looking up different tables, text boxes controlled by what is selected in the combo box and plain text boxes. All controls are unbound.
I used a button to run code which inserts the values entered/selected into a table. I would like the button to then clear the form ready for the user to enter new values.
I can do this by closing and reopening the form - but this isn't very neat.

I have tried using me. with refresh or undo neither of which work, but give no error messages.

I have also tried the code

Dim ctl As Control
For Each ctl In Me.Controls
ctl = vbNull String

This gives me a runtime error 438.
Please help!!

What I want is to open all forms, one by one, and going through all controls to edit The .OnMouseMove event
How can it be done ?

I tried to use this piece of code:

	Dim frmObj As AccessObject
Dim ctl as Control
For Each frmObj In Application.CurrentProject.AllForms
    DoCmd.OpenForm frmObj.name, acDesign

This work but I can't find the way to go through controls as what I get is AccessObject and not a Form object, and can't use this part of the code:

	    For Each ctl In frmObj.name
        With ctl
        End With
    Next ctl

Hi People,

I have a manu form which has a command button. When the button is press it should open a form called FrmBank2 and delete all controls on the form by looping through them. The problem is only 6 controls (textboxes) get deleted and the rest of the controls (Textboxes) remain on the form. I need to delet all the textboxes on the form?. I am using the following code -

Dim ctrlCntrl As Control
Dim frmParent1 As Form

DoCmd.OpenForm "FrmBank2", acDesign, , , , acHidden
Set frmParent1 = Forms!FrmBank2

For Each ctrlCntrl In frmParent1.Controls
DeleteControl frmParent1.Name, ctrlCntrl.Name
Next ctrlCntrl

End Sub

Any ideas as to what might be the problem.


Is there any way i can loop through all controls on a form/report and delete them.
I have seen a thread about this and I think Pat explained it needs to be done backwards but I'd appreciate it if anyone has achieved this to show me the code.
I keep on getting an error that the count is more than the actual number of controls.

Hello - I'm creating an entry form where the first field that needs to be entered is an ID number.

In the interest of ensuring that this number gets entered before any other information, I'd like to set all the other form controls to Visible=False if the value of the ID field is null. Of course, if there is a value in the ID field, I'd like all the controls to be set to Visible=True.

My question - is there any kind of a global symbol (ala the "*" wildcard) that I can use in my VB code to tell the form to set all controls to Visible=True once there is a value in the ID field?

There are about 30 other controls on the form, and in the interest of saving bytage, I'd like to get around having to specify Visible=True for each and every one of them.

Thanks in advance for any assistance you can give with this!

how to lock set of controls in a form with out specifying one by one. i tried a for loop. but it didnt work. i couldn't get the Locked property of the controls.

here is my code:

For x = 0 To Me.Count - 1
If TypeOf Me.Controls(x) Is TextBox Then
Me.Controls(x).Locked = True
ElseIf TypeOf Me.Controls(x) Is ComboBox Then
Me.Controls(x).Locked = True
End If
Next x

the above code gives an error.

how to lock all controls in the form at once. answer would be really appreciated.

Hi guyz!

I've created a tab control form in Access 2003 and want to open it with Access 2007.

When I open the database containing the tab control form in 2007 I enable the content, and then open the form with the tab control on it. However, the tab control doesn't show up with the multiple tabs at all. It only shows the content of what would be the first tab (A data entry form used to update a particular source data table - it's a list of job names with a unique identifier to be used in a combo box) as if it were a single form with no tab controls.

I need to have the multiple tabs show up at the top of the form just as it does in Access 2003. Is there some property I need to set, or Macos TRUST setting, or other security level change?

I've done this before for a client moving from 2003 to 2007 but haven't had to do this recently and I've forgotten what it was that made the 2003 Tab Control Form show up properly. DUH!

Little help here please Thanks!


I like to create a form for managing client data with the following behaviour:
When the form opens all controls are displayed, without any data in it and locked.
When clicking on a button to enter a new client, all controls must be unlocked, and after entering the new client and hitting the button "Save",
all controls must be locked again, the new record displayed and the appropriate navigation buttons activated.
The same behaviour must be realised when hitting the button to "edit" a record . If for some reason the editing a record or the entering a new record must be canceled, all controls must be locked again and the appropriate navigation buttons activated. .
Is that a possible goal and are these requirements realistic for such a form?.

I'm creating a form that will list all report objects in my database, and then the user will select one and print it.

The controls on the form are:List box named lstReports Check box named chkPreview Command button named cmdOpenReport I did a search and found some code that does this and still works, but basically, I have two concerns. I have one report I want to hide - that one is called 'rptMarketBudgetSheet' while the rest of the reports have names in standard English (I'd planned on doing this from the start). I also want to ensure that only one report is selected, as no user will need to print multiple reports, and getting multiple different reports opening might just confuse some of them.

I'm really rusty (it's been years since I've done this), so I'm not sure how I could go in and make sure that reports beginning with "rpt*" don't get listed. If anyone can tell me what I need to do to do that, I'd be most appreciative. Same with ensuring only one report gets selected.

List box code:

	Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    ' Purpose:  Supplies the name of all saved reports to a list box.
    ' Usage:    Set the list box's RowSourceType property to:? EnumReports
    '               leaving its RowSource property blank.
    ' Notes:    All arguments are provided to the function automatically.
    ' Author:   Allen Browne        (removed email due to post count)    Feb.'97.
    Dim db As dao.Database, dox As Documents, i As Integer
    Static sRptName(255) As String              ' Array to store report names.
    Static iRptCount As Integer                 ' Number of saved reports.
    ' Respond to the supplied value of "code".
    Select Case code
        Case acLBInitialize                     ' Called once when form opens.
            Set db = CurrentDb()
            Set dox = db.Containers!Reports.Documents
            iRptCount = dox.Count               ' Remember number of reports.
            For i = 0 To iRptCount - 1
                sRptName(i) = dox(i).Name       ' Load report names into array.
            EnumReports = True
        Case acLBOpen
            EnumReports = Timer                 ' Return a unique identifier.
        Case acLBGetRowCount                    ' Number of rows
            EnumReports = iRptCount
        Case acLBGetColumnCount                 ' 1 column
            EnumReports = 1
        Case acLBGetColumnWidth                 ' 2 inches
            EnumReports = 2 * 1440
        Case acLBGetValue                       ' The report name from the array.
            EnumReports = sRptName(row)
        Case acLBEnd
            Erase sRptName                      ' Deallocate array.
            iRptCount = 0
    End Select
End Function


I have a field named 'Locked' (which is a check box) in my form that is bound to the master table.

The checkbox stores TRUE or FALSE in the table.

I would like to lock all control properties in the form and subform (except for the 'Locked' control) automatically when selecting the LOCKED control and on leaving the record.

A simple VBA solution using the event 'after update' would be appreciated.


Hi is there a way to loop through all controls in form frame340 and check for texbox type and then their values?

Thanks in advance.

I could not get suitable code....i did tried searching

I am trying to find a dictionary or reference which lists all controls, methods and properties available for MS Access forms.

Is there such a thing?

I have a waste tracking database for a restaurant. I have an item table that lists all the items that could be wasted. I would like to list these items on a form with a text box to enter the number of units that were wasted. Is there a way to do this and if so how?

Good day everyone. I am trying to put a combo box in a form that lists all of my reports. I'd like to be able to select all of my reports from a list and when I choose a specific report, it automatically updates and opens. For example, I have report "A", "B" and "C" already formatted and in my objects list. I'd like to add Report "A", "B" and "C" to a combo box and when I select either of those reports, they update automatically (based on any information that I put into the tables / queries) and open. I've tried using the wizard to do this, but reports are not an option in the dialogue box. I have also searched these forums for answers, but these forums seem to be for the advanced and the technical jargon that's used is WAYYYY over my head. Is there anyone that can tell me what to do in laymans terms to help me figure this out? I have no programming experience, but I'm slightly familiar with Macros if I need to use them. I appreciate your help.

I have the following code in a continuous form's Before update event:

	Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Nz(Me.Controls, "")) = "" Then
Dim Response As Integer
Response = MsgBox("You must fill in all fields", vbOK)
End If
End Sub

When I test the code I get the following error:

Quote: Runtime Error '450':

Wrong number of Arguments or invalid property assignment and the red line in the code above is highlighted. I have googled this but most hits involve excel or give explanations that I don't understand. This is the first time I have ever tried to check all controls on a form at once, and would really prefer one error message to one for each control (that seems tedious) So any help in figuring this out would be helpful, as I can't even get to test my message box to see if I have it set up correctly.

Hi Forum Members,

I want to ask how can I list all standard Data Types, Classes, Collections, and Controls which are loaded currently in a VBA IDE session using VBA CODE (programmatically). I need for developing an Add-In for MS Access.

For clarification, from References you added Microsoft DAO 3.6 Object Library, then you added Microsoft Windows Common Controls 6.0. I should get all the primitives data types (Integer, Byte, Boolean, ...), all classes exist in DAO Library, all standard controls (Label, TextBox, ...), and all controls exist in Microsoft Windows Common Controls 6.0.

For those who know MZTools I want exactly like what MZTools give in its Comboboxes when you try to add a Procedure.

I spent lots of time in the net for a clue but am result less. Many asked the same question but there were no good answer.

So, I think I have a good routine to make all controls of a form invisible except a few, but I'm getting "You entered an expression that requires a form to be the active window." I've put the below code in On Open, On Load, On Activate, and still get the error with this line being highlighted,
Quote: Set frmCurrentForm = Screen.ActiveForm Please help. I actually would like to use this in other forms too. Do I just make a public function?

Quote: Public Sub CloseAllControls()
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm

Dim ctl As Control
Dim CurrentForm As Form
Set CurrentForm = Screen.ActiveForm

For Each ctl In CurrentForm.Controls
ctl.Visible = False
Next ctl
End Sub

Hi I would appreciate any help here, I would like a list box or Combo Box on a form which will list all dbf files in "F:Races"
then whichever file I hightlight would then like to use it to run a macro to import it to a table called today overwriting the old table called Today.
I have searched a lot for this info in help and the web but I do need your help.

I want to display the all the labels for a form in an other form. Is this possible.
Basicly have a drop dow box that has all the forms in it and then based on the combo box list the label for that form.

Ok let me explain
I have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or C

When I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.

I thought about running an append query but this would mean running the data several times with possible duplication of client names.

Ideally I would like to have my report to show
Date: 31/07/2006
Name A B C
K.Brown Y Y
J Blogg Y Y
K Smith Y
K Ellum Y Y

then I can just use a count at the bottom

The methods I have tired will filter event date A, but will not show J Blogg
If I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.

Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1


I have a big table which lists all of my customers, and their associated details (address, phone number, etc).

I want to produce a report for any particular customer (chosen by combobox on form) which lists the fields which are blank in the following format:

XXX Co Ltd
Address Line 2
Fax Number
Website Address

Is there a way of doing this?



Maybe some good soul can lead me out of the darkness and back into the light...............

I have a form with 5 tab controls, so far so good, everything works great....
except the tab stops. Trying to set the tab stops starting at page 1, field 1 at tab stop "0" and so forth (tab stop 1, tab stop 2, etc) for consecutive fields is like trying to play pin the donkey in the dark with a high speed rat ...

The tab stop order keeps reshuffling!!! I have attempted to set all controls on the four last tab controls to tab stop "no" so that I can at least try to get page one's tab stop order sorted out - no luck. I no sooner set the first field to tab stop "0" and poof it sets itself to "37" or "13" or "10".

I have also set the tab controls to no tab stop.

I have many fields on these tab controls - please tell me its not possible that this is what is causing the problem?

I look forward to any takers on this.

Kind Regards

I have a continuous subform that allows for scores to be entered for students. I want to disable all controls to change data for previous entries but leave the new record that a continuous form creates enabled for new entries. I also want to have a button to place on the right hand side to edit each previous "disabled" row if the button is clicked.

I've played around with this but haven't had any success. Any ideas on how to accomplish this?


Not finding an answer? Try a Google search.