Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't get form total to update (A2003)

    I have a continuous form based on a query that lists (amongst other data) amounts and status, the latter in the form of a true/false tickbox. What I want to do is to have a total on the form that returns the value of the items ticked so that when I tick or untick an amount the total updates itself.

    I've written a function to iterate through the database to arrive at the total of items ticked and have tried to attach this to the on click event for the tickbox. I can't get this to work and I think this is because having not moved away from the record it hasn't been saved. In other words the tickbox looks as if it has been ticked but (I'm presuming here) since the record is not actually saved the function is still reading the tickbox as unticked. I've also tried the after update event but this also seems not to work. Any ideas how I should proceed.

    Regards

    David

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

    Re: Can't get form total to update (A2003)

    Whether you use an expression with Sum or with DSum or with a custom function, it will only look at the values as saved. You can't expect an expression in the form header or footer to take unsaved values into account.

    One workaround is to save the record in the After Update event of the check box:

    Private Sub chkStatus_AfterUpdate()
    RunCommand acCmdSaveRecord
    End Sub

    This will cause the sum to be updated (more or less) instantaneously.

    Another workaround would be to treat the amount in the current record separately: set the Control Source of the text box to an expression like

    =DSum("curAmount","tblSomething","ysnStatus=True AND Not ID=" & [ID])-[ysnStatus]*[curAmount]

    Here, tblSomething is the name of the table.
    curAmount is the name of the amount field.
    ysnStatus is the name of the status field.
    ID is the name of the primary key field, assumed to be numeric.

    DSum adds the amounts for all other records, and -[ysnStatus]*[curAmount] adds the amount for the current record if Status is True (the formula makes use of the fact that True = -1).

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Can't get form total to update (A2003)

    You can force a Save with either

    DoCmd.RunCommand acCmdSaveRecord or me.Dirty = False
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't get form total to update (A2003)

    Thanks to both of you for the suggestions. The RunCommand did the trick. I'll look further into your other workaround, Hans, as it seems to be more elegant than running through the whole table in VBA.

    Regards

    David

Posting Permissions

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