Refer to the CONTENTS of a Public Variable in code.

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?

Buck_BeasomDatabase DesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Buck_BeasomDatabase DesignerAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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, Developing Solutions LLCCommented:

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).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.