documenting ACCESS - listing all ControlSource instances

S

Steve Weiss

Does anyone have a macro or VBA in either Excel or MS Access, which will
list only the controlsources of each object in a form or report? I can use
documenter, but I want a list of only;
Control Name =
ControlSource =

Thanks!
Steve
 
A

Allen Browne

If you are working with Access 2002 or 2003, you can use the AllForms
collection to get a list of the names of all the forms. In earlier versions,
use the Documents in the Containers, or a query like this:
SELECT [Name] FROM MsysObjects
WHERE (([Type] = -32768) AND ([Name] Not Like '~*'))
ORDER BY MsysObjects.Name;

Now you have the list of forms, you will need to OpenForm on each one in
turn. Open in design view, hidden if you desire.

You can then loop through the Controls collection, to get at all the
controls. Not all controls have a ControlSource, e.g. labels and lines do
not. It can be a bit tricky, e.g. stand-alone option buttons and check boxes
do, but those in an option group do not. So, if the control has the
property, you can Debug.Print its Name and ControlSource to the Immediate
Window.

There is an example in this article of how to loop through all forms,
opening them hidden, looping through the controls on each one and then
closing it:
http://allenbrowne.com/ser-46.html
The example actually modifies the controls to solve a particular problem
with Access 2003, but it could serve as an example to get you started if you
are familiar with VBA.
 
F

fredg

Does anyone have a macro or VBA in either Excel or MS Access, which will
list only the controlsources of each object in a form or report? I can use
documenter, but I want a list of only;
Control Name =
ControlSource =

Thanks!
Steve
Click to expand...

I'll guess you wish the information for one specific named form or
report, rather than all the forms and reports.

The below procedure will return the control name and control source of
a specific Form or Report. Copy it to a module.

Comment out the unwanted object, as i have.

Public Sub ReadControlSource()
' Read control source property on all controls in a specified form or
report.
' Comment out the 4 Report lines and uncomment the Form lines as
needed.

Dim c As Control
' Dim frm As Form
Dim rpt As Report
Dim ObjName As String

ObjName = InputBox("What form")

' DoCmd.OpenForm ObjName , acDesign, , , , acHidden
DoCmd.OpenReport ObjName, acViewDesign, , , acHidden

On Error Resume Next

' For Each c In Forms(NameForm)
For Each c In Reports(ObjName)

Debug.Print c.Name & " " & c.ControlSource
Next c
On Error GoTo 0

' DoCmd.Close acForm, ObjName
DoCmd.Close acReport, ObjName

End Sub

If you wish all of the forms and reports at the same time, post back.
 
S

Steve Weiss

Thanks to Allen and Fred. I will try both methods for learning purposes.

Fred, I would appreciate your "all forms and reports" method, also. I am
going to try and figure out how to do that, however, it would be nice to
have that reference in case I get stuck.
Thanks so much!
 
F

fredg

Thanks to Allen and Fred. I will try both methods for learning purposes.

Fred, I would appreciate your "all forms and reports" method, also. I am
going to try and figure out how to do that, however, it would be nice to
have that reference in case I get stuck.
Thanks so much!
Click to expand...

Place these 2 procedures in a module.
You can run them from there.

Public Sub FormControls()
' Read control source property of all controls in all forms.
' Note.. the debug window may not be big enough to display
' all of the data.
Dim Db As Database, doc As Document, ctl As Control
Set Db = CurrentDb
Dim intX As Integer

On Error GoTo Err_Handler

For Each doc In Db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
Debug.Print doc.Name
On Error Resume Next
For Each ctl In Forms(doc.Name)
Debug.Print , ctl.Name & " " & ctl.ControlSource
Next
On Error GoTo Err_Handler
DoCmd.Close acForm, doc.Name
Next

Exit_FormControls:
Set Db = Nothing
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & vbNewLine & Err.Description
Resume Exit_FormControls

End Sub
=============================

Public Sub ReportControls()
' Read control source property of all controls in all reports.
' Note.. the debug window may not be big enough to display
' all of the data.
Dim Db As Database, doc As Document, ctl As Control
Set Db = CurrentDb
Dim intX As Integer

On Error GoTo Err_Handler

For Each doc In Db.Containers("Reports").Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
Debug.Print doc.Name
On Error Resume Next
For Each ctl In Reports(doc.Name)
Debug.Print , ctl.Name & " " & ctl.ControlSource
Next
On Error GoTo Err_Handler
DoCmd.Close acReport, doc.Name
Next

Exit_ReportControls:
Set Db = Nothing
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & vbNewLine & Err.Description
Resume Exit_ReportControls

End Sub

======
 
Top