Table After Update event macro -- to get started

Question: How can I start a macro for ZIpCode field to run MsgBox "Hello World!"?

I will do more work after we are able to get the macro in place. Now, when the value of zip code changes, for now, it will message "Hello World."

TableAudit.png macro.png
I need help working with the lower part by making the correct selections etc. to make the macro functional.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
I don't think you can trigger code specifically from the ZipCode field this way.... What you are looking at are row-level events, so these would be triggered before or after an entire row is committed to the database.    These macros have limited functionality as they are intended to make updates to some other part of the data.  As such, I don't believe there is a way to call a MsgBox in the way you are asking.

If this were done at the form-level, through a form control's event model, then you could make a call to MsgBox or any other function.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I am sure this feature is added to Access 10 and 13 but I am looking for some help how to get it started. I am very well aware of form events of all kinds and have been using them for over 10 year.

I have ignored macros before but here there is a functionality Microsoft has taken from SQL Server (triggers) and added them to Access and no one seems to know very much about it except the next expert who will show us how it is done.

Mike

Here a video explaining but it is not easy to follow it: https://www.youtube.com/watch?v=wqFYvsemoZo you may want to check it out.
Robert ShermanOwnerCommented:
So, to answer your original question, it is not possible to use the MsgBox function from within the Create/Update Data Macros.   The functions that you can use are very limited and allow you to do basic table operations and comparisons, so that you can establish auditing and/or value adjustments to other tables.

I've put together an example of how you would use this to create an audit table for value changes in a table.   It's pretty basic, but should give you some ideas...  

The file contains two tables.   TableToTrack - just contains two fields, a Primary Key and a numeric field.  If you look at the Table Design for this table, you will see that it contains an AfterUpdate macro that checks to see if an existing record has been updated.   If so, it creates a new record in the 2nd table, TableAudit, which records the primary key of the record that has changed, the name of the field that has changed, the old value, the new value, and the date and time of the change.  

This is all meant to happen behind the scenes, and it does indeed work well.  Developing the macros, however, looks to be a time consuming task, especially if you were to audit many different tables and/or fields.
DataMacroExample.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
This is excellent. In the same file, I have added a new column (FName). for some reason, I hope you would figure out, it is not tracking it.

Could you please take a look at it to see why edits on FName are not tracked?

re:> looks to be a time consuming task, especially if you were to audit many different tables and/or fields.
On a later question, I will ask for solution to install such after update macros using VBA to see how far we can push it.

Thank you,

Mike
DataMacroExample-good.accdb
Robert ShermanOwnerCommented:
Edits on FName are not tracked because the OldValue and NewValue fields in TableAudit are of Numeric data type.   Possible workarounds would be to either change the Audit table so that those value fields accept Text (and then convert any numeric or other types when recording the old and new values) or create additional fields for tracking changes to Text fields, (for example, by adding "OldValue_Text" and "NewValue_Text" fields to TableAudit and setting those fields when dealing with Text.  

For the purposes of an audit log, storing everything as text is probably an acceptable solution.  

Here's a bonus tip:  If you go to the "File" Tab in Access, you will see a new "Application Log" button that will show you any errors that get generated by the Macros.. This helps when trying to debug any errors since the Macros don't provide any direct user prompting when errors occur.

Somehow, I don't think we're going to find much support for creating these macros via VBA...
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Here, with your help, upon update (zipcode for now), I am able to pass oldzip and newzip to an outside function to do with them whatever I want to. See the sample. This means "Hello World!" worked.

Thanks again to get me started.

Mike
DataMacroExample-rev1.accdb
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.