Solved Access Forms (1 Viewer)

eerkut

New member
Local time
Today, 18:17
Joined
Apr 24, 2012
Messages
6
I trying to change all the forms header, footer, and detail background color in my access database with VBA code. Any idea would be helpful.
I m able to change the labels forecolor and backcolor with below code but no luck for header, detail and footer back color.
Dim frmObj As Access.Form
Dim frm As Object
Dim ctl As Control


For Each frm In CurrentProject.AllForms

If InStr(frm.name, "frm") And Not InStr(frm.name, "switchboard") Then

DoCmd.OpenForm frm.name, acDesign

Set frmObj = Forms(frm.name)


For Each ctl In frmObj.Controls
Select Case ctl.ControlType
Case acBoundObjectFrame
Case acCheckBox
Case acComboBox
Case acCommandButton

Case acImage
Case acLabel
ctl.ForeColor = 16777215
ctl.BackColor = 4145843
Case acLine
Case acListBox, acComboBox

Case acObjectFrame
Case acOptionButton
Case acOptionGroup
Case acPage
Case acPageBreak
Case acRectangle

Case acSubform
Case acDetail
ctl.BackColor = 14211288
Case acTabCtl

Case acToggleButton
End Select
Next

DoCmd.Close acForm, frm.name, acSaveYes
DoEvents
End If
Next
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:17
Joined
May 21, 2018
Messages
6,443
Just like you loop the control collection, loop the forms Sections collection. Change the property of the section
Form.Section property (Access) | Microsoft Docs

Actually since you know which sections you are going after, no need to loop. Just use the index.
 

eerkut

New member
Local time
Today, 18:17
Joined
Apr 24, 2012
Messages
6
Thank you. I found the above code on google. Not much a VBA knowledge here. If you can explain little bit more. Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:17
Joined
Oct 29, 2018
Messages
19,173
Thank you. I found the above code on google. Not much a VBA knowledge here. If you can explain little bit more. Thanks
Hi. Welcome to AWF!

Basically, you could try adding a line like this one:
Code:
frm.Section(acHeader).BackColor = vbGreen
Hope that helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:17
Joined
May 7, 2009
Messages
16,826
Code:
Dim frmObj As Access.Form
Dim frm As Object
Dim ctl As Control
Dim i As Integer

For Each frm In CurrentProject.AllForms

    If InStr(frm.Name, "frm") And Not InStr(frm.Name, "switchboard") Then
    
        DoCmd.OpenForm frm.Name, acDesign
        
        Set frmObj = Forms(frm.Name)
        
        On Error Resume Next
        For i = 0 To 8
            Select Case i
                Case AcSection.acDetail
                    frmObj.Section(i).BackColor = 14211288
                Case AcSection.acFooter
                Case AcSection.acGroupLevel1Footer
                Case AcSection.acGroupLevel1Header
                Case AcSection.acGroupLevel2Footer
                Case AcSection.acGroupLevel2Header
                Case AcSection.acHeader
                Case AcSection.acPageFooter
                Case AcSection.acPageHeader
            End Select
            For Each ctl In frmObj.Section(i).Controls
                Select Case ctl.ControlType
                    Case acBoundObjectFrame
                    Case acCheckBox
                    Case acComboBox
                    Case acCommandButton
                    
                    Case acImage
                    Case acLabel
                        ctl.ForeColor = 16777215
                        ctl.BackColor = 4145843
                    Case acLine
                    Case acListBox, acComboBox
                    
                    Case acObjectFrame
                    Case acOptionButton
                    Case acOptionGroup
                    Case acPage
                    Case acPageBreak
                    Case acRectangle
                    
                    Case acSubform
                    Case acTabCtl
                    
                    Case acToggleButton
                End Select
            Next
        Next
        DoCmd.Close acForm, frm.Name, acSaveYes
        DoEvents
    End If
Next
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:17
Joined
Feb 28, 2001
Messages
23,166
Just to put this in English:

Remember that forms have sections just like reports have sections. You usually get to everything on the form because by default you do what you do to the form's detail section. If you have never put in a header or footer on the form, there is nothing to do or see. HOWEVER, if you HAVE put in a header or footer, their controls MUST be referenced by qualifying the control name's location, as for example: Me.sectioname.controlname

ArnelGP's example refers to sections based on their section index. A similar method would work on reports since they ALSO have sections.
 

eerkut

New member
Local time
Today, 18:17
Joined
Apr 24, 2012
Messages
6
ARNELGP's code worked. Thank you to all.



"Never stop learning, because life never stops teaching"
 

Users who are viewing this thread

Top Bottom