change all form properties with code

  • Thread starter Me.Frustrated = True
  • Start date

M

Me.Frustrated = True

Hello everyone.
I have about 60 forms in my database, many with custom colors for the
Details/Header/Footer sections and the forms' controls. I tried to write a
sub to open each form in design view, change the colors back to the defaults,
so I wouldn't have to fix each form manually. But for some reason I can't
get this code to work:

------------------------------
Public Sub ReColor()
On Error GoTo err_code
Dim obj As AccessObject
Dim dbs As Object
Dim ctl As Control
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
Debug.Print obj.Form.Section(0).BackColor
With obj
.Form.Section(0).BackColor = -2147483633
.Form.Section(1).BackColor = -2147483633
.Form.Section(2) = -2147483633
For Each ctl In obj.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
ctl.BackColor = -2147483643
ctl.ForeColor = -2147483640
End If
Next ctl
End With
DoCmd.Save acForm, obj
DoCmd.Close
Next obj

exit_here:
Exit Sub

err_code:
MsgBox (Err.Number & ": " & Err.Description)
Resume exit_here

End Sub
 
Ad

Advertisements

M

Me.Frustrated = True

Sorry... forgot to mention the error and lines that produce it:
.Form.Section(0).BackColor = -2147483633
.Form.Section(1).BackColor = -2147483633
.Form.Section(2) = -2147483633
each of those lines give the error 438 "Object does not support this
property or method"
 
D

Dirk Goldgar

Me.Frustrated = True said:
Hello everyone.
I have about 60 forms in my database, many with custom colors for the
Details/Header/Footer sections and the forms' controls. I tried to write
a
sub to open each form in design view, change the colors back to the
defaults,
so I wouldn't have to fix each form manually. But for some reason I can't
get this code to work:

------------------------------
Public Sub ReColor()
On Error GoTo err_code
Dim obj As AccessObject
Dim dbs As Object
Dim ctl As Control
Set dbs = Application.CurrentProject

For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign
Debug.Print obj.Form.Section(0).BackColor
With obj
.Form.Section(0).BackColor = -2147483633
.Form.Section(1).BackColor = -2147483633
.Form.Section(2) = -2147483633
For Each ctl In obj.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
ctl.BackColor = -2147483643
ctl.ForeColor = -2147483640
End If
Next ctl
End With
DoCmd.Save acForm, obj
DoCmd.Close
Next obj

exit_here:
Exit Sub

err_code:
MsgBox (Err.Number & ": " & Err.Description)
Resume exit_here

End Sub
------------------------------

I want it to open each form, reset the colors/properties, save the form
and
close it. Is there something I'm missing, or do I have it completely
wrong?


Only slightly wrong. The form object that you open in design view to make
these changes isn't the same as the AccessObject object that is a member of
CurrentProject.AllForms. Try this revised version of your code:

'------ start of revised code ------
Public Sub ReColor()

On Error GoTo err_code
Dim obj As AccessObject
Dim ctl As Control

For Each obj In CurrentProject.AllForms

DoCmd.OpenForm obj.Name, acDesign

With Forms(obj.Name)
Debug.Print .Section(0).BackColor
.Section(0).BackColor = -2147483633
.Section(1).BackColor = -2147483633
.Section(2).BackColor = -2147483633
For Each ctl In .Controls
If ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox _
Then
ctl.BackColor = -2147483643
ctl.ForeColor = -2147483640
End If
Next ctl
End With

DoCmd.Close acForm, obj.Name, acSaveYes

Next obj

exit_here:
Exit Sub

err_code:
If Err.Number = 2462 Then ' form section doesn't exist
Resume Next
End If
MsgBox (Err.Number & ": " & Err.Description)
Resume exit_here

End Sub
'------ end of revised code ------
 
M

Me.Frustrated = True

Wow, thanks so much Dirk for the quick response. It worked perfectly, and I
learned something valuable today.
Funny thing is, I set out to do this to save myself some time. I most
likely ended up spending more time trying to figure out the code than I would
have if I'd just fixed the forms manually. But, I'm glad for the experience
nonetheless.
Thanks again!
 
D

Dirk Goldgar

Me.Frustrated = True said:
Wow, thanks so much Dirk for the quick response. It worked perfectly, and
I
learned something valuable today.
Funny thing is, I set out to do this to save myself some time. I most
likely ended up spending more time trying to figure out the code than I
would
have if I'd just fixed the forms manually. But, I'm glad for the
experience
nonetheless.

I always find the knowledge gained in these exercises saves me time in the
long run, even if not in the current project.
Thanks again!

You're very welcome.
 
M

Massimo Vogliotti

I'm very interested to this sub, but i have a question:
how to call it ?

Best regards
Massimo
 
Ad

Advertisements

C

croy

I'm very interested to this sub, but i have a question:
how to call it ?

Best regards
Massimo

What message are you replying to? I don't see another
message in this group with the title of "Only slightly
wrong."
 
Ad

Advertisements

D

David-W-Fenton

I'm very interested to this sub, but i have a question:
how to call it ?

It's a public sub called Recolor, so you'd call it in code or in the
Immediate Window. In the latter case, type the sub name and hit
enter, which will run it. I'd edit the code to add a Debug.Print
obj.Name & " saved with new colors."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top