How to Use Data Macros to Track Changes to Field Entries in MS Access

In this episode, we continue our look at Data Macros by exploring how to log changes to a particular field in a table by logging each change in another table. We'll look at a price field in one table, then log each change to a product's price in a history table. This technique is requested often because it is one way that companies keep track of important fields in their databases.
Related Videos:
How to Use Data Macros to Track Changes to Field Entries in MS Access
You are watching this video now!
Data Macros in Microsoft Access
• Data Macros in Microso...
How to Use Macros in MS Access
• How to Use Macros in M...
Join me on Patreon!
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want the code from this video?
mackenziemackenzie.com/downloads
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...

[@miles6875]

Just ran into a use case for this video - excellent as always

[@seanmackenziedataengineering]

Great to hear! Those data macros are super handy :-)

[@ssdusd]

Thanks! I love videos about Macros

[@seanmackenziedataengineering]

Glad you like them!

[@SharifulIslam-kg1mz]

I like you very much for your kind helping to us with ms access very useful and nice tips & tricks...

[@seanmackenziedataengineering]

Many thanks!

[@aimenbaig6201]

Thanks man!

[@seanmackenziedataengineering]

No problem! Cheers

[@gerfer6261]

💕
I do have something similar to this ( audit trail) but this is more handy
It will be VERY handy if capture user name who done the changes too please

[@seanmackenziedataengineering]

Great idea for a video! How to lookup the Windows username and put it in there.

[@interestingamerican3100]

Hot dayum son!!! I was just researching this topic!

[@seanmackenziedataengineering]

Awesome! Thanks for checking it out!

[@farinatty]

Very nice. What about a full audit trail. User who did change, old value new value etc.

[@seanmackenziedataengineering]

Good question! The old value will be logged here - if you query for a particular VehicleID in the history table, you will have a list of all previous values for that VehicleID. To get the user, you would need to have some single-row query to identify the current system user. I did an example in this video in tblCurrentUser (could be qryCurrentUser or however your program works: th-cam.com/video/wuyImulb_u4/w-d-xo.html

[@ssdusd]

I like your videos! What is the deference between After Update And After Insert? When After Insert trigger? any Examples?

[@seanmackenziedataengineering]

After Insert occurs once, just after a record is inserted. After Update occurs every time a record is updated. So if you insert and then update a record each day, after insert occurs on the first day, and then after update occurs on every day after.

[@seanmackenziedataengineering]

And, After Insert is the example in this video: th-cam.com/video/wuyImulb_u4/w-d-xo.html

[@aimenbaig6201]

Another question, how do you track two fields instead of one?

[@seanmackenziedataengineering]

You can add more to this macro with another If Update(... and then do an update accordingly.

[@danielazzam]

Hello , You are keeping a history of the last price , which is the same price as in the tblVehicle .
What if I need to keep history of how was the previous price ? There is no before update event here , does before change do the required ?

[@seanmackenziedataengineering]

Good question. The way it works is: if you change the same vehicle 2's price many times, you will see many rows for vehicle 2 in the tblPriceHistory. You can see the order of the prices by looking at the date field. I should have selected prices more times!

[@danielazzam]

​@@seanmackenziedataengineering Hello again . Thank you for your reply , but I guess you did not get my point .
What I meant was what follows : let us consider that price of Mercedes is 6000 , and you changed it to 3000 only once , you will then see 3000 in both the main table and in the price history table . This way the original price (6000) was lost and never captured . This works fine if you want to track price change over time , but never captures the first time a price was changed

[@seanmackenziedataengineering]

You can always know the previous price by looking at the history table. Try it. Before update is not required.

[@seanmackenziedataengineering]

@@danielazzam the first time you change it, it will create a history record, because it was null then became the first price. Then it will also create a record for the second price. So, you will have both in your history table.

[@danielazzam]

Yes you are right , I missed that point, that fields are originally empty , I was testing on existing data in time the price will be null originally
Good day

[@ssdusd]

When we can use "RepaintObject Macro Action"?

[@seanmackenziedataengineering]

If you have a process that "locks" or causes "not responding" so that your screen does not update while it runs, you can insert this action to cause the screen to update instead of staying frozen. It can be a little tricky to implement. One alternative is to pass back control to Windows by using DoEvents. For example, every n iterations of your loop. I did this here: th-cam.com/video/BPT-_H1ir6k/w-d-xo.html

[@carimbo8604]

MacKenzie, would you please show us a tutorial on how to store data that changes along the years? For example, healthcare budget (public and private) over the years. Thank you in advance.

[@seanmackenziedataengineering]

Great idea for a video!