Results 1 to 12 of 12
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Put the following code in the After Update form of each of the subforms:

    Private Sub Form_AfterUpdate()
    Me.Parent.Recalc
    End Sub

    This will force the main form to update all calculated controls when a record in the subform is updated.

    Note: how did you create a continuous form with subforms? My Access (2002 SP-3) won't allow me to do that...

  2. #2
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Form based on subform values (2002 SP3)

    I have a continuous form with 2 subforms. On the master form I have calculated controls for Amount Due, Amount Paid, Amont Writeoff, and Balance. I also have a Field called "Paid in Full", and a field called Rebill Code. The value for Amount paid comes from one subform and is a sum of the all payments on the subform. The value for Amount Writeoff comes from the other subform and it too is a sum of all writeoff amounts. The calculated control Balance is the Amount due - Amount paid - Amount Writeoff. When Balance = 0, I want to automaticly change "Paid in Full" to a Y (true), and Change the Rebill code to a "P". The updates are not happening until I come back to the original record a second time.. Here is the sequence of events.
    1. Open form, everything is correct. Notice there needs to be a writeoff made.
    2. click in the writeoff subform (datasheet view) to add a record with an amount (the amount will be the current balance)
    3 after the amount is entered, click on the master form, the next record.
    4. The first record shows a balance of 0, but does not update the Paid in Full or Rebill Code.
    5. Click back on the first record and the fields will update correctly.

    I've even tryed setting up temporay fields that are calculated so that the user would immediately see what "Should" be displayed, but those updates are not happening be hind the scenes.

    Is there an event, or method to use to get a continuous form to update when I leave a subform? Any help would be greatly appreciated.
    John Brain

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    I have my subforms in the Form Footer section.
    Using the code you provide does update all calculated fields. Now is there a way to force the two bound fields to be updated. So that the main form recalculates Balance, and when Balance is 0, the table is updated to reflect Paid in Full and Rebill Code = P?
    Thanks HansV,
    John

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

    Re: Upate Form based on subform values (2002, (XP), SP3)

    In what way are Paid in Full and Rebill Code updated now (when you move to another record)? Do you have code in the Before Update event of the main form, or somewhere else?

  5. #5
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Right now the fields are getting updated only when I come back and click on the record. I have code on the subform "on Exit", I have code in the Main form "On Current". And I'm sure after all the testing, I have code all over the place.
    Here is the on current code (remember I'm using temp fields to display to user, but I need real fields updated)
    Due=Balance

    Private Sub Form_Current()
    If Me.Due.Value = 0 Then
    Me.PaidinFull.Value = True
    Me.RebillCode.Value = Me.tmpRebillCode
    Else
    Me.PaidinFull.Value = False
    Me.RebillCode.Value = Me.tmpRebillCode
    End If
    Me.Refresh

    Forms![payments Received]![tmpMsg].Visible = True
    Forms![payments Received]![Paid].Requery
    Forms![payments Received]![tmpRebillCode].Visible = True
    ' Forms![Payments Received]![RebillCode].Visible = False
    Forms![payments Received]![RebillCode].Visible = True
    Forms![payments Received].TPLRate.Visible = False

    Forms![payments Received].TPLAmount.Visible = False
    Forms![payments Received].RebilledPayor.Visible = False
    Forms![payments Received].TPLComment.Visible = False

    If Me.writeoff > 0 Then
    Me.[PayAdjustment subform].Visible = True
    Else
    Me.[PayAdjustment subform].Visible = False
    End If

    End Sub

    Private Sub PayAdjustment_subform_Exit(Cancel As Integer)
    Forms![payments Received].Recalc
    End Sub

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

    Re: Upate Form based on subform values (2002, (XP), SP3)

    I would add code to the After Update event of both subforms, below the Me.Parent.Recalc instruction:

    If Me.Parent.Due = 0 Then
    Me.Parent.PaidinFull = True
    Me.Parent.RebillCode = "P"
    Else
    Me.Parent.PaidinFull = False
    Me.Parent.RebillCode = ? ' fill in appropriate value
    End If

  7. #7
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Thank you Hans. That worked great. And just to go one step further, I noticed that when I deleted my Writeoff record in the subform, the main form is not updating. Is the deletion of a record different from updating a record?
    Thanks again for all the help.

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

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Yes, deleting a record is not the same as updating it. You'll also have to use the AfterDelConfirm or Delete event of the subforms to recalculate the main form and update the two bound controls.

  9. #9
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Ok, Thank you very much for all your help. Should have asked several days ago. Outstanding help as usual.
    John

  10. #10
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Hans,
    One more question regarding this if I may. I also have a Button called "Pay It" located on the main form. If the user clicks on it, a Macro runs that opens the subfrom, adds a new record, and updates the values appropriately. This is just a quick method for the user to make full payments to many different line items when we receive a large check. However, do to the code we added with the line "Me.Parent.recalc" in the subforms After Update event, I get an error when I run the macro. As much as I like the Me.Parent notation, can I refer to the parent or Main form specifically?
    Thanks,
    John

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

    Re: Upate Form based on subform values (2002, (XP), SP3)

    You can replace Me.Parent with Forms!NameOfTheForm. If the name of the main form contains spaces or punctuation, enclose it in square brackets: Forms![Name Of The Form]

  12. #12
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upate Form based on subform values (2002, (XP), SP3)

    Thank you once again.
    John B.

Posting Permissions

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