Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Records _ Querying Edited Record On 'after Update' Data Macro Event

Posted by: samb Jul 30 2019, 11:54 AM

Hello, so here's the situation. I'm working with an accounting database. There are two tables - one that holds transaction summary and a second that holds transaction detail on a one-to-many relationship. A VBA function runs on the detail table's "After Update" data macro event that uses the detail records to update fields on the summary table. The function queries all of the detail records for that transaction. The problem I'm facing is that when I query the detail table, I am not getting the edited record with updates.

I've encountered this problem elsewhere with "After *" data macro events. It seems that the update operation doesn't actually complete until after the macro stops. When I breakpoint the function, I can see that the record is still in edit mode. I kind of understand this given that the data macro has access to the old and new field values. Is there a way to force the update operation to complete within the 'After Update' event? I've been playing around with various DAO.Recordset methods (LockEdits, Edit, Update, etc.), but no luck.

And I know it might be advisable to move this process down to a query or form, but this is a personal project where my preference is to keep as much application logic in the table as possible.

Posted by: theDBguy Jul 30 2019, 11:59 AM

Hi. I'm a little confused, probably because I haven't used this approach a lot, but are you really talking about using a data macro? The one you activate while in the design view of the table? Or, are we simply talking about a VBA function behind a form? Thanks.

Posted by: samb Jul 30 2019, 12:05 PM

I am using the "After Update" data macro attached to a table.

Posted by: GroverParkGeorge Jul 30 2019, 12:08 PM

Those fire independently of any VBA, do they not?

Posted by: theDBguy Jul 30 2019, 12:09 PM

I am using the "After Update" data macro attached to a table.
And did you say you use a VBA function in it? I didn't know that was possible.

Posted by: samb Jul 30 2019, 12:18 PM

Yes, you can use "SetLocalVar" to run a public function.

Posted by: theDBguy Jul 30 2019, 12:19 PM

Nice. Thanks!

Posted by: samb Jul 30 2019, 01:09 PM

UPDATE: Tried a few more things, including pausing execution and DoEvents, but the data macro seems completely synchronous. Perhaps this is protection against recursion. I'm probably trying to make Access do something it isn't designed to do. Wouldn't be the first time wink.gif

Posted by: theDBguy Jul 30 2019, 01:27 PM

Hi. That's too bad. I wish I had better news for you, but I also don't have any idea how to get around it. Sorry.