Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update table from report data (Access 2K SR2)

    What I want to do is take each of the group footer subtotals and update a field in a table. It probably seems back to front but it also seems convenient since the sub-total is there.

    The background of it is to create a weekly ledger report of several departments which displays last weeks total of each department, this week's activities, plus the new balance. So I figure if I can take each department's new balance and dump it into a relevant field for each dept in the dept table, I can use it again as the opening balance for next week's ledger run.

    I hope this makes sense.

    So far I've created an update query to run when the group footer is formatted but it only updates one record. How can I do the rest?

    Any ideas?

    Thanks

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

    Re: Update table from report data (Access 2K SR2)

    Have you set the correct criteria? Which record is being updated?

    The SQL for the update query should look something like this:

    UPDATE tblDepartments SET Balance=[Reports]![MyReport]![txtBalance] WHERE [DepartmentID]=[Reports]![MyReport]![DepartmentID]

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table from report data (Access 2K SR2)

    Yes. That's exactly the one I'm using. It will update one record correctly and ignore all the others. I've also tested it on the On Format property and the On Retreat property of the group footer, but not on On Print.
    Should I be addressing the Group Footer collection? If so, I have no idea how to do that. If someone can help please?
    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table from report data (Access 2K SR2)

    Sorry. One more thing. If I use On Format the last record is updated. With On Retreat, the first record is updated.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update table from report data (Access 2K SR2)

    Why are you referencing a subtotal in a report for this instead of just calculating it and saving the value?
    Charlotte

Posting Permissions

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