Another possibility would be, rather than referencing the subform to put the
total in a control in the main parent form, instead to compute it directly
from the table on which the subform is based. Lets assume that the parent
form and subform are linked on columns named ItemID of number data type, and
that the subform is based on a table called Transactions. The parent form's
current ItemID can be used in the criteria of the DSum function to directly
sum the values from those rows in Transactions where the ItemID matches the
current one, so the expression for the ControlSource property of a text box
in the parent form would be:
=DSum([Quantity]*[Unit Cost],"[Transactions],"[ItemID]=" & [ItemID])
The first reference to ItemID in the above is to the column in the table,
the second to the current ItemID value in the parent form.
This approach would mean you don't have to reference the subform at all so
you can fill the controls in that in whatever way you want. Domain
aggregation functions like DSum are not the fastest kid on the block,
however, so performance might be affected , but probably not unduly so.
You might find you need to Recalc the parent form in the AfterUpdate event
procedure of the subform to get the total in the main form to reflect any
changes made to the subform's data:
Me.Parent.Recalc
Ken Sheridan
Stafford, England
OnTheJobLearning said:
Actually, forget this post. This query is in a earlier revision before I put
in the combo box to auto fill in the Unit Cost from the selection made. This
is the root of all errors, but I'm trying to work with it bacause it will
make all entries so much easier.
It is the APPROVALS FORM that has the Qty, Unit Price, and Total Cost for
each line item within the Transaction #.
:
Actually, I have an Approvals Query that has the Transaction #, Wafer Type,
Unit Cost, Quantity and Total Cost calculatef for each line item for each
Transaction #.
Can this be used?
:
Please forgive me, I've been working on this one object for days. Things are
getting muttled together. People have answered, but I've tried everything
and can't get this one thing to work.
Since I've already gotten the subform created, not off a query, and the
combo box set up, is there a simple way to get this completed. I've changed
field names, control names......
I do have the "Approvals" form that calculates the total for each line item.
Can I base a query off this to calculate the total for each record
(Transaction #) and put this into the main form total field?
:
Rather than referencing the Column property of the combo box I'd suggest that
you base the subform on a query which joins the table containing the Unit
Cost column to the subform's current table(s), and include this column in
those returned by the query. You can then bind the Unit Cost control on the
subform to this column.
You can still use the Wafer TypeA combo box as at present, but you won't
need to include the Unit Cost column in its RowSource unless you specifically
want to see it in the control's drop down list. Once you select an item from
the combo box its Unit Cost will be assigned to the text box.
If you set the Unit Cost text box's Locked property to True and its Enabled
property to False this will prevent a user moving focus to it and
inadvertently attempting to edit it.
Ken Sheridan
Stafford, England
:
I'm struggling with calculating a sum from a subform into a main form.
The sum field in the main form is:
=[Approvals Subform]![Total Approved]
The Total Approved field in the subform is:
=Sum([Quantity]*[Unit Cost])
The Unit Cost field is: (Based off Wafer TypeA combo box choice)
=[Wafer TypeA].Column(1)
Once I based the Unit Cost field on the Combo box, the Total Approved would
not work. However, I have a TotalCost per line item that uses the "Unit
Cost" and this works.
Getting totally confused. If I have to enter the expression instead of Unit
Cost, "=[Wafer TypeA].Column(1)" I keep getting errors. I'm not entering it
right in the expression. Or, in the Total Approved field can I do a
=Sum([TotalCost]) to avoid using the expression at all?
