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
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
Attempt 2 (Not Working)
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"
1. List all Control names in all Forms in Access 2010
2. Clear all controls in a form.
3. how do I open all forms, and going through all controls?
4. looping through a Form and deleting all controls!
5. Loop thru all controls on a form/report and delete them
6. Is there a "Global Symbol" for all a form's controls?
7. how to lock all controls in the form at once
8. Access 2003 Tab Control Form not working in 2007
9. Opening a Form and locking all controls
10. Listing All Report Objects in List Box - Wish to hide some
11. Form : Lock all controls in form
12. Loop through all controls in form frame340
13. Forms Controls, Methods and Properties
14. Listing all items from a table in a form
15. List all Reports in a Combo Box
16. runtime error 450 in beforeupdate event, referring to all controls
17. List all data types programmatically
18. Make all controls invisible but few
19. List all DBF files
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
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
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,
Debug.Print " " & ctl.Name
DoCmd.close acForm, strForm
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
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 -
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!
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!
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
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
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)
Erase sRptName ' Deallocate array.
iRptCount = 0
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)
When I test the code I get the following error:
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.
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,
Set frmCurrentForm = Screen.ActiveForm
Please help. I actually would like to use this in other forms too. Do I just make a public function?
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
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
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.