Update table with form data

[terryvanduzee]

Hello
I have a form (Access 2003) that updates based on a calculation in the form. What I am trying to do is to update a table value (insert a record for the current form information, including the calculated field). Ex: I have a field that calculates a running balance. In my table I have a balance field.)

Now, what happens is I have a "Next" button that takes the user to a new record, and the balance value shows up properly. But if I close the form and reopen it, the value for balance always shows up as blank (new record). I need to be able to display the value from the table where the ID number is max and offset the record by -1 (the last value before the new record). I tried DMAX but could not find a way to do DMAX -1.

The table's ID field is an autonumber so the last record will always be the max.

Any help would be appreciated.

Thank you

Terry

 

[Pampers]

Hi Terry van der Zee,
It is a good database practice not to store calculated values in a table, because 99% of the time there is no need, it just bloats your database. Have a look at the subject database normalisation.

Pampers
Keeping it simple can be complicated

 

[terryvanduzee]

Pampers:

Yes, I realize the normalization factor of this, but it is not the calculated field I am storing. It is the balance field which is updated by the calculated field.
On the form, I have a Starting balance and a balance. The balance field on the form is the calculation and the Starting balance is the field from the table.

Ex: Current balance is $100.00

Transactions happen on the form that bring that balance down to $25.00, that $25.00 is then written to the table as the new balance. That new balance is what should show up in the balance field on the form when the form is opened next. But because the form is set to "Add" records, it begins a new record, where the DMAX formula picks up the blank balance for the new record. What I need it to do is to pick up the last record with a balance (the record before the new record).

Thank you
Terry

 

[Pampers]

Hi Terry,
I got it. And this doesn't work?

=DMax("BookingID","tblBooking")-1

Pampers
Keeping it simple can be complicated