How do you store a single constant in Access?

[Jack Sheet]

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

 

[Albert D. Kallal]

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.

 

[Jack Sheet]

That is stirling stuff, thanks Albert. I felt a bit embarrassed asking the
question, but feel justified now!

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.

 

[Guest]

A way to do it that does not involve creating a table would be to create a
database property and use that.

Here is how you do that:
set MyPrp = currentdb.CreateProperty("GetLunch",dbtext,"GoNow")
currentdb.Properties.Append myprp
set myprop = nothing

Now, if you query the propery
x = currentdb.Properties("GetLunch")
x will = "GoNow"

If you later need to change it:

currentdb.Properties("getlunch") = "MaybeLater"

Now it will return "MaybeLater"

The last value assigned to the property will be presistent. That is, next
time you open the database, it will return whatever the value was when the
database was closed last.

Now, to use it in a query, all you need is a function call to return the
value.

Public Function DoWhat(strX As String) As Boolean

If Currentdb.Properties("GetLunch") = "GoNow" Then
DoWhat = True
Else
DoWhat = False
End If
End Function

Now, the trick is that for a query to execute for every record, it has to be
passed a value, whether in uses it or not. It doesn't have to be a field
name.

IIf(DoWhat(""), "Left", "Right")

will work just fine.

 

[Jack Sheet]

Thanks Klatuu

For the moment I am going to have a go at doing it by creating a table and
tying a form to it, but I am tucking this away for future reference also.

 

[Jack Sheet]

Albert, I decided (for now) to go down the route of creating a one-record
table to store the constant, and tying a form to it.
At present it doesn't work for me, so I would like help knowing where I have
gone wrong.

I created a table T_RollDate that contains just one field RollDate with just
one record.
I created a form F_RollDate that shows that field. I disabled the form from
adding new records.
I am not sure that I want to make the form invisible as suggested, as I
could use this form to enable the user to update this variable.

To test it out I created a little update query Q_RollDate

UPDATE T_Clients
SET T_Clients.RollDateStage1 =
DateSerial(Year(forms!F_RollDate!RollDate),[YEM],[YED]);

Using F_RollDate I entered 31/05/2008 as the "constant" in T_RollDate

I then ran Q_RollDate, expecting that all dates in T_Clients.RollDateStage1
would be some date in 2008, the day and month determined by other fields YEM
and YED in T_Clients

However, when I ran the update query it still prompted me for a parameter
value for forms!F_RollDate!RollDate, instead of (as expected) picking it up
from the table.

Any ideas? Thanks

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.

 

[Jack Sheet]

Albert
I have now tried option 1 solution, and still hit a snag. I have to say
that I prefer method 2 (using a form) and my other post re. that problem
takes precedence, for me. Even so, here is my problem with method 1.

I have a standard module that reads ...
Option Compare Database
Public MyRollDate As Variant 'possibly should be Long
Private Sub Autoexec()
Set MyRollDate = CurrentDb.OpenRecordset("T_RollDate")
End Sub
Public Function FnRollDate(RollDate As Long) As Variant 'possibly should be
Long
FnRollDate = MyRollDate(RollDate)
End Function

I have a query
UPDATE T_Clients
SET T_Clients.RollDateStage1 =
DateSerial(Year(FnRollDate("RollDate")),[YEM],[YED]);

If I run the query I get "Data type mismatch in criteria expression".

Help!!?? Thanks

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.

 

[Jack G]

Klatuu,

That looks like a great way to do it that I never knew existed. Wish this
post had been around a few months ago!

Jack

 

[Jack Sheet]

Problem seems to be solved, (for now!) I used DLookup to interrogate the
value of the constant. The form seemed to be a red herring. But doubtless
I misunderstood.

Albert, I decided (for now) to go down the route of creating a one-record
table to store the constant, and tying a form to it.
At present it doesn't work for me, so I would like help knowing where I
have gone wrong.

I created a table T_RollDate that contains just one field RollDate with
just one record.
I created a form F_RollDate that shows that field. I disabled the form
from adding new records.
I am not sure that I want to make the form invisible as suggested, as I
could use this form to enable the user to update this variable.

To test it out I created a little update query Q_RollDate

UPDATE T_Clients
SET T_Clients.RollDateStage1 =
DateSerial(Year(forms!F_RollDate!RollDate),[YEM],[YED]);

Using F_RollDate I entered 31/05/2008 as the "constant" in T_RollDate

I then ran Q_RollDate, expecting that all dates in
T_Clients.RollDateStage1 would be some date in 2008, the day and month
determined by other fields YEM and YED in T_Clients

However, when I ran the update query it still prompted me for a parameter
value for forms!F_RollDate!RollDate, instead of (as expected) picking it
up from the table.

Any ideas? Thanks

 

[onedaywhen]

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!!!).

A one-row table of 'constants' is a standard trick in SQL. A word of
warning: take steps to ensure it doesn't contain more than one row!
e.g.

CREATE TABLE Constants (
lock CHAR(1) DEFAULT 'x' NOT NULL
PRIMARY KEY,
CONSTRAINT constants__max_one_row
CHECK (lock = 'x'),
....<<other columns with initial vales specified as DEFAULT>>
)
;
INSERT INTO Constants (lock) VALUES ('x')
;
ALTER TABLE Constants ADD
CONSTRAINT constants__exactly_one_row
CHECK (1 = (
SELECT COUNT(*)
FROM Constants AS T2
))
;

Jamie.

--