using data macro to retrieve record that has just been updated (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Feb 19, 2013
Messages
16,553
I'm experimenting with data macros and want to insert into a history table the newly changed record. In real life, the history table is in a remote database which cannot be accessed using a data macro so calling a vba function seems the way to go. Since I'm still in testing mode, I have set up a simple db using a local history table to test principles.

So I created a vba function

Code:
Function upDateHistory(PK as Long) as boolean
dim sqlStr as String

    Debug.Print PK
    DoEvents
    sqlStr="INSERT INTO tblHistory SELECT * FROM tblData WHERE dataPK=" & PK
    currentdb.execute sqlstr

End Function

and in tblData data macros after Update datamacro I use

Code:
SetLocalVar
    Name: addToHistory
    Expression: upDateHistory([dataPK])

this works in that the function is called and a record is inserted - but the data is the data prior to the change., not the changed data.

It would appear that despite using the after update macro, the vba function is running before currentdb is refreshed. I tried

dim db as dao.database
set db=currentdb

db.execute sqlStr

but same result - the old data is put into the history table

I also tried the same with the after insert event. Although the correct PK is returned, no insert occurs because no record exists at that point

At the moment, although I haven't tried it yet the only way I can see to move forward is to write numerous SetLocallVars to call numerous functions to populate a global array or tempvar and then use a VALUES type sql code but this could be a nightmare to maintain.

Does anyone have any suggestions? I might be missing something obvious with trying to keep it simple. Note this needs to be run from a data macro, running it from the front end as part of the update process is not an option for this particular task.

I attach the simple db to illustrate the issue
 

Attachments

  • datamacro.accdb
    424 KB · Views: 403

theDBguy

I’m here to help
Staff member
Local time
Today, 07:01
Joined
Oct 29, 2018
Messages
21,357
Hi. I suspect you may have to pass the actual values you want to store in the history table to the VBA function in your data macro. I say this because I am thinking the VBA function probably can't see the changes in the table yet until the data macro has completed and the database engine has finished processing the update. Hope that makes sense...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:01
Joined
Oct 29, 2018
Messages
21,357
Here's one way of doing it.
 

Attachments

  • datamacro.zip
    20.6 KB · Views: 230

CJ_London

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Feb 19, 2013
Messages
16,553
Thanks DBG - that was my thinking and is what I thought I might have to do - which is a PIA since in the real system there are around 200 fields (data is not normalised as required by the destination db)
 

Users who are viewing this thread

Top Bottom