Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    14

    Exclamation Unanswered: After Update event problem

    I have a form with a subform in datasheet view. The main form is for creating new orders and the subform records the items for each order.

    In the after update event of the "Part" field on the subform, the part price is looked up with a Dlookup and placed in the appropriate field on the subform.

    What I am trying to do is have a running "Price Total" field on the MAIN form which sums all the part prices on the subform. Using the Dsum I can accurately sum all the part prices from the subform which match the order number on the main form.

    The problem is that I cannot get the "Price Total" field to automatically update itself, even when it is supposedly changed in the afterupdate event of the subform field. It updates only after a part is selected and the selected again. ( i want it to update the price total after only one selection of the part on the subform)

    Here is the afterupdate event for the part field on the subform:

    Private Sub Part_AfterUpdate()
    Me!PartPrice = DLookup("PartPrice", "Parts", "Part = '" & [Part] & "'")

    total = DSum("[PartPrice]", "SubForm_Source", "OrderNbr = " & Forms![MainForm]![OrderNbr])

    Forms![MainForm]![PriceTotal] = total

    End Sub

    "total" is declared as a public variable of type Double in a module.

    I have also tried the afterinsert event of the subform and making the control source of the "PriceTotal" field a dsum function. Neither of these things corrected the problem. Two selections of the part were still required before the PriceTotal would update itself.

    Any suggestions would be greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    I think that your problem is that the record in the subform is not saved at when you calculate total....

    so you should try saving it before total formula.....

  3. #3
    Join Date
    Jul 2002
    Posts
    14
    Thanks for the idea, but that didn't seem to do anything.

    I placed the line
    DoCmd.RunCommand acCmdSaveRecord
    right above the total line.

    In theory I completely agree with what you said, but it seemed to have no effect at all on the form.

    I don't know if it makes any difference or not, but I failed to mention in my previous post that "Part" on the subform is a combo box and the user is selecting this with the mouse. I also noticed that I get "PriceTotal" to update not only when I select that same part and then select it again, but also when I have multiple records on the subform and I go back and change a part which is not the final record.

  4. #4
    Join Date
    Dec 2002
    Posts
    60
    Try adding:

    Forms![MainForm]![PriceTotal].Requery

    after

    Forms![MainForm]![PriceTotal] = total

  5. #5
    Join Date
    Aug 2003
    Posts
    23
    I think the easiest way to get automatic update is withaout coding at all.

    Place a textbox in the subforms footer.
    ControlSource:
    =Sum(PriceFieldName)

    In the main form another textbox.
    ControlSource:
    =subFormName.Form!TextBoxInSubName

    I hope that will work.

    /Svein Erik

Posting Permissions

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