Prob with code in declarations section of form module ...

#21

Because that is not what you do. You use a function to look the value up
from the table and reference the function any time you want to use that
value or you assign the value from the table to a variable.

If you think about this logically the postal rate changes, therefore it is
not a constant it is a variable, so you should not declare it as a const you
should declare it as either a variable or a function.

If you use a variable and the table then you need a procedure to read the
value from the table and assign it to the variable. If you use a function
then you can just read straight from the table each time.

General Discussion
===============
When you declare something as a const what you are saying is that the value
you assign to the const will never change. The purpose of consts is to make
your code more readable and to make it easier to update how your code
behaves in all the areas that the const is going to be used in one easy
change.

e.g. Say you want to define all the currency values in your application as
being to 2 decimal places you could do this.

Global Const CURR_DP$ = "0.00"

and then use it like this

Dim myVar

myVar = Format(SomeValue, CURR_DP)

You can now easily see that the value is going to be formatted to whatever
CURR_DP is defined as.

Say you then show the app to the customer and the customer says, "hang on a
minute all our currency values are to 4 decimal places", you can fix this
easily by changing the definition of CURR_DP i.e.

Global Const CURR_DP$ = "0.0000"

but, and this is important, you do not alter that value during normal
runtime. If you need to alter the value you use a variable or a function.

--
Terry Kreft

"MLH" <CRCI (AT) NorthState (DOT) net> wrote

Quote:

Of course, there is this problem...
Const MyConstant As Long = DLookup("[SomeLongField]", "SomeTable")

That ain't gonna fly in the Declarations section of a Form module.
I recall now why I hard coded the constant value assignments in
the module - because A97 cannot handle looking the values up.
It's a drawback associated with the ADT's perception of what
defines a constant. I think they should lighten up. Maybe they
have in more recent Access versions.

I know there are those who will argue that a Const assignment
isn't needed in the Form's Declarations section at all. But I think
it is appropriate - and as handy as a pocket on a shirt. The 42
cent value has been constant since May 12th and won't change
until this May. And it's darn sure constant during the minute or
so frmPostageCalculator is open. I think it's sad you cannot tell
Access... Hey, Access! I have a value that's gonna remain
constant for this entire form session. Use MyConstant as the
placeholder for the value and go look the value up in a table.

If MyConstant has a value of 1 in my database and 2 in your
database, then it's really not a constant, is it? I mean, it's not
Pi. It's not e and it's not Avagadro's number. Const IS is a
variable assignment. It's just a variable that, once assigned in
a session - cannot be reassigned. I can live with that. The ADT
pretty much forced programmers to hard code hard values in
code when assigning constants. I'm sorry, but nobody would
use something like Const MyPi = Pi - why would they? The
intrinsic constant already serves the purpose of substituting
a programmer-friendly word in the place of a value. Ya don't
need another one. But when ya do need one - ya gotta hard
code the value in the assignment statement.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx

On Sun, 12 Apr 2009 14:23:12 GMT, "tina" <nospam (AT) address (DOT) com> wrote:

if those values will change as of x date, they can change again farther in
the future. if you hard-code them in a module, it will take a programmer
to
change them each time that happens. i'd recommend putting the values into
a
table, so your customer/user has access to update them as the needed,
without requiring the services of you or another programmer. something
like

tblConstants
ConstID (pk)
ConstName

ConstID ConstName
1 SmallEnvWt
2 LargEnvWt
3 PaperWeight
4 FirstClassStamp
5 FirstClassOn9x12
6 OneOunceJumpRate
7 Certified3811
8 RetRcpt3800
9 NonMachinableSurcharge

tblConstantValues
cValID (pk)
ConstID (foreign key from tblConstants)
cValue
cDate

cValID ConstID cValue cDate
1 1 0.2059 1/1/09
2 2 0.625 1/1/09
3 3 0.1666667 1/1/09
4 4 0.42 1/1/09
5 5 0.83 1/1/09
6 6 0.17 1/1/09
7 7 2.7 1/1/09
8 8 2.2 1/1/09
9 9 0.2 1/1/09
10 4 0.44 5/11/09
11 5 0.88 5/11/09
12 6 0.17 5/11/09
13 7 2.8 5/11/09
14 8 2.3 5/11/09

the 1/1/09 date is a dummy date, of course; you'd have to change it to the
date of the last change, or to whatever effective date makes sense for the
use you're putting the data to.

putting the values into an updateable table means more work for you on the
programming side to ensure that the appropriate values are used for the
circumstances. but that one-time setup work will pay off in making the
database user-maintainable, rather than programmer-maintainable.

hth

