Re: Calculated Value will not store in Table

From: tonyaims (anonymous_at_discussions.microsoft.com)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 13:50:38 -0700

Hi Albert,

Good progress. I had not thought about TotPrice. I agree
there is no need to save it. Thanks for pointing that out.

I have another design question that has to do with table
relationships. In my application, a PO can have many
items. I have tblPO, tblMaterialGrp, tblMaterialDesc,
tblPOItem. The indeces for these tables are autonumbered
so tblMaterialGrp has a primary index field MatGrpID that
is autonumbered and a field called MatGrp that holds the
text name of the group(eg: glass).

For each record in tblPO, there can be many records in
tblPOItem. Each record in tblPOItem will have the numbers
you have helped me with and the material group(eg: glass)
and the material description(eg: glass tempered, glass
thermal, etc.). Material group and description are drawn
from their respective tables.

tblPOItem, tblMaterialGrp, tblMaterialDesc are related by
MatGrpID and MatDescID(There are other relationships as
well).

I have a combo box in frmPOItem that lists MaterialGrp
names. The autonumbered ID for the selected name is
stored in tblPOItem.MaterialGrpID. I do not have a field
in tblPOItems that holds the actual text name of the
selected group.

Now if I run a form or a report that wants to list
material group names, will the names be listed or the
IDs? Do I have to have a field that holds the text name
in order for the text to show up in forms and reports or
is the ID adequate?

Thanks,

Tony

>-----Original Message-----
>"tonyaims" <anonymous@discussions.microsoft.com> wrote
in message
>news:0ef201c49969$34c1d2f0$a301280a@phx.gbl...
>
>> Thanks Albert.
>
>Your welcome...and we seem to be making progress!
>
>>
>> What does the "Option Explicit" statement say/do?
>
>Great question!. Option Explicit means that ALL
variables you use in code
>MUST be defined first.
>For me as a developer, that option is thus ALWAYS USED.
So, it means that
>the "compiler"
>will go through all your code, and ensure that you did
not miss-type
>something; For example
>
>strMsg = "hello, how are you today"
>
>msgbox strMgs
>
>If you look close at the above code, all it does would
display a message of:
>
> "Hello, how are you today"
>
>The problem is that I have a type-o in the above (on
purpose for this
>example). Note how I used:
>
> msgbox strMgs
>
>I misspelled strMgs....it should be:
> strMsg
>
>If we use option Explicit, then we MUST define all
variables we use, and any
>un-defined, or misspelled variable will be instantly
found for you by he
>computer.. Hence, most developers make the Option
Explicit feature the
>default. You should do this.
>
>Whack ctrl-g to bring up the developer environment. Then
go tools->options.
>On the "edit" tab, you want to make sure the 2nd option
is checked:
>
> Require Variable Declaration
>
>If you check the above, then ms-access will
automatically put in the Option
>Exploit into any code module. This option "used" to be
the default.
>
>Ok...lets get back to our code:
>>
>> Anyway, after adding it total price is calculated. I
have
>> had difficulty with discount. If I enter a 10 then the
>> discount is not properly calculated(i.e.: it is
treated as
>> 10 not .10) but if I enter .1 in the form, it is not
>> accepted. The .1 is there until I tab to the next
control
>> and then it becomes 0 so there is no discount applied.
So
>> I changed the line in MyTotCalc from
>
>My guess is that your discount field is NOT a currently
value. If any of
>your fields are integer (long), then no decimals are
allowed into the field.
>This likely is your problem, and means your table
defines for these fields
>needs to be checked. I would use currency in these
cases, or you could
>divide the value entered by 100 if you wish (as you did).
>
>>
>> curResult=curResult - (curResult*Nz(DiscPercent,0))
>>
>> to
>>
>> curResult=curResult - (curResult*Nz(DiscPercent, 0) /
100)
>>
>> and entered a whole number as the discount instead of a
>> decimal number. Now the TotPrice is calculated
correctly
>> but the discount is still not stored in the table.
>
>You mean field DiscPercent is not being saved..right?
You enter the
>DiscPercent, and that gets saved. You calculate the
TotPrice, and our code
>saves that. Is there actually another field involved
here? (because if there
>is..we need to set it...and I see no code at all here
that sets this
>field...if in fact there is another field here?).
>
>Hum...again, I would check the table definitions for
this field. I would
>suggest using currency type field.
>
>Also, if in fact there is another field, then we are
learning a good lesson
>here:
>
> Don't store calculate values.
>
>In fact, I should actually said that I will NOT show you
how to write this
>code, but have FORCED you to come up with a expression
that CALCULATES the
>result each time. The reason why this "calculating"
concept is important is:
>
> We NEVER have to worry about the values being
wrong...since we don't
>save them
> Why save values that you can calculate them (this is
one the first
>lessons in good database design).
> It is easy to accident save the wrong values, and in
fact, when we
>finally do get our code working, then we don't have to
go and check EXISTING
>DATA!!! Note that as we enter and test our code, the old
data, or other data
>entered will NOT be fixed when we finally fix our code.
>
>As you can see, it is often somewhat easier to write a
small piece of code
>as we did, but at the end of the day, we should have
tried to come up with a
>design that DOES NOT store these values. We don't need
to store them,and can
>calculate them any time we need. Further, now you have
to start going back
>into your system to ensure our data is correct, *after*
we get our code
>working. If we had come up with a solution that just
calculates the results,
>then when we get the code correct...we are done! (as it
is now, you have to
>go back and check the data since our code is not 100%
correct!).
>
>Anyway, we shall just have to say that the team of
developers and system
>analysts made a design decision here. We shall assume
that we found it
>somewhat easier to store these values. However, some
members of our design
>team did suggest that we don't store the values, and
that good time honored
>sound advice about not storing calculated values was
taken with fair
>consideration! ;-)
>
>Good luck!!
>
>--
>Albert D. Kallal (Access MVP)
>Edmonton, Alberta Canada
>pleaseNOOSpamKallal@msn.com
>http://www.attcanada.net/~kallal.msn
>
>
>.
>

Relevant Pages

  • Re: Calculated Value will not store in Table
    ... If we use option Explicit, then we MUST define all variables we use, and any ... > had difficulty with discount. ... design that DOES NOT store these values. ...
    (microsoft.public.access.forms)
  • Re: Department store self-checkouts run on XP (and they crash)
    ... Here's yet another example of a very poor embedded design that somehow ... out the door. ... So I was at the local department store a few days ago and while using the ... self-checkout, the application completely quit and I was left staring at ...
    (comp.arch.embedded)
  • Re: Batch Generation of Records
    ... This is one of those cases that if you CAN just store the information about ... your design will have a bunch of code now that has to figure ... appointments like anniversaries.and you only have to add ONE record. ... Also, before and coding gets done, any reason why something like outlook is ...
    (microsoft.public.access.formscoding)
  • Re: Concatenate 2 cells into 1, same record
    ... >See sql-script below for my current database (some Dutch terms). ... But this design looks as if it's just slapped together without paying too ... Your tables have no keys. ... Do you also want to store phone numbers ...
    (microsoft.public.sqlserver.programming)
  • Re: Hi Everybody!
    ... live...I have to drive 15 minutes to get to a grocery store, ... I found the local Wal*Mart and Lammy picked out two skeins of Red Heart ... designed and am almost done with a second sweater. ... and you can email me and tell me if you want my design notes. ...
    (rec.crafts.textiles.yarn)