Calculate line total without leaving the record

[BruceM]

I have a purchase order main table with a line items child table. They are
related one-to-many. Data entry is by means of a main form with a
continuous subform. It is a standard set-up for such situations.

The user selects the product and the quantity. Product Description, Product
Code, Unit, and Unit Price are displayed based on the Product selection.
ProductID and UnitPrice are saved. The other Product fields are displayed
but not saved. Quantity is entered and saved. The line total is determined
by multiplying Product by UnitPrice.
I tried displaying the purchase order total by means of the expression:
=Sum(Quantity*UnitPrice)
in an unbound text box (txtTotal) in the form footer.

The trouble was that it did not calculate when the Quantity was selected,
but rather when I left the new record. However, attempts to save the record
(and thereby get the correct total in the text box) ran afoul of the
subform's Before Update code, which prevents the user from creating a line
item without a quantity.

All I could come up with was the following in the txtQuantity (the Quantity
text box) After Update event:
Me.txtTotal = DSum("Quantity*Items_UnitPrice", "tblPO_Items", _
"PO_ReqID = " & Me.PO_ReqID & "") + _
(Me.txtUnitPrice * Me.txtQuantity)

In the form's Current event:
Me.txtTotal = DSum("Quantity*Items_UnitPrice", "tblPO_Items", "PO_ReqID = "
& Me.PO_ReqID & "")

This seems unnecessarily awkward, but I cannot seem to find another way to
accomplish this. Am I missing something obvious?

 

[Graham Mandeno]

Hi Bruce

Instead of calculating the extended price in your textbox, calculate it in a
field of your form's RecordSource query:

ExtendedPrice: Quantity*UnitPrice

Then bind txtTotal to =Sum([ExtendedPrice])

This will improve the performance of your form. However, you will probably
find that the total still does not update until the current record is saved.
If you want the result updated before the editing of the record is complete,
you must force a save, but first check that sufficient data has been
entered:

If Not IsNull(UnitPrice) and Not IsNull(Quantity) and Not IsNull(ProductID)
Then
Me.Dirty = False
End If

 

[BruceM]

Thanks for the reply, and for the helpful suggestions. I was already
calculating the line total (Extended Price) in the query, but was having
trouble with the save (to get the line total while still in the record)
because I was not checking the text box values soon enough. By assuring no
null values in required fields as a condition for saving the record I
avoided that problem, and now it works as intended.
It seems obvious now that I see how it was done. My brain must have locked
up for a while there.

Hi Bruce

Instead of calculating the extended price in your textbox, calculate it in
a field of your form's RecordSource query:

ExtendedPrice: Quantity*UnitPrice

Then bind txtTotal to =Sum([ExtendedPrice])

This will improve the performance of your form. However, you will
probably find that the total still does not update until the current
record is saved. If you want the result updated before the editing of the
record is complete, you must force a save, but first check that sufficient
data has been entered:

If Not IsNull(UnitPrice) and Not IsNull(Quantity) and Not
IsNull(ProductID) Then
Me.Dirty = False
End If
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a purchase order main table with a line items child table. They
are related one-to-many. Data entry is by means of a main form with a
continuous subform. It is a standard set-up for such situations.

The user selects the product and the quantity. Product Description,
Product Code, Unit, and Unit Price are displayed based on the Product
selection. ProductID and UnitPrice are saved. The other Product fields
are displayed but not saved. Quantity is entered and saved. The line
total is determined by multiplying Product by UnitPrice.
I tried displaying the purchase order total by means of the expression:
=Sum(Quantity*UnitPrice)
in an unbound text box (txtTotal) in the form footer.

The trouble was that it did not calculate when the Quantity was selected,
but rather when I left the new record. However, attempts to save the
record (and thereby get the correct total in the text box) ran afoul of
the subform's Before Update code, which prevents the user from creating a
line item without a quantity.

All I could come up with was the following in the txtQuantity (the
Quantity text box) After Update event:
Me.txtTotal = DSum("Quantity*Items_UnitPrice", "tblPO_Items", _
"PO_ReqID = " & Me.PO_ReqID & "") + _
(Me.txtUnitPrice * Me.txtQuantity)

In the form's Current event:
Me.txtTotal = DSum("Quantity*Items_UnitPrice", "tblPO_Items", "PO_ReqID =
" & Me.PO_ReqID & "")

This seems unnecessarily awkward, but I cannot seem to find another way
to accomplish this. Am I missing something obvious?