Solved

Access 2013 Set Conditional format of multiple field in a form – use with a subform related to ID: 39845510

Posted on 2014-02-12
2
573 Views
Last Modified: 2014-02-12
I asked this question - Access 2013 Set Conditional format of multiple field in a form – use with a subform related to ID: 39845510 and got a great solution that works with my form.

As a related question, I'd like to enter the function into a module and make it available to any for or subform

Help with that would be appreciated.


Code from the 1st question response was :

Private Sub Form_Load()

For Each ctl In Me.Controls

      If ctl.ControlType = acTextBox Then

            ctl.OnGotFocus = "=ChangeColor('" & me.name & "', '" & ctl.Name & "'," & " True)"
            ctl.OnLostFocus = "=ChangeColor('" & me.name & "', '" & ctl.Name & "'," & " false)"

      End If
 Next

 End Sub
 
with this in a module

Function ChangeColor(frmName As String, ctlname As String, booFocus As Boolean)

    If booFocus = True Then
   
       Forms(frmName).Controls(ctlname).BackColor = vbGreen
             
    Else
   
        Forms(frmName).Controls(ctlname).BackColor = vbWhite
       
    End If
End Function
0
Comment
Question by:rogerdjr
2 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
You need to create a standard module for the code.  it can't be in the class module of a form.

Public Sub SetColor(frm as Form)
For Each ctl In Me.Controls

      If ctl.ControlType = acTextBox Then

            ctl.OnGotFocus = "=ChangeColor('" & frm.name & "', '" & ctl.Name & "'," & " True)"
            ctl.OnLostFocus = "=ChangeColor('" & frm.name & "', '" & ctl.Name & "'," & " false)"

      End If
 Next
End Sub

Open in new window

Then the load event becomes
Private Sub Form_Load()
    Call SetColor(Me)
End Sub

Open in new window


All I did was to change the "Me" reference to "frm".  You have to send the form reference into the sub but once you do, you can access anything with frm. that you could with me.
0
 

Author Closing Comment

by:rogerdjr
Comment Utility
Perfect - thnks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now