Calculated Value will not store in Table

[tonyaims]

I have a PO with multiple items. Table design is a PO
table and a POItems table. POItems contains UnitPrice,
Units, Discount and TotPrice(plus other fields not
relevant to the problem).

In the POEntry form, there is a PO form with a POItems
subform. In the POItems subform there are Text Boxes for
Unit Price, NumUnits, Discount and TotPrice.

In the AfterUpdate event for UnitPrice I would like to
set TotPrice to Unit Price. In the AfterUpdate event for
NumUnits I would like to set TotPrice to
UnitPrice*NumUnits. In the AfterUpdate event for Discount
I would like to set TotPrice to (UnitPrice*NumUnits-
UnitPrice*NumUnits*Discount).

Any suggestions?

Thanks,

Tony

 

[Albert D. Kallal]

I would just create a small sub in the forms module code.

Since all 3 controls have to update the TotPrice, then I would make a common
routine that gets called in the after update event of all 3 controls.

So, for the after update event for UnitPrice, I would go:

Call MyTotCalc

And, for the after update even for NumUnits, I would go:

Call MyTotCalc

And, do the same for Discount.

Now, your MyTotCalc routine would be:


Private Sub MyTotCalc

dim curResult as currency

curResult = ( nz(UnitPrice,0) * nz(NumUnits,0) )
curResult = curResult - (curResult * nz(Discount,0)
me.TotPrice = curResult

end sub

 

[tonyaims]

Albert,

Thanks for the suggestions. I need a little more help as
I am very new to Access.

In the main window for my application, I selected Modules
and created the MyTotCalc subroutine. I put:
Call MyTotCalc
in the AfterUpdate event for the controls as you
suggested. For some reason the
Call MyTotCalc
shows up after an End Sub in the code for a different
control in the form.
If I insert
Call MyTotCalc
in the code that access puts in the control, I get a d -
For instance, the UnitPrice control has the following
code:
Private Sub UnitPrice_AfterUpdate()
Call MyTotCalc
End Sub
Then I get a compile error:
Expected variable or procedure, not module
So I either created the subroutine in the wrong place or
inserted the Call MyTotCalc in the wrong place or both.

Any further suggestions?

Thanks again,

Tony

 

[Albert D. Kallal]

Ok...new to this.

The first question (or answer) we ask is where should the code go?

Since we are going to write some code for each control, and obviously that
code goes with the actual form's module code.

And, since each event is calling this code, then might as well put the code
in the forms module also (it belongs to that one form..and so that is a good
spot for it).

So, assuming we are starting from scratch, here is how we would go:

We would open the form with the controls in design mode. If this is a
sub-form, then lets open the form used for this sub form (no use trying to
peek through another form here...as that is a bit hard). So, lets just open
up the form with the controls on it in design mode.

I would highlight the UnitPrice control. I would then make sure the
properties sheet is displayed for this control. I would then click on the
properties sheet "event" tab. I would then click on the after update event.
You then see the [...] (a button with 3 dots). You click on this button, and
are given 3 choices:

Expression Builder
Macor Builder
Code Builder

You would select Code Builder

Private Sub UnitPrice_AfterUpdate()

Call MyTotCalc

End Sub

Of course, the only part in the above that you had to type was the "Call
MyTotCalc", as the result is already put in for you.

Repeat the above process for all 3 controls.

Now, we want to put in the code, and we have decided that the code will also
go with the above stuff. So, at this point you can generally just hit the
"code" buttion on the tool bar (we are back looking at the form in desing
mode now). Or, simply go view->code. This will jump you back into the same
area that the above code is. In fact, you can even see that other code!.
Now, at the begining of the forms module, you would type in your code:


Option Compare Database
Option Explicit


Private Sub MyTotCalc()

Dim curResult As Currency

curResult = (Nz(UnitPrice, 0) * Nz(NumUnits, 0))
curResult = curResult - (curResult * Nz(Discount, 0))
Me!TotPrice = curResult

End Sub


The first part of Option Compare Database, and Option Explict likey will be
already there. If not, put in the Option Explict.

However, note how in my orignal post, I had:

curResult = curResult - (curResult * Nz(Discount, 0)

The above is IN-CORRECT, as it is missing a ")", you need

curResult = curResult - (curResult * Nz(Discount, 0))


The above should get you going. Don't forget to delete,and remove your other
"old" code and stuff..

 

[tonyaims]

Thanks Albert.

What does the "Option Explicit" statement say/do?

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(ie: 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

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. For
some reason, discount is being stored in the table
POItems as 000.00% instead of 10.

Any ideas?

Ugh! It is 4:10AM. I need to get some sleep...

Thanks again!

Tony

-----Original Message-----
Ok...new to this.

The first question (or answer) we ask is where should the code go?

Since we are going to write some code for each control, and obviously that
code goes with the actual form's module code.

And, since each event is calling this code, then might as well put the code
in the forms module also (it belongs to that one form..and so that is a good
spot for it).

So, assuming we are starting from scratch, here is how we would go:

We would open the form with the controls in design mode. If this is a
sub-form, then lets open the form used for this sub form (no use trying to
peek through another form here...as that is a bit hard). So, lets just open
up the form with the controls on it in design mode.

I would highlight the UnitPrice control. I would then make sure the
properties sheet is displayed for this control. I would then click on the
properties sheet "event" tab. I would then click on the after update event.
You then see the [...] (a button with 3 dots). You click on this button, and
are given 3 choices:

Expression Builder
Macor Builder
Code Builder

You would select Code Builder

Private Sub UnitPrice_AfterUpdate()

Call MyTotCalc

End Sub

Of course, the only part in the above that you had to type was the "Call
MyTotCalc", as the result is already put in for you.

Repeat the above process for all 3 controls.

Now, we want to put in the code, and we have decided that the code will also
go with the above stuff. So, at this point you can generally just hit the
"code" buttion on the tool bar (we are back looking at the form in desing
mode now). Or, simply go view->code. This will jump you back into the same
area that the above code is. In fact, you can even see that other code!.
Now, at the begining of the forms module, you would type in your code:


Option Compare Database
Option Explicit


Private Sub MyTotCalc()

Dim curResult As Currency

curResult = (Nz(UnitPrice, 0) * Nz(NumUnits, 0))
curResult = curResult - (curResult * Nz(Discount, 0))
Me!TotPrice = curResult

End Sub


The first part of Option Compare Database, and Option Explict likey will be
already there. If not, put in the Option Explict.

However, note how in my orignal post, I had:

curResult = curResult - (curResult * Nz(Discount, 0)

The above is IN-CORRECT, as it is missing a ")", you need

curResult = curResult - (curResult * Nz(Discount, 0))


The above should get you going. Don't forget to delete,and remove your other
"old" code and stuff..

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[Albert D. Kallal]

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

 

[tonyaims]

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-----

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.

Require Variable Declaration

If you check the above, then ms-access will

automatically put in the Option

 

[Albert D. Kallal]

Hi Albert,

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

Actually, the issue of saving is kind of a balance issue. if you don't save
it, then you don't have to worry about storing the wrong values! On the
other hand, for a simple report, you now must write some additional sql
statements to "sum", or "calculate" those values. So, while you save in one
area, you make a bit more work in he other!! So, there is a balancing act
here, and the answer is actually going to be based on the amount of
experience you have, and the particular project (sometimes, it is better, or
"easier" to store the values...but being "easier" for the developer does not
necessary make a better application..but you get the job done!!. And, as
mentioned, in some cases, storing the data is better, but if you can advoied
this...you should.

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?

The ID's will be listed (unless you are using the HORRIBLE and dreaded table
lookup feature..which you want to avoid).

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?

Well, as you can see, using a combo box is the recommend way to handle this.
In fact, if you use the wizard, it will *usually* store the id for you, but
actually display the text description field of your choice. So, for forms,
you can usually use the combo box wizard in this case.

However, for reports, then the best solution is to build a query. If you
have 2, or even 3 or even 4 combo lookups (that is where you store the ID,
but need some description, or "price" or whatever from the other table),
then you simply build a query and base the report on that way.

So, you drop in your main table into he query builder. And, for EACH of
those ID's that lookup into other tables, you simply then drop in the those
other tables (you can drop in as many other tables as you need into he query
builder). You then drop the join lines form the main table to those child
tables. It is important that you draw the join lines FROM the main table to
the child tables. Further, you have to use a left join here. You now are
free to drop in any field, or description field from those other tables
(often, you might have a few from those other tables). You then simply build
the report based on this query, which now has both the "ID" fields, and the
description fields.

So, for forms, I would simply using a combo box, and in fact, let the wizard
build the combo for you. For reports, you use the query builder, and simply
drop in those additional tables with the fields you need.

 

[tonyaims]

Albert,

Thanks for the suggestions. What is a "left" join?

Tony

 

[Albert D. Kallal]

Albert,

Thanks for the suggestions. What is a "left" join?

Tony

Ah...gee, you had to ask..huh!!!

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal
(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

Now, the concept of a left join is NOT very important for you combo box
lookups, except for the fact that it don't work unless you use left joins!!

However, for forms, and sub-forms, and related tables, left joins are quite
important. Keeping in mind that related tables, and your above problem are
different problems, there read the following as to why you want to be aware
of left joins:

--------------------------------------

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup).

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBookin records, my code also
assumes that a tblBGroup will also have to be added).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

 

[tonyaims]

Albert - Thanks a lot. Very helpful information.
I will be away on a business trip until the end of the
week so you get a break - at least from my questions.

Tony