UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Querying Edited Record On 'after Update' Data Macro Event, Access 2016    
 
   
samb
post Jul 30 2019, 11:54 AM
Post#1



Posts: 84
Joined: 19-September 02
From: Oregon


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.

Go to the top of the page
 
theDBguy
post Jul 30 2019, 11:59 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
samb
post Jul 30 2019, 12:05 PM
Post#3



Posts: 84
Joined: 19-September 02
From: Oregon


I am using the "After Update" data macro attached to a table.
Go to the top of the page
 
GroverParkGeorge
post Jul 30 2019, 12:08 PM
Post#4


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Those fire independently of any VBA, do they not?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Jul 30 2019, 12:09 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
samb
post Jul 30 2019, 12:18 PM
Post#6



Posts: 84
Joined: 19-September 02
From: Oregon


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

Attached File  data_macro_screenshot.PNG ( 73.3K )Number of downloads: 5


Go to the top of the page
 
theDBguy
post Jul 30 2019, 12:19 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


Nice. Thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
samb
post Jul 30 2019, 01:09 PM
Post#8



Posts: 84
Joined: 19-September 02
From: Oregon


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
Go to the top of the page
 
theDBguy
post Jul 30 2019, 01:27 PM
Post#9


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 05:19 PM