Programmatically changing control properties 1

[TrekBiker]

I've inherited a database in which the fonts for text boxes, command buttons, etc are very hard to read, including shadows and border colours that are unsightly.

Can I change all control properties in all forms in one go using code? I want simple Calibri 10, flat, no borders.

Thanks

 

[Andrzejek]

You may get some ideas about faq702-5010 from this link.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[TrekBiker]

Thanks for the suggestion. As an example I've added a command button to one of the forms to run this.

Dim ctl As Control

For Each ctl In Forms.Item("Orders by Customer")
    If ctl.ControlType = acLabel Then
        ctl.FontName = "Calibri"
        ctl.FontSize = 10
    End If
   
Next ctl

This does the job but on saving and reopening the old font reappears.

Any thoughts?

 

[dhookom]

You can only save the changes if the form is opened in design mode. You must:
- open the form in design mode
- make the changes
- save and close the form

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Alternatively, you could use the code in the Form_Open event of each Form:

Private Sub Form_Open(Cancel As Integer)
 
Dim ctl As Control

For Each ctl In Me.Controls

    If ctl.ControlType = acLabel Then
        ctl.FontName = "Calibri"
        ctl.FontSize = 10
    End If
   
Next ctl

End Sub

Note that replacing

For Each ctl In Forms.Item("Orders by Customer")

with

For Each ctl In Me.Controls

keeps you from changing the code for each Form...making it apply to the current Form.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[TrekBiker]

Thanks Duane and Missinglinq

Either way works fine but seems to mean going though each form separately. Is it possible to change properties of all forms in a single operation?

 

[dhookom]

You could write code that loops through all forms and opens each in design mode. Then loop through the controls to make changes and save/close the form.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Here's something I cobbled together years and years ago, that does what Duane is talking about...didn't post it originally because I had to schlep thru the almost 4400 archived files I have looking for it!

Create a new, temporary Form, put a Command Button on it, and use this code in the OnClick event of the button:

  Dim rst As DAO.Recordset
   
    Dim ctl As Control
    
    Set rst = CurrentDb.OpenRecordset("SELECT [Name] FROM MSysObjects WHERE [Type] = -32768")
   
    With rst
      
      Do While Not .EOF
         
         DoCmd.OpenForm !Name, acDesign, , , , acHidden
         
         For Each ctl In Forms.Item(!Name)

           If ctl.ControlType = acLabel Then
             ctl.FontName = "Calibri"
             ctl.FontSize = 10
             ctl.ForeColor = vbGreen
           End If

         Next ctl

          DoCmd.Close acForm, !Name, acSaveYes
         
         .MoveNext
       
      Loop
      
        .Close
   
   End With

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[missinglinq]

Hate to leave anything to chance...

[ol A]
[li]Open the Form[/li]
[li]Click on the Command Button[/li]
[li]Copy my last Post[/li]
[li]Pass it on, if you find someone who needs it![/li]
[/ol]

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[missinglinq]

Also, I see I left the line

ctl.ForeColor = vbGreen

in there...you can delete it, of course...I just used it to make it easier when testing the code...small changes in Font Sizes are hard to detect, at my advanced age!

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

 

[TrekBiker]

Absolutely what I wanted, thanks very much! I can add in other control types and do the full job so much quicker than manually changing styles.

Very kind of you to hunt through your archives, I bet there's a lot of good stuff in the other 4399.

 

[missinglinq]

Glad we could help!

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007