"MLH" <CRCI (AT) NorthState (DOT) net> wrote in message
news:gbp3u4d4rrnegphi6ked367npvgjk2i1g3 (AT) 4ax (DOT) com...
Am trying to use the following in declarations section of form module.
Of course, it won't compile. What's the best way to set my constants
inside the If-Then. I need a work-a-round. Can I set Form module
constants outside the form module's uppermos declarations section?

Option Compare Database
Option Explicit

Const SmallEnvWt As Double = 0.2059
Const LargEnvWt As Double = 0.625
Const PaperWeight As Double = 0.1666667
If Now < #5/11/2009# Then
Const FirstClassStamp As Double = 0.42
Const FirstClassOn9x12 As Double = 0.83
Const OneOunceJumpRate As Double = 0.17
Const Certified3811 As Double = 2.7
Const RetRcpt3800 As Double = 2.2
Const NonMachinableSurcharge As Double = 0.2
Else
Const FirstClassStamp As Double = 0.44
Const FirstClassOn9x12 As Double = 0.88
Const OneOunceJumpRate As Double = 0.17
Const Certified3811 As Double = 2.8
Const RetRcpt3800 As Double = 2.3
Const NonMachinableSurcharge As Double = 0.2
End If

#22

"Terry Kreft" <dont (AT) spam (DOT) me.co.uk> wrote

Quote:

No, your structure is better. If I understand what Tony is suggesting then
it entails giving the field names "meaning" which is not a good idea.

I have used a control table before. e.g.

tControl
------------
Group
Item
Value
Decription

... where I've stored programming values which I need to be able to change
easily which works quite nicely. Essentially Group/Item are the key and
Value is the value I'm interested in, description contains a friendly
description for the user.

For the case in question you could easily extend this with.

tControl
------------
Group
Item

Off Topic.

I have a table with a field named "Item" and VB.Net express had a fit when I
tried to link to the table. Apparently "Item" is a key word.

#23

"Terry Kreft" <dont (AT) spam (DOT) me.co.uk> wrote:

Quote:

No, your structure is better. If I understand what Tony is suggesting then
it entails giving the field names "meaning" which is not a good idea.

But all field names have a meaning. Including description or date. As Tina points
out someone will have to change the code if new fields are added due to a pricing
system change. So I don't understand your objection.

Also in my opinion it's a bit more work to read 10 values in 10 records than 1 value
in one record.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#24

