Cycle forms and set font type and size

[swedbera]

Is it possible through code to cycle through all forms and set the properties
of its controls, such as font name and font size?

Arlene

 

[Brendan Reynolds]

Public Sub SetFontAndSize()

Dim aob As AccessObject
Dim frm As Form
Dim ctl As Control
Dim prp As Property

For Each aob In CurrentProject.AllForms
DoCmd.OpenForm aob.Name, acDesign
Set frm = Forms(aob.Name)
For Each ctl In frm.Controls
For Each prp In ctl.Properties
If prp.Name = "FontName" Then
prp.Value = "Comic Sans MS"
'if control has FontName property, assume
'it also has FontSize property.
ctl.FontSize = "14"
Exit For
End If
Next prp
Next ctl
DoCmd.Close acForm, aob.Name, acSaveYes
Next aob

End Sub

 

[David C. Holley]

Yes. The code below was written to snoop around and database, grab
control names and controlSources and capture the data to a table. The
principle is the same. It should be easy for you to adapt it. I've added
comments to help you understand what does what.

Sub snoopCtl()

Dim sourceRS As DAO.Recordset
Dim targetRS As DAO.Recordset
Dim ctl As Object

'The MSysObjects table is a table that contains a list of all objects in
the database, identifying their name, owner, type, etc.
'-32768 is the TYPE for form objects

Set sourceRS = CurrentDb.OpenRecordset("SELECT * FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768));", dbOpenForwardOnly)

While Not sourceRS.EOF
Debug.Print sourceRS.Fields("Name")
DoCmd.OpenForm sourceRS.Fields("Name"), acDesign, , , ,
acHidden
For Each ctl In Forms(0).Controls
Debug.Print " " & ctl.Name
'Code to change the properties goes here you may want
to use the With ctl/wend to make life easier
Next
DoCmd.Close acForm, sourceRS.Fields("Name"), acSaveNo
'Check help the contstant is either acSave or acSaveYes not certain
sourceRS.MoveNext
Wend

sourceRS.Close
Set sourceRS = Nothing

End Sub

 

[swedbera]

Hi David & Brendan:

Thanks so much. Brendans solution was just what I was trying to do, but
couldn't get it right and David's solution has just given me some new ideas
on what is possible to do.

I really appreciate your quick responses and suggestions. There are still
so many things that I don't understand how to do in vba and it is so nice to
have you people to turn to.

Arlene