Change fonts throughout database

[Rose B]

I have created a database in Access 2007 and used a default design for the
forms etc., which uses Font Calibri 11 for most buttons/labels. However, when
run on an XP machine these fonts exceed the size of the label/button size.
What I would like to do is to go through and change all of the font styles
and sizes throughout the database via VB. Can anyone tell me where/how I can
do this? (I am sure all Access objects are available - but just don't know
where!).

(Is there a recommended set of defaults to use so I don't fall into the same
trap again?)

Thanks in advance.

 

[Jerry Whittle]

Is it a problem with all XP machines or just one? You can change font sizes
on a particular machine. Maybe someone changed the default to Large on that
one computer.

 

[Rose B]

No, it seems to be more than one. I know that I can go through each form
individually in design mode (which I have started to do) but I thought that
there might be a way that I could loop through some kind of recordset
containing the Access objects and get the job done quicker. (I am finding
Arial with a slighly smaller font size works better)

 

[John Spencer]

You could write a VBA function to open each form in design mode, step through
the controls and set specific control types to the desired font and font size
and then save the changes.

If you do that I would make a backup BEFORE I tried it, just in case something
does not work the way you want it to.

The following UNTESTED function might give you the basic idea - it has no
error code and has not been tested, so use at your own risk.

Public Function fFixForms()
Dim frmName As String
Dim ctlAny As Control
Dim I As Long
Dim iSave As Long

For I = 0 To CurrentProject.AllForms.Count - 1
iSave = acSaveNo
frmName = CurrentProject.AllForms(I).Name
DoCmd.OpenForm frmName, acDesign
For Each ctlAny In Forms(frmName).Controls
Select Case ctlAny.ControlType
Case acCommandButton
ctlAny.FontName = "Arial"
ctlAny.FontSize = 11
iSave = acSaveYes
Case acLabel
ctlAny.FontName = "Arial"
ctlAny.FontSize = 10
iSave = acSaveYes
End Select
Next ctlAny

DoCmd.Close acForm, frmName, iSave
'Change acSaveYes to acSavePrompt if you want to decide
'whether or not to save on each form

Next I

End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[fredg]

No, it seems to be more than one. I know that I can go through each form
individually in design mode (which I have started to do) but I thought that
there might be a way that I could loop through some kind of recordset
containing the Access objects and get the job done quicker. (I am finding
Arial with a slighly smaller font size works better)

Does this help?

Public Sub ChangeAllFonts()
Dim Db As DAO.Database, doc As Document, ctl As Control
Set Db = CurrentDb

On Error GoTo Err_Handler
For Each doc In Db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
For Each ctl In Forms(doc.Name)
On Error Resume Next
ctl.FontName = "Arial"
ctl.fontsize = 10
Next
DoCmd.Close acForm, doc.Name, acSaveYes
On Error GoTo Err_Handler
Next

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

 

[Rose B]

This worked a treat!!!!! THANKS

Does this help?

Public Sub ChangeAllFonts()
Dim Db As DAO.Database, doc As Document, ctl As Control
Set Db = CurrentDb

On Error GoTo Err_Handler
For Each doc In Db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
For Each ctl In Forms(doc.Name)
On Error Resume Next
ctl.FontName = "Arial"
ctl.fontsize = 10
Next
DoCmd.Close acForm, doc.Name, acSaveYes
On Error GoTo Err_Handler
Next

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

 

[Rose B]

This also worked!!! THANKS

(These are good routines, John/Fred, that can be used to change control
attributes, depending upon whichever one is most approporiate).