Why does a DLookup in a Before Change data macro sometimes return an old value?

Question

I have a large relational Access 2010 database. It is normalized, and includes some union queries that are very slow. I therefore thought I could speed things up by creating some cached fields. For example in tblOrder I would create a CustomerName field. To maintain this cached field I created a Before Change data macro that would dLookup the customer's company name from tblCustomer. It worked great. Then I created an After Update data macro in tblCustomer so when the user changes the Company Name all the child records would automatically be updated. It worked, but then the Before Change data macro fired and the dLookup returned the old Company Name. Any help would be very appreciated.

I made a sample of my problem using the Northwing Database. You can download a copy of it at http://www.thetechmentors.com/freestuff/exerciseFiles/msAccess/DlookupDatamacroProblem.zip

Answer 1

All you need to do is tweak the Before Change data macro on [tblOrder] to do the name lookup only when the [CustomerID] changes in that table. You can do that using the Updated() function like so:

That way when the macro fires as a result of the update performed from the After Update data macro on [tblCustomer], the [tblOrder].[CustomerID] value has not changed so the name lookup is bypassed.