Storing calculated fields

[NES]

I simply can't seem to be able to enter two values into a form (and thus,
into a table) and then have their product shown in another field and ALSO
stored in that table. Having the calculation result appear in the form is
not a problem. The problem is the field control cannot be assigned to the
table AND be the result of a calculation it seems.

It may seem redundant to store this result, but it's necessary in this case.
Any suggestions you may have would be appreciated. THis is very important to
the cause.

 

[ruralguy via AccessMonster.com]

The solution to your problem is to use code in the AfterUpdate event of your
two values to assign the results to the control that is bound to your 3rd
field.

Me.ThirdControl = Me.FirstControl * Me.SecondControl

You may want to test for valid values in the two controls first.

 

[tedmi]

... The problem is the field control cannot be assigned to the
table AND be the result of a calculation it seems.

That's not a problem, it's Access' way of nudging you toward good DB design.
You can always simulate the presence of the calculated field by using a query
instead of a table for whatever downstream purpose. So instead of a table
with fields A, B and AtimesB, you have only the fields A and B, and use a
query like so:
SELECT A, B, A*B AS AtimesB FROM...
In the event you need the product for some purpose (say as an index or PK),
then store only A and product in the table, and use a query like:
SELECT A, AtimesB/A as B, AtimesB
But if absolutely positively insist on redundantly storing all three values,
you can bind the Product control of the form to a table field and use VBA to
set its value, using the AfterUpdate events of text boxes holding values A
and B.

 

[NES]

Thank you very much for confirming what I suspected. <s> Actually, I
mis-stated the situation. The table is storing medications and the unit price
of the same, but not the TotalCost.

Initially, as these are entered as new items, the Quantity and UnitPrice are
stored in the table, which is the result of TotalCost divided by Quantity.
This is done via a special form, used only for new medications.

Thereafter, as inventory is relieved by dispensing, the transaction is
stored in a separate update table in QuantityDispensed and UnitCost fields.
Additional info is stored in a separate table for patient/medication
tracking.

When new medications arrive and are entered, they are entered into a
separate update table of similar structure where the "quantity to add" and a
now "recalculated" UniteCost is to be temporarily stored.

Once a day, or more frequently if desired, a macro will run two update
queries, one will will relieve inventory of all medications dispensed, and
the other will post the increase in inventory and the new unit cost. Then the
two update tables are cleared of data and ready to receive additional updates.

I use a query for the inventory addition because the person entereing it
must verify the NDC# (National Drug Code), the source of which is the Master
Drug table, and the updated info is to be stored in the update table until
the update query is run.

It seems to be the most efficient way of doing this. But I may have to
reconsider.

If all of this seems confusing, I apologize. Drug tracking and patient
information involves different problems that must be handled together, and
I'm doing the best that I can.

Thanks again for your information. I can probably do something now that you
confirm the situation.

 

[NES]

Thank you very much for your reply. I'm trying to avoid the use of code where
I can. I'm not that familiar with VBA. I have use the AfterUpdate property
in a form property in the past to change the update date field. I'll take a
look at that. Thanks again

 

[ruralguy via AccessMonster.com]

Hi Norm,
There are pleanty of posters here that can assist with the code you are
looking for. Feel free to post back if you need further assistance on this
issue.

Thank you very much for your reply. I'm trying to avoid the use of code where
I can. I'm not that familiar with VBA. I have use the AfterUpdate property
in a form property in the past to change the update date field. I'll take a
look at that. Thanks again

The solution to your problem is to use code in the AfterUpdate event of your
two values to assign the results to the control that is bound to your 3rd

[quoted text clipped - 13 lines]

 

[Dariusz Ryngwelski]

ok.

Thank you very much for confirming what I suspected. <s> Actually, I
mis-stated the situation. The table is storing medications and the unit
price
of the same, but not the TotalCost.

Initially, as these are entered as new items, the Quantity and UnitPrice
are
stored in the table, which is the result of TotalCost divided by Quantity.
This is done via a special form, used only for new medications.

Thereafter, as inventory is relieved by dispensing, the transaction is
stored in a separate update table in QuantityDispensed and UnitCost
fields.
Additional info is stored in a separate table for patient/medication
tracking.

When new medications arrive and are entered, they are entered into a
separate update table of similar structure where the "quantity to add" and
a
now "recalculated" UniteCost is to be temporarily stored.

Once a day, or more frequently if desired, a macro will run two update
queries, one will will relieve inventory of all medications dispensed, and
the other will post the increase in inventory and the new unit cost. Then
the
two update tables are cleared of data and ready to receive additional
updates.

I use a query for the inventory addition because the person entereing it
must verify the NDC# (National Drug Code), the source of which is the
Master
Drug table, and the updated info is to be stored in the update table until
the update query is run.

It seems to be the most efficient way of doing this. But I may have to
reconsider.

If all of this seems confusing, I apologize. Drug tracking and patient
information involves different problems that must be handled together, and
I'm doing the best that I can.

Thanks again for your information. I can probably do something now that
you
confirm the situation.

 

[Robert]

If that doesn't work you might try using a recordset.