Changing Public Variable in Access

Hi Experts,

I have a public variable declared in MS Access that I use in several queries to export the data to CSV files.  And it works fine with the initial declaration, but I was trying to see if there was a way to change the value of the public variable so I  could run the same set of queries with the new value.

For example I would declare the public variable as
Public Function GBL_StoreCode()
  GBL_StoreCode = "ABC"
End Function

Then run the queries with that function and all is good.  But then I want to change the value of GBL_StoreCode to "XYZ" and rerun the queries with that value. But I get a runtime error 424.

Do you have any suggestions on what I can try?

Thanks in advance

Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
That is the tricky bit with public variables...

They can be tricky to maintain.
There really should only be one place to set/reset the variable, ...else things can get confusing and out of hand.

In the most basic sense, create a form with a textbox  where you can type in this value and set it, ..., or clear it (set it to:  "")
Then do something like this on a button on the same form:

If isnull(me.txtGlobalVariable) then
end if

Then always use this form to set/clear the variable.

But lets see what other experts post, case I misunderstood something...

Create your public variable:

public glbPublicVariable as string '... or long, single, etc.

Now, create a function to "get" this public variable:

function Get_glbPublicVariable() as string
Get_glbPublicVariable = glbPublicVariable
end function

Use the =Get_glbPublicVariable()  to retrieve this value.

Now, all you have to do is provide glbPublicVariabe a value - anywhere in your app.

glbPublicVariable = "ABC"

Your app will work.

Scott C

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
424 is the "Object Required" error, so I'm curious what your Public Variable is intended to do. Generally you get these errors when you have failed to open/initialize an Object, or something of that nature.

So how are you using that Public Variable, and what type of variable (or Object) is it?
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 Applications

From novice to tech pro — start learning today.