- Local time
- Today, 22:24
- Joined
- Feb 19, 2013
- Messages
- 16,618
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
and in tblData data macros after Update datamacro I use
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
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