How do I grab the value from the previous record on this calc field?

[kellstee]

I have a form with the following fields:
Beginning Balance, Additions, Subtractions, and Ending Balance.

Additions and Subtractions comes from a query, Beginning and Ending balance are derived (Beginning Balance on the first record comes from a table).
Ending Balance is just calculated (Beginning + Adds - Subtracts).
The rest of the Beginning Balance values on all other records besides the first should be the previous Ending Balance.

I used the PrevRecVal function found here thinking I could pick up the Ending Balance value from the previous record:

http://support.microsoft.com/?id=210504

But it doesn't seem to be working. I think because my Ending Balance is calculated in the form and doesn't come from a query?

How can I come up with Beginning Balance?

Kelly

 

[Remou]

Several methods are shown in the Microsoft article; which one 'did not work' and in what way did it fail? What information is stored? Would it be correct to say that the Ending / Beginning balance is the Opening Balance (DLookUp) plus the sum to date of Additions (DSum) minus the sum to date of Subtractions (DSum)? Have you considered the Default Value?

 

[kellstee]

Thanks for the response, Remou. The first method in the article doesn't work for me. My "primary key" is a date field and the dates can sometimes vary. I used the second method and it does work for me, just not on a calculated field in the form (I calculated Ending Balance in the form).

I think you're pointing me to the dsum formula, so I researched that a little, but still seem to be stuck on the same issue.

DSum sums only the values that come from my source (my main data source is a table). When I try to do the dsum on a calculated field in the form, I get "error".

And my mistake. . .I had said that my Adds came from a table. They do not. They are derived in the form. So when I try to dsum on Adds - Subtracts + Initial Balance, I get an error because the Adds are derived in the form (I'm guessing here).

Kelly

Kelly

 

[kellstee]

Anyone have any other ideas?

 

[postermmxvicom]

Okay, now I am a newbie, so you might have to slow this down for me. As I understand it:

The second method fails to work because you want to look up a calculated field, not a field from the underlying table.

So...why not lookup the values in the table that the calculated field uses, then recalculate your answer.


Or, why not make a query that performs this calculation. Then lookup the previous record there and retrieve the info.



One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[Remou]

Please post some sample data. I do not quite understand 'the dates can sometimes vary'. The important thing about dates, especially dates used as primary keys, is that they occur one after the other, though sometimes with big gaps.

I am a little surpised that you are abandoning data such as additions and subtractions.

As I mentioned, there is always the Default Value, which can be set programmatically, but will only work if records are being added.