Help with assigning variable names

Hi
I am trying to use a table to store some constants. The table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in code and
assign them into variables using the values in the first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad

I'd rename all of your fields: AFAIK, all of those are reserved words, and
you can get into all sorts of grief if you use reserved words.

Why do you want to store them in variables? You can do a lookup whenever you
need the value...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ad" wrote in message ...
Hi
I am trying to use a table to store some constants. The table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in code and
assign them into variables using the values in the first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad

Good point on the field names. I need to rename the fields.

As to why holding the values in variables, my intention was to load these
constants into global variables when the program starts so they become
available globally. I thought this would make the programming much easier
(just refer to the variables rather than lookingup from table each time)
and the program running faster.

These constants are not changed very frequently but used a lot in the process.
A typical example is the labour hourly rate. It might get changed a couple of
times during a year. It is however heavily used in the process to for
calculating labour costs.

ad

"Douglas J. Steele" wrote in message ...
I'd rename all of your fields: AFAIK, all of those are reserved words, and
you can get into all sorts of grief if you use reserved words.

Why do you want to store them in variables? You can do a lookup whenever you
need the value...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"ad" wrote in message ...
Hi
I am trying to use a table to store some constants. The table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in code and
assign them into variables using the values in the first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad

I do use a number of (my) "application" constants store in
a Table tblCustomData. The are not strictly constants as
I do change them occasionally. However, I only use only 2
Fields CDName and CDValue both of Text type. If
necessary, I convert them to whatever type I need in code.

Note that I don't retrieve them on start-up and store them
in VBA variables as VBA Variables will be reset if an
untrapped error occur. Hence, I only retrieve them when I
need (using DLookUp or Recordset). For those frequently
used "constants", I retrieve them and store them in
unbound Controls on the "StartUp" Form which remains open
but hidden after the normal start-up. Since the "Startup"
Form remains open until the database is close, I can
retrieve these values from its Controls. The advantage is
that the values in these Controls are not reset in case of
an untrapped error.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi
I am trying to use a table to store some constants. The
table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store
value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store
value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in
code and
assign them into variables using the values in the
first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad


.

Thanks Van. I am glad to hear that I am not the only one who
uses a hidden form to store constants. That's exactly what
I have been doing. Only recently I came up with this idea
of using global variables. Imaging referring to a constant
by "cstLabourRate" instead of "Forms!constants!LabourRate",
not mentioning lookuping the table each time.

I am not sure how serious the untrapped error issue is.
Could you please elaborate?

ad

"Van T. Dinh" wrote in message ...
I do use a number of (my) "application" constants store in
a Table tblCustomData. The are not strictly constants as
I do change them occasionally. However, I only use only 2
Fields CDName and CDValue both of Text type. If
necessary, I convert them to whatever type I need in code.

Note that I don't retrieve them on start-up and store them
in VBA variables as VBA Variables will be reset if an
untrapped error occur. Hence, I only retrieve them when I
need (using DLookUp or Recordset). For those frequently
used "constants", I retrieve them and store them in
unbound Controls on the "StartUp" Form which remains open
but hidden after the normal start-up. Since the "Startup"
Form remains open until the database is close, I can
retrieve these values from its Controls. The advantage is
that the values in these Controls are not reset in case of
an untrapped error.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi
I am trying to use a table to store some constants. The
table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store
value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store
value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in
code and
assign them into variables using the values in the
first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad


.

"ad" wrote in message

Hi
I am trying to use a table to store some constants. The table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in code and
assign them into variables using the values in the first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad

I take yet a different approach from Doug and Van. For what I think of
as "application profile" values that I expect to need frequently -- and
especially if I'm going to want to pull them into a query -- I'll create
them as public properties, by making Property Get procedures for them in
a standard module. The module will also define the private variables in
which the values are stored and from which the Property Get procedures
return them. Each Property Get procedure checks a flag to see if the
values have yet been loaded into the private variables from the table
where they are stored, and calls a routine to load them if they haven't.
That way, I don't have to worry if the values have been reset, because
if so, the flag will have been reset and the values will be reloaded.
But once loaded, the values will normally remain in memory for the
lifetime of the application instance, so I avoid the overhead of
repeated lookups.