well, all field names mean something; i think you're referring to fieldnames
storing *data*. and i agree with that. but in nearly every database i build,
i have a tblMisc (miscellaneous) that holds only one record; i stick fields
in the table to hold various bits of unrelated data that i need to store -
usually acting as markers that are referenced in code - which have no
relationship with the data tables. basically the same as what Tony
described. and of course that's not normalized, but i don't lose sleep over
a spreadsheet-wize table (but don't tell John Vinson! <g>) that is designed
to never hold more than one record.

i went with the more normalized structure mainly because of the effective
dates of the values included in the op's initially posted code. the only way
i know to store historical data like that is in a normalized table with a
date field; it would take something pretty drastic to induce me to store
historical data spreadsheet-wize.

"Terry Kreft" <dont (AT) spam (DOT) me.co.uk> wrote

Quote:

No, your structure is better. If I understand what Tony is suggesting then
it entails giving the field names "meaning" which is not a good idea.

I have used a control table before. e.g.

tControl
------------
Group
Item
Value
Decription

... where I've stored programming values which I need to be able to change
easily which works quite nicely. Essentially Group/Item are the key and
Value is the value I'm interested in, description contains a friendly
description for the user.

For the case in question you could easily extend this with.

tControl
------------
Group
Item
EffectiveDate
Value
Decription
... where Group/Item/EffectiveDate are the key.

Then the data might look like this:-
Group Item EffectiveDate Value Description
===== ===== ========== ===== =========
Post FirstClassStamp 1/1/1990 0.42
Post FirstClassOn9x12 1/1/1990 0.83
...
Post FirstClassStamp 5/11/2009 0.44
Post FirstClassOn9x12 5/11/2009 0.88
...

This of course is horribly non-normalised but does make maintenance of
random values easier.

--
Terry Kreft

"tina" <nospam (AT) address (DOT) com> wrote in message
news:P0yEl.116657$4m1.90324 (AT) bgtnsc05-news (DOT) ops.worldnet.att.net...
i thought of that, Tony, and almost set it up that way. in some ways it
might be easier to be able to expand the "constant" list without
changing
table structure, but OTOH probably a programmer is going to have to
write
code to utilize any newly defined constants anyway - hmm, 6 of one... <g
and
shrug

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote in message
news:8ll4u4t62p4g1acpu59hud51r5hmajh0ko (AT) 4ax (DOT) com...
"tina" <nospam (AT) address (DOT) com> wrote:

if those values will change as of x date, they can change again
farther
in
the future.

I'm with Tina on this topic. Although if your list of constants will
stay
constant
then there's no reason why all the values couldn't reside in one record
with a data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#25

If you're happy then that's fine.

--
Terry Kreft

"tina" <nospam (AT) address (DOT) com> wrote

Quote:

well, all field names mean something; i think you're referring to
fieldnames
storing *data*. and i agree with that. but in nearly every database i
build,
i have a tblMisc (miscellaneous) that holds only one record; i stick
fields
in the table to hold various bits of unrelated data that i need to store -
usually acting as markers that are referenced in code - which have no
relationship with the data tables. basically the same as what Tony
described. and of course that's not normalized, but i don't lose sleep
over
a spreadsheet-wize table (but don't tell John Vinson! <g>) that is
designed
to never hold more than one record.

i went with the more normalized structure mainly because of the effective
dates of the values included in the op's initially posted code. the only
way
i know to store historical data like that is in a normalized table with a
date field; it would take something pretty drastic to induce me to store
historical data spreadsheet-wize.

"Terry Kreft" <dont (AT) spam (DOT) me.co.uk> wrote in message
news:Jd-dnT3C1YuqlH7UnZ2dnUVZ8jqdnZ2d (AT) eclipse (DOT) net.uk...
No, your structure is better. If I understand what Tony is suggesting
then
it entails giving the field names "meaning" which is not a good idea.

I have used a control table before. e.g.

tControl
------------
Group
Item
Value
Decription

... where I've stored programming values which I need to be able to
change
easily which works quite nicely. Essentially Group/Item are the key and
Value is the value I'm interested in, description contains a friendly
description for the user.

For the case in question you could easily extend this with.

tControl
------------
Group
Item
EffectiveDate
Value
Decription
... where Group/Item/EffectiveDate are the key.

Then the data might look like this:-
Group Item EffectiveDate Value
Description
===== ===== ========== ===== =========
Post FirstClassStamp 1/1/1990 0.42
Post FirstClassOn9x12 1/1/1990 0.83
...
Post FirstClassStamp 5/11/2009 0.44
Post FirstClassOn9x12 5/11/2009 0.88
...

This of course is horribly non-normalised but does make maintenance of
random values easier.

--
Terry Kreft

"tina" <nospam (AT) address (DOT) com> wrote in message
news:P0yEl.116657$4m1.90324 (AT) bgtnsc05-news (DOT) ops.worldnet.att.net...
i thought of that, Tony, and almost set it up that way. in some ways it
might be easier to be able to expand the "constant" list without
changing
table structure, but OTOH probably a programmer is going to have to
write
code to utilize any newly defined constants anyway - hmm, 6 of one...
g
and
shrug

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote in message
news:8ll4u4t62p4g1acpu59hud51r5hmajh0ko (AT) 4ax (DOT) com...
"tina" <nospam (AT) address (DOT) com> wrote:

if those values will change as of x date, they can change again
farther
in
the future.

I'm with Tina on this topic. Although if your list of constants will
stay
constant
then there's no reason why all the values couldn't reside in one
record
with a data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#26

If you're happy then that's fine.

--
Terry Kreft

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote

Quote:

"Terry Kreft" <dont (AT) spam (DOT) me.co.uk> wrote:

No, your structure is better. If I understand what Tony is suggesting then
it entails giving the field names "meaning" which is not a good idea.

But all field names have a meaning. Including description or date. As
Tina points
out someone will have to change the code if new fields are added due to a
pricing
system change. So I don't understand your objection.

Also in my opinion it's a bit more work to read 10 values in 10 records
than 1 value
in one record.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

#27

"tina" <nospam (AT) address (DOT) com> wrote:

Quote:

i thought of that, Tony, and almost set it up that way. in some ways it
might be easier to be able to expand the "constant" list without changing
table structure, but OTOH probably a programmer is going to have to write
code to utilize any newly defined constants anyway - hmm, 6 of one... <g and
shrug

To each their own.

<ego> But I'm still right. </ego> <chuckle>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Prob with code in declarations section of form module ...

comp.databases.ms-access comp.databases.ms-access

Re: Prob with code in declarations section of form module ... - 04-13-2009 , 09:22 AM

Re: Prob with code in declarations section of form module ... - 04-13-2009 , 01:42 PM

Re: Prob with code in declarations section of form module ... - 04-13-2009 , 03:10 PM

Re: Prob with code in declarations section of form module ... - 04-14-2009 , 02:23 AM

Re: Prob with code in declarations section of form module ... - 04-14-2009 , 02:50 PM

Re: Prob with code in declarations section of form module ... - 04-14-2009 , 02:51 PM

Re: Prob with code in declarations section of form module ... - 04-15-2009 , 05:08 PM