January 2013 - Posts

Lag in data being updated

I have to do some minor, or so I thought, summing of detail amounts entered in a subform and placing the sum on the transaction header record.    But, most of the time, amounts were updating one record behind were they were supposed be updating.  

I spent hours trying to figure out what was going on what with debug.print statements with embedded Now()s, verified that the record ID of queries being passed to the VBA code was correct and so on and so forth.  The debug.prints on the subform and the code were clearly showing I had the correct Autonumber record ID.  The amounts in the debug.print behind the subform were showing the correct values but the code running the SQL query to do the update was showing the old record values.

I put the code in behind a command button on the form and it worked as expected.

I knew it was something stupid.  And sure enough it was.

I was running the code to update the transaction header record in the controls AfterUpdate event.   I should’ve been running the code in the forms AfterUpdate event.   Duhhh!!!! 

The debug.print in the AfterUpdate event was showing the value of the control and not the value of the field in the record because the update from the form to the table hadn’t occurred yet.

(And yes I decided to add a Duhhhh category to my blog postings.)

Posted by Tony | with no comments
Filed under: ,