• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

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

1 Solution
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, ...in 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
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now