Previous Record

[KRRC]

Hi!
I have a form, which has 3 fields. One is the date, the other is the meter value (TarA), and the last is the consumption (CTarA) which is the difference between the today's meter value and yesterday's. I also want to have the option to correct previous readings if necessary, and so I have the following code:


Private Sub TarA_AfterUpdate()
On Error GoTo Err_TarA_AfterUpdate

Dim TarAValue As Integer, PrevTarAValue As Integer, NextTarAValue As Integer

TarAValue = Me.TarA

Recordset.MovePrevious
PrevTarAValue = Me.TarA

Recordset.MoveNext
Me.CTarA = TarAValue - PrevTarAValue
Me.Form.Refresh

Recordset.MoveNext
NextTarAValue = Me.TarA
Me.CTarA = NextTarAValue - TarAValue
Me.Form.Refresh

Exit_TarA_AfterUpdate:
Exit Sub

Err_TarA_AfterUpdate:
MsgBox Err.Description
Resume Exit_TarA_AfterUpdate

End Sub

This works fine for correcting existing records but doesn't work for new records. I'm a beginner at this, so it will probably be something obvious...
Thanks for any help.

Kathrin

 

[KRRC]

Please...Can anyone help?

 

[Fizzio]

I shall try to be your knight!

A couple of further questions...

If you change a previous value, do you want the following record to be updated?
What exactly do you mean by it not working for new records?

I would probably use different code (using a mirror recordset rather than manipulating the form's recordset directly) to get the previous value.

 

[KRRC]

Thanks for replying!
Yes, I want to update the next record when I change a previous record.
What I mean, is that it just doesn't work with new records. After updating the field TarA, the CTarA stays empty. If I go to a new record and the come back to this one it works fine...
I kind of know what a mirror recordset is, but I wouldn't know how to manage the code. It was hard enough to get the above code half working

Maybe I'm not being clear enough, so let me know...thanks again.

Happy New Year!

 

[Pat Hartman]

Storing a calculated value is a violation of third normal form. At a more practical level, it leads to data anomolies and is as you can see, difficult to code.

A better simpler solution is to use a DLookup() to find the ending value from the previous record so you can calculate consumption as you need it.

YourQuery:
Select Max(YourKey) as MaxYourKey From YourTable
Where YourKey < Forms!YourForm!YourKey;

=DLookup("TarA","YourQuery")

 

[KRRC]

Ok, I have given up on the idea of storing the calculated value, but I need it to be on the form. I have managed this by using the prerecval and also dlookup (looking up the value on the table instead of a query), problem is that if i need to go back and correct any record it wont update the calculated field of the next record, which should also be affected.
I tried using a query and Dlookup as you suggested, and I get the same problem.
What do you suggest?
Thanks.
Kathrin

 

[KRRC]

By the way, I also tried doing the calculations on the query, but the form would only be updated after exiting and opening it, so I dlookup the value of the day before on the query and the subtract it from the day concerning the record. (to confusing?!)

 

[Pat Hartman]

You would need to requery the form in order to get the new values to propagate. That's why it seems to work if you exit the form and go back. After you change the value, requery the form.

Me.Requery

 

[KRRC]

Thanks! It seems to be working fine.

Kathrin