DSum always one record behind

[reburton]

Hi,
I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind.
The save code is:
Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset)
rst.FindFirst "ID = " & Forms!frmInvoice!txtID
With rst
.Edit
!Total = Forms!frmInvoice!txtTotal
.Update
End With
Exit Sub

txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.

 

[Mihail]

Fro your description, this code:
With rst
.MoveNext
.Edit
!Total = Forms!frmInvoice!txtTotal
.Update
End With
should solve your issue, but I can't explain what happen.

 

[reburton]

Thanks for replying! I tried adding .MoveNext but it now gives me an error message: " No current record". It also stopped saving any value at all to the table. I had used .MoveNext on t6he other end of the with statement, but I didn't think I needed it so I took it out just before I posted. I have to admit, I am stumbling around in the dark.

 

[Mihail]

Now, in the morning, I see more clear your first post.

I have code to save the value returned by DSum to a table

One of the DBs principles: Never store a calculated value in a table.

.... on a lost focus event

Only in order to understand what is happen, move your code (not my) under the After Update event.

But I repeat: Never store a calculated value in a table. You can store this in a variable (a temporary table, that not involve relationships, is also a variable ) in order to use it later, in the same session, in your code.