One nice thing about doing it this way is that I can refer to the
properties by name in a query, which I can't do with an ordinary public
variable.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk, could you elaborate on that a bit? I have a 'setup table' that the
user can edit to store his preferences and options. I found that if I read
the table at startup and stored the table contents in variables, they could
disappear. So I coded to read the table again before each variable was
used. It works fine, but is, of course, inefficient. Your method sounds
good to me, but the VBA documentation on transferring values from a table to
public class variables is a bit dense. Can you help me get started here?

UpRider

"Dirk Goldgar" wrote in message
...
"ad" wrote in message

Hi
I am trying to use a table to store some constants. The table
contains the following fields:

name - name of the constant
type - type of data
int - if it is integer this field is used to store value
double - if it is double this field is used to store value
yesno - if it is Boolean this field is used to store value
text - if it is text this field is used to store value

Now I have two questions:

1. Is this appropriate? What do you guys normally do?

2. I would like to read the values of the constants in code and
assign them into variables using the values in the first
field (name) as the variable name. How do I define such
variables with dynamic names from the table?

Your help would be appreciated

ad

I take yet a different approach from Doug and Van. For what I think of
as "application profile" values that I expect to need frequently -- and
especially if I'm going to want to pull them into a query -- I'll create
them as public properties, by making Property Get procedures for them in
a standard module. The module will also define the private variables in
which the values are stored and from which the Property Get procedures
return them. Each Property Get procedure checks a flag to see if the
values have yet been loaded into the private variables from the table
where they are stored, and calls a routine to load them if they haven't.
That way, I don't have to worry if the values have been reset, because
if so, the flag will have been reset and the values will be reloaded.
But once loaded, the values will normally remain in memory for the
lifetime of the application instance, so I avoid the overhead of
repeated lookups.

One nice thing about doing it this way is that I can refer to the
properties by name in a query, which I can't do with an ordinary public
variable.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

My experience is that if an untrapped error occurs, VBA variables are rest
to their default values, i.e. Long / Integer / Byte ... variables to 0,
String Variables to empty String "", Variant Variables to Null. This is
pretty serious if you expect the correct values to be available for the
whole Access session. To avoid untrapped errors occurring, you will need to
use error-trapping code in *every* procedure in your database!

See the post from UpRider in this thread. He obviously experienced this
problem.

Regarding the long reference to the Control rather than simply a Variable
Name, I always think that if I write correct code *once*, it will stay there
so it is not a problem.

Actually, I prefer to use a UDF "fnGetMyValue()" to retrieve the value from
the Control rather than just reference to the Control. This way, I can
check whether the hidden Form is still there or not (and if required,
re-open the Form for the user to enter required info.) before I retrieve the
value from the Control. Also, I can use the UDF directly on the Queries /
SQL and let JET resolve the reference for me.

--
HTH
Van T. Dinh
MVP (Access)


"ad" wrote in message ...
Thanks Van. I am glad to hear that I am not the only one who
uses a hidden form to store constants. That's exactly what
I have been doing. Only recently I came up with this idea
of using global variables. Imaging referring to a constant
by "cstLabourRate" instead of "Forms!constants!LabourRate",
not mentioning lookuping the table each time.

I am not sure how serious the untrapped error issue is.
Could you please elaborate?

ad

"UpRider" wrote in message

Dirk, could you elaborate on that a bit? I have a 'setup table' that
the user can edit to store his preferences and options. I found that
if I read the table at startup and stored the table contents in
variables, they could disappear. So I coded to read the table again
before each variable was used. It works fine, but is, of course,
inefficient. Your method sounds good to me, but the VBA documentation
on transferring values from a table to public class variables is a
bit dense. Can you help me get started here?

Okay. Here's the code I have in the basProfile module -- a standard
module -- in a small application. It happens to be an application that
manages a retail catalog and updates a database on the web:

'---------- start of module code -------------
Option Compare Database
Option Explicit

' Copyright © 2002, Dirk Goldgar
' Limited license granted: You may use this posted code freely, but not
' claim it as your own or sell it except as part of a larger
application.

Dim mfProfileLoaded As Boolean
Dim mstrClientName As String
Dim mstrLocalPictureFolder As String
Dim mstrWebPictureFolder As String
Dim mstrQuoteFolder As String
Dim mstrNoPictureFile As String
Dim mintRecentItemDays As Integer
Dim mstrWebDatabaseFolder As String
Dim mstrWebUpdateURL As String


Property Get ClientName() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

ClientName = mstrClientName

End Property

Property Get LocalPictureFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

LocalPictureFolder = mstrLocalPictureFolder

End Property

Property Get QuoteFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

