This seemed to me a very easy thing to do at the beginning, but it got complicated and very challenging. I am pretty new to MS Access, so -after struggling for many days- I hope to find the solution here.
Say we have a simple table like this:
Now, I need to track changes on this table on the record level and Log the changes in another table (Say Log table) like this:
Where track_type:
1 for add(insert)
2 for edit(update)
3 for delete
The changes(add/edit/delete) should be logged when the record on the Student Form is changed.
My Problem is that I am not able to generate the changes correctly.
My form has to refresh a lot which make excessive entries in the log table like this one:
As you can see, instead of one log for the insert, it takes two logs to insert a record.
I searched the net thoroughly, but I cannot find this kind of logging. I found information about logging on the field level, but this is not what I want. I know it may be better in most cases, but not for my case.
So, I will appreciate any guiding information/code.
a minimized database that focuses on the problem is attached and you can download it and see what I have tried and where I am stuck.
Notes:
1- Changes should be logged only when the user: - Move from the current record on the form. - Exit the form
2- Changes Should not happen while on the same record even if these changes committed to the Student table.
3- Refresh is unavoidable.
4- Audit Trail is not helpful if on the field level.
5- Data Macro is not helpful as changes can be committed more than once.
6- Before/After Insert or update event is problematic due to the M.Refresh. I need alternate method or how to do it correctly with these events.
7- It is highly appreciated to do the solution within the attached database.
8- Custom Navigation button are not an option.
Say we have a simple table like this:
Now, I need to track changes on this table on the record level and Log the changes in another table (Say Log table) like this:
Where track_type:
1 for add(insert)
2 for edit(update)
3 for delete
The changes(add/edit/delete) should be logged when the record on the Student Form is changed.
My Problem is that I am not able to generate the changes correctly.
My form has to refresh a lot which make excessive entries in the log table like this one:
As you can see, instead of one log for the insert, it takes two logs to insert a record.
I searched the net thoroughly, but I cannot find this kind of logging. I found information about logging on the field level, but this is not what I want. I know it may be better in most cases, but not for my case.
So, I will appreciate any guiding information/code.
a minimized database that focuses on the problem is attached and you can download it and see what I have tried and where I am stuck.
Notes:
1- Changes should be logged only when the user: - Move from the current record on the form. - Exit the form
2- Changes Should not happen while on the same record even if these changes committed to the Student table.
3- Refresh is unavoidable.
4- Audit Trail is not helpful if on the field level.
5- Data Macro is not helpful as changes can be committed more than once.
6- Before/After Insert or update event is problematic due to the M.Refresh. I need alternate method or how to do it correctly with these events.
7- It is highly appreciated to do the solution within the attached database.
8- Custom Navigation button are not an option.
Last edited:
