saving user-defined variables

[grades]

Hi,
I have a database that creates many forms based on queries which are based on user-defined variables. These are permanent variables added directly in the query, such as "if an employee is late 4 times in a month, we yell at them." I would like to be able to change this to "5 times in a month" if the users want to.
Rather than waiting for that to happen and having to change all my queries, i want to set up a form for them to access and edit the variable, save it, and then my queries to reference that variable.
My question is what is the best way to store and reference them? I could create a table, but i am not too sure how easy it would be to make my queries work with that.
I could also add them onto my main form which is always open, but I'm not sure how to make them save when access is closed. (a quick trial from my variable form won't let me update either a text box or label value on the main form)
Any ideas?

 

[pbaldy]

Best is a relative term. If you want to save settings through Access closing, options include a table and registry values. I'd use a table myself. A local table if they're user specific, a central table if they all use the same values.

 

[Galaxiom]

I wouldn't use a local table but have a back end one with settings for each user. This will persist beyond a front end update and provide a consistent user experience at multiple locations.

 

[Lightwave]

Grades similar to Galaxiom. I sometimes use a side end for this kind of thing this is a separate back end database stored on the local computer with tables linked to front that has fields for the required variables. This means that each individual can have their own persistent parameters and like Galaxiom says these persist beyond front end update.

You could probably do this with some kind of predicate linked to user id in the existing back end so that when a user pulls up the settings form only their variables are shown. I personally have never tried this.

 

[grades]

Thanks for your replies.
I would use a front end table for this one since everything is front end and the database is accessed from the network (this may change in future) and if these variables are ever going to change, then they should change for everyone at once.
So i fi use a table, could someone give me a quick example of the table structure and how to properly reference the appropriate table cell as a variable in a query?
Thanks.

 

[pbaldy]

I'm concerned when you say "everything is front end". Is the database split? It almost certainly should be.

In any case, you can use a one-record table with fields for whatever values you need stored. There are any number of ways to access them. The simplest in a query is to include the table in the query without a join. Since it would only have one record, it wouldn't affect the results returned like it would otherwise.

SELECT...
FROM MainTable, ThisTable
WHERE MainTable.FieldName = ThisTable.FieldName

Note that if this table is in the front end and there's a lot of data in the back end, this will slow the query down. You can also create public functions that return specific values from the table:

SELECT...
FROM MainTable
WHERE MainTable.FieldName = FunctionName(DesiredValue)

which assumes the function accepts the input and selects the appropriate value.