Copying value from previous record in a continuous form

[b-ballstyle]

Alright, so I have a continous form on a form and i want to take a value in the previous record (BLUE), subtract one value in the current record (GREEN) and put that calculated value in the field (BLACK). All this by pressing the button on the current record (RED).. Is it possible? I can find any documentation on how to reference that previous record.. Please help

[June7]

Are you saving Balance to table? Saving calculated data (data based on other data), especially aggregate data is usually a bad idea.

This calc is easily accomplished on report. Textbox on report has a RunningSum property. To perform running subtraction, review http://stackoverflow.com/questions/5...ng-subtraction

Doing this on form is more complicated because need to calculate running sum/subtraction of all previous records. This can be done with a subquery or DSum() function.

Another possible approach is code to set the DefaultValue of a hidden textbox to the balance calculated in current record. Control Source of the visible Balance would be: HiddenBal - Used. Use the AfterUpdate event of Used textbox to set DefaultValue property of the hidden control.

[b-ballstyle]

The balance is saved in my table.. I just want, on click of the button to take the last balance, sustract the USED value and set value to the balance.. I dont want that field to be "calculated", just a button that will set the value if you ask for it

[b-ballstyle]

Also this cant be a "calculated field" because the first value is entered manually

[June7]

Using code to set value of unbound textbox in a continuous/datasheet form will result in all instances of the textbox displaying same value. Therefore, my suggestion is the calculation be automatic or not at all. Doesn't matter if the Used value is entered or not, the Balance textbox will be Null until Used value is entered because calculations with Null result in Null.

[b-ballstyle]

Using code to set value of unbound textbox in a continuous/datasheet form will result in all instances of the textbox displaying same value. Therefore, my suggestion is the calculation be automatic or not at all. Doesn't matter if the Used value is entered or not, the Balance textbox will be Null until Used value is entered because calculations with Null result in Null.

But I cant do it that way because for the first month of the list, I only have the balance (that I have to enter manually) and no used value..

[June7]

Like I said, not easy on forms. Forms are designed for data entry, reports are for data manipulation and output. That's the intent of Access designers and possibly of any relational database application.

How much trouble do you want to go through to get this output on form?
Google: access running balance on form
Review http://social.msdn.microsoft.com/For...e-ea626e9de964

I presume the monthly records are in a subform? The arrangement of fields under Ordered and Received indicates the database is not in normalized structure.

[b-ballstyle]

I know but I dont want a running sum. What I want to do, for example, is just take a value of a specific row and use it elsewhere.

[June7]

And that is what is hard. That is pulling a value from other record(s) in the dataset. Forms are not designed for that. 'Rows' in a form are not like 'rows' in a spreadsheet. Completely different concept. Each 'row' on form represents a record of data. These 'rows' are repetitions of the controls you see in Design view. So, they really aren't 'rows', not in the sense you see in a spreadsheet where specific value can be addressed in a calculation by cell coordinates. Cannot do that in relational database.

[b-ballstyle]

Yes but lets say you have a query you should be able to pull data from any record where certain information matches

[June7]

Yes, and that's what subqueries and domain aggregate functions are for.