Data Macros in Microsoft Access

In this episode, we're going to take an introductory look at Data Macros in our MS Access database. Data macros perform a similar function to "Triggers" in other database management systems, and they do that by allowing users to execute updates when a record is inserted, or when a record is updated, or other events. For example, when a price is updated in one table, that price update can be logged in another table for a price history. In today's example, we'll log an event record each time a new case management record is added to a database, putting the current user's name on the record in the process.
Related Videos:
How to Use Data Macros to Track Changes to Field Entries in MS Access
• How to Use Data Macros...
Data Macros in Microsoft Access
You are watching this video now!
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.co...
Want the code from this video?
mackenziemacken...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess

[@siclucealucks]

You are the only channel in my list where I have hit the bell icon :) Great Content

[@seanmackenziedataengineering]

Wow, thanks!

[@garythedo]

Thanks for these great videos! Hope that sometime you can do a video on migrating tables/backend to SharePoint. Thanks again!

[@seanmackenziedataengineering]

Great suggestion! Thanks!

[@imstevemcqueen]

Excellent as usual

[@seanmackenziedataengineering]

Thanks!

[@SHCSBaker]

Great video! I'm thinking about changing my existing Audit Log to use this method. However I don't want the data macro to fire for every change, I think this could be addressed using a If Updated("myField") then RunDataMacro. I can't find many examples of how to use RunDataMacro, could you explain how this method works?

[@seanmackenziedataengineering]

Good idea - you can use If Updated for this purpose. I’ll cover more like this in upcoming videos! Including RunDataMacro

[@lorenzogaruglieri7046]

hi Sean. Congratulations on your videos. Question: is it possible through DataMacros to perform a personal function contained for example in a standard module? Thank you Lorenzo

[@seanmackenziedataengineering]

Yes, you can do it. One way is to use SetLocalVar and setting the value to your VBA function. However, this crosses the line between the top and bottom layers of the application as I described in the beginning of the video. If you do it, it may sometimes break. Data Macros will trigger anytime data is updated, because they are tied to the ACE engine. If another program or script updates data, Data Macros will still trigger, but if you put a VBA function in there that process may have an error because your app is not running at the time data is updated. There can also be issues as to where your VBA resides, if the database is split, etc.

[@handsome4819]

That's really good. Thanks 👍

[@seanmackenziedataengineering]

Glad you enjoyed it!

[@999juanrivera1]

In your video Sam did the update but in the log John was entered. ho can this be corrected to get the correct user name?

[@seanmackenziedataengineering]

In our example, we pretend that we made John Doe log in to our app, and then we stored his name in tblCurrent while he uses the app. He is entering new cases for the other Case Workers, like a secretary.

[@999juanrivera1]

@@seanmackenziedataengineering OK got it. Thanks

[@gerfer6261]

🎉🎉🎉

[@seanmackenziedataengineering]

Thanks!

[@tonykutch4877]

Great video! I'm a database guy from long ways back (try DBASE II on a Radio Shack TRS-80) and finally getting back into Access. Do most of my work directly on the back end, so I'm thinking data macros may provide a solution for my current project, though I'm not sure how complex the final solution will be. I've got an inventory of components, purchased from multiple vendors on multiple invoices. I already have a query set up to list individual component costs and quantities per invoice. My goal is to accumulate the total costs of the components used in an end item (always the same list of components) and update the component inventory as each end item is completed. Essentially looking to manage inventory use on a FIFO basis. Any suggestions you might have would be greatly appreciated. Thanks, again, for the great videos.

[@seanmackenziedataengineering]

Oh man, TRS-80! That was my computer when I was a kid. Dungeons of Daggorath in black and white 3D wireframes.. good times. On your project, you could definitely use a status change trigger. If the order changes status to complete, execute your query with all the build component counts to reduce inventory by those counts for each component. On the event/trigger you can capture the order or assembly ID then use that ID as a parameter in your update query, updating all of them in one shot. Interesting project!

[@fernandolucas2435]

Excellent!! How can I know the current user login in the FE?

[@seanmackenziedataengineering]

In this example, we pretend that we created our own login system that stored the current user in tblCurrent. I will make a video on how to get the Windows login too, as this has been asked several times recently! cheers!

[@siclucealucks]

I have tried to use Environ("UserName") Instead of CurrentUser but unfortunately it seems that Environ functionset is not available for macros.

[@seanmackenziedataengineering]

Great question. I will do another video to show how to get the *Windows* username. In this example, we pretend that we are building an app. We made John Doe log in, using our own login form, and he is making appointments for the other Case Workers. When he logged in, we stored his name in tblCurrent.

[@siclucealucks]

@@seanmackenziedataengineering Thats a great video Idea

[@jot2243]

Hi Sean. I was trying to change the form to launch my MS-Access to a particular form (In the Access option, Current Database Tab) but each time and I did that, I got this warning message "The value of a Display Form property was invalid and was not saved" and it returns the value to none even though I have done it countless times before now. It won't change even if I open it again and change it to the form I want. I need your help to resolve this issue as I am stuck.

[@seanmackenziedataengineering]

You probably need to open your database in exclusive mode. Someone (even you perhaps) may have another database lock open on the file. Open Access > Open > Browse > in the dropdown next to Open, choose Open Exclusive If you can't, you may need to ask others to close the file or restart your machine to remove the lock. Great question!

[@golfmbavu4285]

Bonjour, je suivis votre tutoriel avec intérêt mais comment apprendre beaucoup plus en français

[@seanmackenziedataengineering]

Merci d'avoir regardé ma chaîne ! KZbin fournit-il de bons sous-titres ?

[@vholas]

Very useful! After Update and After Insert events work for me, but if I use After Delete nothing happens. What am I doing wrong? Thanks

[@seanmackenziedataengineering]

It is possible that what you try to do after the delete gets an error. Perhaps you refer to the deleted record or something like that. What does your After Delete macro do?

[@vholas]

@@seanmackenziedataengineering I want to store in the LOG table information about inserted/changed/deleted records from the DATA table. If I use the code Value = [DATA].[ID] in the After Insert or After Update event, it works, but if I use it in the After Delete event, then a new record is not created in the LOG table. No error will appear...

[@vholas]

I want to write to the LOG table the insertion/change/deletion of a record from the DATA table. I use the expression Value = [DATA].[ID]. It works for both After Insert and After Update events, but not for After Delete. No record will be created, no error will appear...

[@seanmackenziedataengineering]

@@vholas it is likely because you refer to a value that is already deleted when the macro runs. You might try Before Delete instead so you can use the value before it is deleted. Let’s see how it goes 🛠️

[@vholas]

There is no CreateRecord action available for the Before Delete event. I also tried accessing the previous value in a field by using syntax [Old].[Field Name] , but this doesn't work either.

[@josephdaquila2479]

Can you run a python script on an an event?

[@seanmackenziedataengineering]

You can from a VBA event. See this example kzbin.info/www/bejne/aoWZooKlpceqma8

[@josephdaquila2479]

@@seanmackenziedataengineering got it. Now can I chain data macro to vba macro to python?

[@interestingamerican3100]

Holly &^$*%&! You can do that in Access?!?!

[@seanmackenziedataengineering]

You bet! Super cool feature, making Access even more powerful!