Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating fields in a subform (Access 2002)

    I have a form with a subform containing PartID, PartName, Qty, UnitListPrice, ExtendedPrice, and DiscountedPrice. I have a subroutine that calculates the ExtendedPrice and DiscountedPrice whenever the Qty is updated. It determines the DiscountedPrice using the Discount listed on the mainform. The code is:

    Private Sub Qty_AfterUpdate()
    Me![ExtendedPrice] = Me!UnitListPrice * Me!Qty
    Me![DiscountedPrice] = Me!UnitListPrice * Me!Qty * (100 - Forms![FRM-Order].[Discount]) / 100
    Me.Requery
    Me.Refresh
    End Sub

    This works fine when I update the Qty field, but I also need to recalculate the DiscountedPrice whenever the Discount is updated on the mainform. I tried to requery and refresh the subform whenever the Discount is updated on the mainform, but this doesnt seem to update the discountedprice. I have also tried to recalculate the DiscountedPrice in the Subform, but this doesnt work either. Here is the code I tried:

    Forms![SBF-OrderParts].[DiscountedPrice] = Forms![SBF-OrderParts].UnitListPrice * Forms![SBF-OrderParts].Qty * (100 - Forms![FRM-Order].[Discount]) / 100

    Can someone help point out what I am doing wrong? Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Updating fields in a subform (Access 2002)

    I think you should create a query based on the Orders and OrderParts table, joined on the OrderID. You can calculate the ExtendedPrice and DiscountedPrice in this query. Set the Record Source of the subform to the name of this query, and bind the ExtendedPrice and DiscountedPrice text boxes to the corresponding controls. No code needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •