Calling Active Screen Module on a Subform

[steve1111]

Hello,

I have a module created that uses the screen.activeform property to look up a series of integers to color each form to a user's custom preferences (i.e Header backcolor, text, buttons, etc.)

My module works great when a form is a parent form, but I get an error that the form needs to be active when the form has a subform. From what I understand, the subform is not actually the form but a control and the subforms load up before the rest of the main form.

So to make this work currently I have to put the entire code of the module in the on load event of the subform replacing screen.active to me.form. I have tried adjusting the module to me.form rather than screen.active, but that gives some addition complications.

My question is where or how do i prompt the subform to run the module so that I can keep from pasting the entire code in each subform?

Thank you

 

[Ranman256]

The subForm (or any form) can run modules if they are public.
The 'module' isn't public, the routines in them are.

public sub mySub()
  Code
End sub

The code in the Form 'module' can only be run by the Form. (Subform or not)
But any form can call public functions/subs.

 

[steve1111]

The function is a public function, and when I Call ColorForms and the form is opened as a form itself it works fine. The issues is when I use that form in a subform, the function is no longer working properly.

 

[Ranman256]

if the form is now a subform, it could be a reference problem.
Id need to see the code.

 

[steve1111]

Here is the full code, works fine whenever the form is not being used as a sourceobject for a subform. I am trying to find out where to Call ColorForms so that the subform can reference this code.

Public Function ColorForms()

Dim I As Integer
Dim ctl As Control
Dim ctrl As Control
Dim o As Object
Dim frm As Form

Dim HeaderBC As Long
Dim HeaderFC As Long
Dim HeaderBtnBC As Long
Dim HeaderBtnFC As Long

Dim DetailBC As Long
Dim DetailFC As Long
Dim DetailBtnBC As Long
Dim DetailBtnFC As Long

'-----------------------------------------------------------------------------------

HeaderBC = DLookup("HeaderBackcolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[UserName] = '" & fOSUserName() & "'"))
HeaderFC = DLookup("HeaderFontForecolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
HeaderBtnBC = DLookup("HeaderButtonBackcolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
HeaderBtnFC = DLookup("HeaderButtonForecolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))

DetailBC = DLookup("DetailBackcolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
DetailFC = DLookup("DetailFontForecolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
DetailBtnBC = DLookup("DetailButtonBackcolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
DetailBtnFC = DLookup("DetailButtonForecolor", "systUserColorSettings", "[UserID] = " & DLookup("ID", "systUsers", "[Username] = '" & fOSUserName() & "'"))
'-----------------------------------------------------------------------------------
For Each ctrl In screen.ActiveForm
With screen.ActiveForm
    .Section(1).BackColor = HeaderBC
    .Section(2).BackColor = HeaderBC
    .Section(0).BackColor = DetailBC
End With
Next

Dim MyForm As Form
Set MyForm = screen.ActiveForm

For Each ctl In MyForm.FormHeader.Controls
        Select Case ctl.ControlType
                Case acLabel
                    ctl.ForeColor = HeaderFC
                Case acTextBox
                    ctl.ForeColor = HeaderFC
                Case acComboBox
                    ctl.ForeColor = HeaderFC
                Case acListBox
                    ctl.ForeColor = HeaderFC
                Case acCommandButton
                    ctl.BackColor = HeaderBtnBC
                    ctl.ForeColor = HeaderBtnFC
                    ctl.BorderStyle = 0
                    ctl.HoverColor = RGB(255, 255, 153)
                    ctl.HoverForeColor = vbBlack
                Case acTabCtl
                    ctl.BackColor = HeaderBC
                    ctl.ForeColor = HeaderFC
                Case acRectangle
                    ctl.BackColor = HeaderBC
        End Select
Next

For Each ctl In MyForm.FormFooter.Controls
        Select Case ctl.ControlType
                Case acLabel
                    ctl.ForeColor = HeaderFC
                Case acTextBox
                    ctl.ForeColor = HeaderFC
                Case acComboBox
                    ctl.ForeColor = HeaderFC
                Case acListBox
                    ctl.ForeColor = HeaderFC
                Case acCommandButton
                    ctl.BackColor = HeaderBtnBC
                    ctl.ForeColor = HeaderBtnFC
                    ctl.BorderStyle = 0
                    ctl.HoverColor = RGB(255, 255, 153)
                    ctl.HoverForeColor = vbBlack
                Case acTabCtl
                    ctl.BackColor = HeaderBC
                    ctl.ForeColor = HeaderFC
                Case acRectangle
                    ctl.BackColor = HeaderBC
        End Select
Next

For Each ctl In MyForm.Detail.Controls
    Select Case ctl.ControlType
        Case acLabel
            ctl.ForeColor = DetailFC
        Case acTextBox
            ctl.ForeColor = DetailFC
        Case acComboBox
            ctl.ForeColor = DetailFC
        Case acListBox
            ctl.ForeColor = DetailFC
        Case acCommandButton
            ctl.BackColor = DetailBtnBC
            ctl.ForeColor = DetailBtnFC
            ctl.BorderStyle = 0
            ctl.HoverColor = RGB(255, 255, 153)
            ctl.HoverForeColor = vbBlack
        Case acTabCtl
            ctl.BackColor = DetailBC
            ctl.ForeColor = DetailFC
        Case acRectangle
            ctl.BackColor = HeaderBC
        End Select
Next

End Function

 

[Uncle Gizmo]

In this YouTube video:-

https://youtu.be/zsHMWNyTQnY

I explain the general principle of how you can modify multiple Forms with the each forms onload event and utilising generic code in a module. To apply this in your particular case, I reckon you should change this line:-

Set MyForm = screen.ActiveForm

Passing in the form from the load event as an object.

 

[steve1111]

Thank you Uncle Gizmo, this is fantastic and exactly what I was looking for. Has applications elsewhere in my db to clean up my excess code!

 

[Uncle Gizmo]

Hi Steve,
Glad it worked! You could do me a big favour and subscribe to my YouTube channel, that would be an excellent way of saying thank you!

Subscribe to Uncle Gizmo's Youtube Channel and get notified when new MS Access (Mostly) videos are Added!

 

[steve1111]

Yes absolutely, it looks like you have a ton of other very useful and cool things available!