QuoteFolder = mstrQuoteFolder

End Property


Public Function OpenProfileForm()

DoCmd.OpenForm "frmProfile"

End Function

Property Get WebPictureFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebPictureFolder = mstrWebPictureFolder

End Property

Property Get WebDatabaseFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebDatabaseFolder = mstrWebDatabaseFolder

End Property

Property Get WebUpdateURL() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebUpdateURL = mstrWebUpdateURL

End Property


Property Get NoPictureFile() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

NoPictureFile = mstrNoPictureFile

End Property

Property Get RecentItemDays() As Integer

If mfProfileLoaded = False Then
LoadProfileData
End If

RecentItemDays = mintRecentItemDays

End Property

Public Sub LoadProfileData()

On Error GoTo Err_LoadProfileData

Dim rs As DAO.Recordset
Dim strFormat As String
Dim intStart As Integer
Dim intEnd As Integer

Set rs = CurrentDb.OpenRecordset("Profile")

With rs
mstrClientName = !ClientName & vbNullString
mstrLocalPictureFolder = !LocalPictureFolder & vbNullString
mstrWebPictureFolder = !WebPictureFolder & vbNullString
mstrNoPictureFile = !NoPictureFile & vbNullString
mintRecentItemDays = Nz(!RecentItemDays, 0)
mstrQuoteFolder = !QuoteFolder & vbNullString
mstrWebDatabaseFolder = !WebDatabaseFolder & vbNullString
mstrWebUpdateURL = !WebUpdateURL & vbNullString
.Close
End With

If Len(mstrLocalPictureFolder) = 0 Then
mstrLocalPictureFolder = CurrentProject.Path
End If
If Left(mstrLocalPictureFolder, 2) = ".\" Then
mstrLocalPictureFolder = _
CurrentProject.Path & Mid(mstrLocalPictureFolder, 2)
End If

If Len(mstrQuoteFolder) = 0 Then
mstrQuoteFolder = CurrentProject.Path
Else

' Process date-format specifications in the form "[fmtspec]".
Do
intStart = InStr(1, mstrQuoteFolder, "[", vbBinaryCompare)
If intStart 0 Then
intEnd = InStr(intStart, mstrQuoteFolder, "]",
vbBinaryCompare)
If intEnd intStart Then
strFormat = Mid$(mstrQuoteFolder, intStart + 1,
intEnd - (intStart + 1))
mstrQuoteFolder = _
Left$(mstrQuoteFolder, intStart - 1) & _
Format(Date, strFormat) & _
Mid$(mstrQuoteFolder, intEnd + 1)
End If
End If
Loop Until intStart = 0

' Insert application folder path if required.
If Left(mstrQuoteFolder, 2) = ".\" Then
mstrQuoteFolder = _
CurrentProject.Path & Mid(mstrQuoteFolder, 2)
End If

End If

mfProfileLoaded = True

Exit_LoadProfileData:
Set rs = Nothing
Exit Sub

Err_LoadProfileData:
MsgBox "ERROR - Unable to load profile data. " & _
"Either the database is damaged, or the Profile table " & _
"hasn't been filled in yet." & vbCr & vbCr & _
"The actual error was " & Err.Number & ": " &
Err.Description, _
vbExclamation, "Error Loading Profile"

Resume Exit_LoadProfileData

End Sub
'---------- end of module code -------------

The application also contains a form for maintaining the data in the
Profile table. In that form, there is a simple AfterUpdate event
procedure to ensure that the profile variables are reloaded when any
record is updated:

Private Sub Form_AfterUpdate()
LoadProfileData
End Sub

Does that clarify everything?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you, DIrk. At a quick inspection it looks exactly like what I need.
I'll study it and certainly learn something useful for me here!

UpRider

"Dirk Goldgar" wrote in message
...
"UpRider" wrote in message

Dirk, could you elaborate on that a bit? I have a 'setup table' that
the user can edit to store his preferences and options. I found that
if I read the table at startup and stored the table contents in
variables, they could disappear. So I coded to read the table again
before each variable was used. It works fine, but is, of course,
inefficient. Your method sounds good to me, but the VBA documentation
on transferring values from a table to public class variables is a
bit dense. Can you help me get started here?

Okay. Here's the code I have in the basProfile module -- a standard
module -- in a small application. It happens to be an application that
manages a retail catalog and updates a database on the web:

'---------- start of module code -------------
Option Compare Database
Option Explicit

