I need to do a 'clean-up' for all reports in a database. I'd like to scan all
reports and all controls in each report and test and reset properties. E.g I
want to change all fonts to Arial, get rid of all italic text etc. etc.
Does anyone have sample code to loop thru all reports, all controls in a
report and then test and update properties?
Thanks in advance
Here is an example.
It will debug.print all of each control's properties, but I've just
changed the FontName here.
You can add ctl.FontSize, ctl.FontStyle, etc. as well as other
properties (ctl.Left, etc.).
Note: Not all controls have the same properties, i.e. Labels have a
Caption property, Text controls do not, etc.
You'll need to add error handling or use an If/Then/Else statement to
ask for the correct property in each type if control. I've given you
just a start.
Public Sub ChangeAllFonts()
Dim doc As DAO.Document
Dim cont As DAO.Container
Dim ctl As Control
Dim prp As Property
With CurrentDb
For Each cont In .Containers
If cont.Name = "Reports" Then
For Each doc In cont.Documents
DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
Debug.Print
For Each ctl In Reports(doc.Name).Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is Label _
Or TypeOf ctl Is ListBox Or TypeOf ctl Is ComboBox Then
Debug.Print ctl.Name,
For Each prp In ctl.Properties
Debug.Print " " & prp.Name & " " & prp.Value
Next prp
'Change all the FontName's to "Arial"
ctl.FontName = "Arial"
End If
Next ctl
DoCmd.Close acReport, doc.Name, acSaveYes
Next doc
End If
Next cont
End With
End Sub
