Cowboy_BeBa
Registered User.
- Local time
- Today, 09:23
- Joined
- Nov 30, 2010
- Messages
- 188
Hi all
I have a subform thats essentially just a Bill Of Materials for a list of products (which are on the main form)
so selecting a product on the main form allows you to view/add the products bill of materials in the subform, very basic
the trouble im having is that now i wanna add a costing feature
to calculate the cost i need to multiply three fields stoCost (the cost per unit of the item in the sub form) bomSize (the size of each unit) and bomQTY (the total qty used), also if field bomUsage is equal to the word "Add", this value needs to be multiplied by -1
so with that in mind if created a calculated field in the subform with the control source
=IIf([bomUsage]="Add",[stoCost]*[bomQTY]*[bomSize]*-1,[stoCost]*[bomQTY]*[bomSize])
however this merely gives the cost of each line item (which is useful, as if a product costs too much to produce it helps to know how much each item costs so that the total cost may be minimized), however i need a total as well
Unfortunately, as this is a calculated field there is no "Sum" option when i select the totals bar (only "Count")
Ive tried adding a form footer to the sub form and adding another field with the control source "=Sum(txtCostings)" (txtCostings being the name of the text box in which i put the first control source)
however the form footer is nowhere to be seen when i open the form in form view
can anyone tell me what im doing wrong?
ive written a query to determine the total and theoretically i could put a text field in the main form with a dlookup control source to this query, however im worried ill need to have the form refreshed every time a change is made or a new record is selected, would rather not go down that route if i can avoid it
I have a subform thats essentially just a Bill Of Materials for a list of products (which are on the main form)
so selecting a product on the main form allows you to view/add the products bill of materials in the subform, very basic
the trouble im having is that now i wanna add a costing feature
to calculate the cost i need to multiply three fields stoCost (the cost per unit of the item in the sub form) bomSize (the size of each unit) and bomQTY (the total qty used), also if field bomUsage is equal to the word "Add", this value needs to be multiplied by -1
so with that in mind if created a calculated field in the subform with the control source
=IIf([bomUsage]="Add",[stoCost]*[bomQTY]*[bomSize]*-1,[stoCost]*[bomQTY]*[bomSize])
however this merely gives the cost of each line item (which is useful, as if a product costs too much to produce it helps to know how much each item costs so that the total cost may be minimized), however i need a total as well
Unfortunately, as this is a calculated field there is no "Sum" option when i select the totals bar (only "Count")
Ive tried adding a form footer to the sub form and adding another field with the control source "=Sum(txtCostings)" (txtCostings being the name of the text box in which i put the first control source)
however the form footer is nowhere to be seen when i open the form in form view
can anyone tell me what im doing wrong?
ive written a query to determine the total and theoretically i could put a text field in the main form with a dlookup control source to this query, however im worried ill need to have the form refreshed every time a change is made or a new record is selected, would rather not go down that route if i can avoid it
