Public variable named from field content

[rwfreeman]

I've declared 20 Public variables in the Declaration section of a
General module:
Public gCF1 as Double, gAFa as Double, etc.

These variables will contain values that are used in subsequent
calculations in the project and they represent default conditions.
However the user could elect to override one or all of them
temporarally and perform a non-default calculation. So my thought is
to assign values from the default table to public variables that could
be altered if the user chooses to do so.

Values for the variables are stored in a table that is queried to
create a recordset ("rst") that has a structure like so
rst.Fields(2) rst.Fields(3)
CF1 0.000001
AFa 0.2
so that the name of the variable is in .Fields(2) and its numeric
value is in .Fields(3). Thus, gCF1 should have a value of 0.000001
and gAFa should have a value of 0.2.

Looping through the recordset and using a SELECT CASE
rst.Fields(2).Value statement works but seems to me to be rather
inelegant and inflexible. If, instead of the SELECT CASE, I use a
statement such as
"g" & rst.Fields(2).Value = rst.Fields(3).Value
an compile error results ("Expected identifier or line number.")

FoxBase had a "macro substitution" (&VariableName=VariableValue) which
worked perfectly for this. What's the VBA equivalent?

Thanks for the help

 

[Dale Fye]

I'm not aware of anyway to do what you are trying to accomplish. You could
store the variable name and value in a collection, or you could store these
values in a control on a form. I occassionally store public variables in
text boxes on my form. I extend the length or width of a form, and put
textboxes (set the TabStop to False, and scrollbars set to none) in that
extended area. With the TabStop set to False and the scrollbars turned off,
the user should never get to see these textboxes, but they provide you a way
to see what is in them as you are developing.

Then you could name them txt_GCF1, txt_gAFa, ...
and could reference them as follows:

Dim rs as DAO.Recordset
Set rs = currentd.openrecordset("Select * FROM yourTable")

While not rs.eof

me.Controls("txt_" & rs(2)).Value = rs(3)
rs.movenext

Wend
rs.close
set rs = nothing

HTH
Dale

 

[rwfreeman]

I'm not aware of anyway to do what you are trying to accomplish.  You could
store the variable name and value in a collection, or you could store these
values in a control on a form.  I occassionally store public variables in
text boxes on my form.  I extend the length or width of a form, and put
textboxes (set the TabStop to False, and scrollbars set to none) in that
extended area.  With the TabStop set to False and the scrollbars turned off,
the user should never get to see these textboxes, but they provide you a way
to see what is in them as you are developing.

Then you could name them txt_GCF1, txt_gAFa, ...
and could reference them as follows:

   Dim rs as DAO.Recordset
   Set rs = currentd.openrecordset("Select * FROM yourTable")

   While not rs.eof

       me.Controls("txt_" & rs(2)).Value = rs(3)
       rs.movenext

    Wend
    rs.close
    set rs = nothing

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.










- Show quoted text -

Dale, thanks. I believe I'll stick with my SELECT CASE ... END CASE
construct.

Richard

 

[Dale Fye]

Richard,

The two down sides of using public variables are:
1. some errors will cause those variables to lose their values.
2. you cannot use public variables in queries, but you can values that are
stored in form controls.

Whenever I want to create global variables, one of my favorite techniques is
to add the textboxes to my Splash form. This form never gets closed (only
hidden) until the application closes, so the variables are always available,
and there is no chance they will get dropped after an unhandled error.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.