Re: How do you store a single constant in Access?

If the value really is a constant, then you can hard code it in your code.

However, you mentioned that users *might* need to change this So, then, so
save values from a user, it makes sense to build a table to hold those
values. So, you can make a one record table to store those values.

Not only does hits solve the problem of a USER being able to change the
value, but, when you close the database, the values are saved! (kind of what
a database is for!!!).

To use the values through out the application, you have two general choices

1) in your start-up code, open up a global reocrdset
or
2) open a up a form, and make it invisible.

With idea #1 above, you will have to write a function to return the value in
question (you need to do this, since you can use variables, or values from a
table directly in your quires - so, you build a function to do this).


eg:

startup code:
set gblrstDefaults = currentdb.OpenRecordSet("tblDefaults")

The above thus opens a reocrdset. the variable gblrstDefaults would be a
global variable (one defined as public in a standard code module).

Now, in that same code module, we can place a public function like:

Public Function GetMyDefault(strField As String) As Variant

GetMyDefault = gblrstDefaults(strField)

End Function


Now, in forms, or sql quires, you can return that value like

GetMyDefault("Defaultcity")

You could also dispense with having to "pass" the name of the field you want
if you only have ONE value to store

eg:

Public Function GetDefaultCity as string

GetDefaultCity = gblrstDefaults!City

end function

It is your choice..if you only have a few fields/values that you save, then
you can write a new function for each new field in this one record table,
but my first example forces you to pass the field name, but the tradeoffs is
that for each default value, you don't have to write a new function.

Default city would be the default for a city. We had to write that little
function since sql query does not allow us to reference variables directly
(but, a query or form can reference a public function directly).

Option #2 is to open a form that is attached to that one record table. Then,
you can use anywhere

forms!MyDefaultForm!DefaultCity

So, you can use that forms expression anywhere you want.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


"Jack ***" <mind-the-gap@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uhxS4EU4GHA.2144@xxxxxxxxxxxxxxxxxxxxxxx
Currently I have a select query that prompts for a value when I run the
query. It is a single value that does not vary with any records. How
would I go about storing that constant separately within the database, for
the query to interrogate when run so that the query does not prompt for
it? Periodically the user would want to overwrite that value, which would
then be stored in the same place. I would also want to be able separately
to interrogate the value of that constant via a query.

Thanks

--
Return email address is not as DEEP as it appears



.

Quantcast