Public Variable question

[garyholc]

I have a table in my database called T_Param which will contain defaults for the forms used within the database.

One form auto creates a folder location, but I want the user to be able to specify where the default location will be.

I created a public variable called location and a form which allows the user to change the location. The location is changed whenever the user changes it on the param form as it updates the public variable.

However, if the user just goes into the main form, it obviously does not find the default location, as the public variable has not been set.

Is there a way, whenever the database is opened, or when the form is opened, to tell the public variable to use whatever is stored in the T_Param table in the field called FName?

Hope that makes sense!!

 

[Banana]

Would using a constant be satisfactory?

Another alternative is to use a mini table to hold that information.

Or just set the public variable in startup form's load event.

 

[garyholc]

The user may want to change the default location....

The location is currently stored in a field called FName in a table called T_Param.

I made a form, which uses this table to show the user the current default location. If they change it and click the change button, it tells the public variable to become this default location.

My problem is that if they dont need to change the default location, when they open the database, the public variable (fileloc) is null.

Hope that makes more sense.

 

[Banana]

That's by design- public variables do not live between shutdown and startup of an Access application. You would have to use your startup form's Load event to set the public variable with the value from your table.

Did that help?

 

[boblarson]

If you want it populated when the database opens, you can do it in a couple of ways.

1. Create a public function in a standard module to populate the value via a DLookup to the table where it is stored and call the function from the AutoExec macro.

2. Put the same code as in #1 in the On Open or On Load event of the first form that opens.

Personally, because of the volativity of the global variable, I have gone to using a hidden, unbound form to store the information while the db is open because it can survive during times where the global variable may be reset (such as unhandled errors and all).

 

[garyholc]

Thanks Bob

yes i'm aware I would need to add some code to the On_Open event on the form to set the variable, but I am unsure of the code

I know it would need to be

FileLoc = <<the field in the table>>

But its the syntax for the <<field in the table>> bit I am stuck with to tell the FileLoc to use the value in that field in the table.

Thanks
Gary

 

[boblarson]

The code would be:

location = DLookup("[FileLoc]", "T_Param")

If you have more than one FileLoc listed then you would need to add criteria:

location = DLookup("[FileLoc]", "T_Param", "[SomeOtherField]=SomeCriteria")

 

[garyholc]

Arrrghh why didnt I think of the Dlookup! yeah that worked, many thanks

 

[boblarson]

Glad we could help.