Calculated Form Fields do not fill data into related table fields

[rich1838]

I am creating a db for a retail business. I have multiple vendors from which
I buy the same product for resale. These vendors issue pricing either based
on net or varied discoumts from their list price. I created a table for the
products, a sub table for the vendors, and another sub table to caculate
pricing based on list / net costs. On the form, I'll take the vendors price
and use a Yes/No field to determine a calculation for my price, eg. Iif
([List /Net], [Vendor Price], [Vendor Price] - ([Vendor Price] * [Vendor
Discount])) . The calculations in the form field [My Price] work, however
this calculated number does not show up in the table field [My Price], nor
can I link this calculated number into other fields on other forms (PO's). I
have checked the one-to-many relationships and referential integrity /
cascade up and delete and nothing?

 

[Rob Parker]

First, it's generally bad practice to store calculated values in tables; you
should do the calculation when the value is required for display on a form,
or printing in a report. The exception to this rule is if the calculated
value should not change when the underlying data is changed, eg. invoice
data should not change with subsequent price changes; it should reflect the
value obtained from the price at the time it was issued. It seems that in
your current situation you should not be storing the calculated [My Price]
(although you might want/need to store data generated from this in your PO
forms).

Next, you can't use a control to calculate a value (in this case, it will
contain your iif(...) formula, and also bind it to a field in a table or
query. You will need code to insert or edit the table data when the value
is calculated; this will normally be placed in the AfterUpdate event of your
control, or in a form's Current event - it's up to you to decide where the
most appropriate place is.

Last, there may be several reasons why you can't use the calculated value in
another form. The form containing the calculated value must be open; and
the control containing the value must be referred to correctly. This second
point is a likely source of error if you are using wizard-generated forms,
since the control will have the same name as the field to which it is
bound - or not, in your case ;-) If your control containing the calculated
value is named [My Price], the other form(s) may be trying to get the value
from the [My Price] field in the table - and this doen't contain the value,
as discussed above. It's good practice to rename wizard-generated controls,
following a standard naming convention. It's also good practice not to
include spaces in names of anything in your database (objects, fields,
controls, ...). Your calculated value control should have a name such as
tboxMyPrice, and you can use its value on another form (if its form is open)
by referring to it as Forms("frmMyFormName").tboxMyPrice. [Note: this
syntax will not work if your control is on a subform, since subforms are not
members of the Forms collection.]

Hope this helps - and I also hope this doesn't appear twice - my system
crashed after I wrote this and (I think) before it was sent.

Rob

I am creating a db for a retail business. I have multiple vendors from which
I buy the same product for resale. These vendors issue pricing either based
on net or varied discoumts from their list price. I created a table for the
products, a sub table for the vendors, and another sub table to caculate
pricing based on list / net costs. On the form, I'll take the vendors price
and use a Yes/No field to determine a calculation for my price, eg. Iif
([List /Net], [Vendor Price], [Vendor Price] - ([Vendor Price] * [Vendor
Discount])) . The calculations in the form field [My Price] work, however
this calculated number does not show up in the table field [My Price], nor
can I link this calculated number into other fields on other forms (PO's). I
have checked the one-to-many relationships and referential integrity /
cascade up and delete and nothing?