' Copyright © 2002, Dirk Goldgar
' Limited license granted: You may use this posted code freely, but not
' claim it as your own or sell it except as part of a larger
application.

Dim mfProfileLoaded As Boolean
Dim mstrClientName As String
Dim mstrLocalPictureFolder As String
Dim mstrWebPictureFolder As String
Dim mstrQuoteFolder As String
Dim mstrNoPictureFile As String
Dim mintRecentItemDays As Integer
Dim mstrWebDatabaseFolder As String
Dim mstrWebUpdateURL As String


Property Get ClientName() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

ClientName = mstrClientName

End Property

Property Get LocalPictureFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

LocalPictureFolder = mstrLocalPictureFolder

End Property

Property Get QuoteFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

QuoteFolder = mstrQuoteFolder

End Property


Public Function OpenProfileForm()

DoCmd.OpenForm "frmProfile"

End Function

Property Get WebPictureFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebPictureFolder = mstrWebPictureFolder

End Property

Property Get WebDatabaseFolder() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebDatabaseFolder = mstrWebDatabaseFolder

End Property

Property Get WebUpdateURL() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

WebUpdateURL = mstrWebUpdateURL

End Property


Property Get NoPictureFile() As String

If mfProfileLoaded = False Then
LoadProfileData
End If

NoPictureFile = mstrNoPictureFile

End Property

Property Get RecentItemDays() As Integer

If mfProfileLoaded = False Then
LoadProfileData
End If

RecentItemDays = mintRecentItemDays

End Property

Public Sub LoadProfileData()

On Error GoTo Err_LoadProfileData

Dim rs As DAO.Recordset
Dim strFormat As String
Dim intStart As Integer
Dim intEnd As Integer

Set rs = CurrentDb.OpenRecordset("Profile")

With rs
mstrClientName = !ClientName & vbNullString
mstrLocalPictureFolder = !LocalPictureFolder & vbNullString
mstrWebPictureFolder = !WebPictureFolder & vbNullString
mstrNoPictureFile = !NoPictureFile & vbNullString
mintRecentItemDays = Nz(!RecentItemDays, 0)
mstrQuoteFolder = !QuoteFolder & vbNullString
mstrWebDatabaseFolder = !WebDatabaseFolder & vbNullString
mstrWebUpdateURL = !WebUpdateURL & vbNullString
.Close
End With

If Len(mstrLocalPictureFolder) = 0 Then
mstrLocalPictureFolder = CurrentProject.Path
End If
If Left(mstrLocalPictureFolder, 2) = ".\" Then
mstrLocalPictureFolder = _
CurrentProject.Path & Mid(mstrLocalPictureFolder, 2)
End If

If Len(mstrQuoteFolder) = 0 Then
mstrQuoteFolder = CurrentProject.Path
Else

' Process date-format specifications in the form "[fmtspec]".
Do
intStart = InStr(1, mstrQuoteFolder, "[", vbBinaryCompare)
If intStart 0 Then
intEnd = InStr(intStart, mstrQuoteFolder, "]",
vbBinaryCompare)
If intEnd intStart Then
strFormat = Mid$(mstrQuoteFolder, intStart + 1,
intEnd - (intStart + 1))
mstrQuoteFolder = _
Left$(mstrQuoteFolder, intStart - 1) & _
Format(Date, strFormat) & _
Mid$(mstrQuoteFolder, intEnd + 1)
End If
End If
Loop Until intStart = 0

' Insert application folder path if required.
If Left(mstrQuoteFolder, 2) = ".\" Then
mstrQuoteFolder = _
CurrentProject.Path & Mid(mstrQuoteFolder, 2)
End If

End If

mfProfileLoaded = True

Exit_LoadProfileData:
Set rs = Nothing
Exit Sub

Err_LoadProfileData:
MsgBox "ERROR - Unable to load profile data. " & _
"Either the database is damaged, or the Profile table " & _
"hasn't been filled in yet." & vbCr & vbCr & _
"The actual error was " & Err.Number & ": " &
Err.Description, _
vbExclamation, "Error Loading Profile"

Resume Exit_LoadProfileData

End Sub
'---------- end of module code -------------

The application also contains a form for maintaining the data in the
Profile table. In that form, there is a simple AfterUpdate event
procedure to ensure that the profile variables are reloaded when any
record is updated:

Private Sub Form_AfterUpdate()
LoadProfileData
End Sub

Does that clarify everything?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)