Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals (Access XP)

    Evening Everyone,

    Hope someone can help me here.

    I have several fields, BegBal, Pymt, and NewBal.

    After each record is updated, and a new record is created, I would like the NewBal to automatically become the BegBal for the new record.

    In my mind this should be a simple process...perhaps I have over thought it and has become too difficult...does anyone have any suggestions?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Total (XP)

    You can't have a 'floating' default value in a table, but you can do this on a form. However, I would like to recommend a different approach.

    Recording balances falls into the category of 'storing calculations'. You are storing the transaction (pymt), so that is does cancel out one of the issues with storing calculations (if you store a calculation, without the transaction, you lose historical tracking.) However you are now storing more information that you need to physically store on disk.

    Instead, build a table of transactions with Date fields (and/or time). Then, to get the balance at any given point, use a query, that pulls the appropriate records (based on <=requested date), and sum the pymt field. (You will always have the correct balance that way, historically accurate, and without storing repetitive data.....)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totals (Access XP)

    Have you already got NewBal of the previous record (this becomes BegBal of this new record), and that all you want is add BegBal to Pymt giving NewBal for the new record.
    I presume that this is on a form, and that you have setup a field called NewBal (hidden), so the code required is in the BeforeUpdate for the form as follows:
    NewBal = BegBal + Pymt

    I am presuming that the fields BegBal and Pymt are fields on the form.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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