Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum Totals (Access 2000/SR-1)

    I have a continuous form that I am inputting monthly totals for various line items. I am summing the row totals at the end of the row and also summing the monthly line item totals in a calculated control for each month, in the footer section. I have the row totals updating as the user makes changes and tabs to the next control, but the footer totals for the columns do not update until you click out of the row or close and reopen the form. Is there anyway to get these column totals to update as the user tabs through the form inputting data?

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

    Re: Sum Totals (Access 2000/SR-1)

    The only way I can think of is to have Me.Recalc in the On Exit event procedure of every control that contributes to the column totals. This may have a significant impact on the performance of your form. Example of code:

    Private Sub txtMyControl_Exit(Cancel As Integer)
    Me.Recalc
    End Sub

    If you have many controls, you can avoid creating a separate event procedure for each of them by creating a function:

    Private Function ControlExitHandler()
    Me.Recalc
    End Function

    Select all relevant controls, then enter<pre>=ControlExitHandler()</pre>

    in the On Exit event in the Properties window.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Totals (Access 2000/SR-1)

    That works great, but after the Recalc the focus goes to the next input field in the first record. How do I keep the focus in the same record so I can move left to right from field to field in the same record and still get the Recalc function?

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

    Re: Sum Totals (Access 2000/SR-1)

    In the example form I tested this on, I can tab through the fields in a record normally, although there is some delay if the number of records is large. Does it help if you put the expression =ControlExitHandler in the AfterUpdate event of the controls instead of in the OnExit event?

    And do you have other code in the OnEnter, OnExit, GotFocus, LostFocus, BeforeUpdate or AfterUpdate events of the controls or of the form that might influence the tab behavior?

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Totals (Access 2000/SR-1)

    Thanks! That solved the problem. Prior to making the post, I was trying some other things to make the calculations update and once I deleted that code everything works fine.
    Thanks again for the help!!!

Posting Permissions

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