Refer to the CONTENTS of a Public Variable in code.

Buck_Beasom used Ask the Experts™
I want to refer to the CONTENTS of a declared public variable in a function. The idea is this:

Public strPrimarySecID As String

Public Function Change_Text_Control(a_strControl As String, a_Variable As String)
    If Me.Controls(a_strControl).Value <> Me.a_Variable.Value Then
        Me.Controls(a_strControl).BackColor = RGB(240, 255, 41)
        Me.Controls(a_strControl).BackColor = RGB(255, 255, 255)
    End If

End Function

I want to use the same function for ALL controls on the form, or at least the ones that contain strings. (I figure a separate function each for Strings, Numbers and Booleans.)

The "Me.Controls" form allows the specific control to be referenced as a variable. I want the same thing for the Public Variable I have declared, so I can compare the value in the control to the value in the variable and - if it is different - change the control backcolor. Is there some similar syntax for the VARIABLE that will allow me to compare the CONTENTS?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Designer
All I had to do was pass the Variable Value in the Function rather than the NAME of the variable and the function worked fine.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

I strongly recommend against using public variables, these variables will lose their values if your code encounters an unhandled error.

Instead, I would encourage you to:
1.  Use Tempvars.  This is a collection which will not lose its values if you encounter an unhandled and these can be referred to in queries:

tempvars!ClientID = 3
SELECT * FROM yourTable where [ClientID] = [tempvars]![ClientID]

2.  use a function.  This function would accept an optional value to set the value of lngClientID, and then returns the value of lngClientID as the function return value
Public Function fnClientID(Optional varValue as Variant = NULL) as long

    Static lngClientID as long

    if not isnull(varValue) then lngClientID = varValue
    fnClientID = lngClientID

End Function

Open in new window

SELECT * from yourTable where [ClientID] = fnClientID()

3.  Use a form (usually hidden) to store these values and refer to that form in your query

SELECT * from yourTable WHERE [ClientID] = [Forms]![frmVariables]!txtClientID
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010


You need to close out your question if your 2nd post is the solution to your original question.

You should be able to find an option to close my question or accept this solution (your 2nd post).


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial