MS Access run code after a row is updated

Question

I am very new to MS Access, forgive me for this simple question but I am very confused with my current problem.

So I want to run a VBA function after a table receives an update on one of its fields. What I have done is:

1. Create a Macro named Macro_update under CREATE->Macro, with action RunCode, and its argument is the VBA function I wish to run. The function has no bug.

2. Select my table, and under Table->After Update, I wrote

    IF [Old].[status]=0 And [status]=1 THEN
    RunDataMacro 
     MacroName Macro_update
   

But after I update my status field in my table nothing happened... I am suspicious of the fact that in step 2 my action is RunDataMacro, but I am actually running a Macro (is there a difference?)... any help is appreciated!

Answer 1

You can use a Data Macro to get it working locally for now. This means that the table will need to be stored in an Access database.

If your web service is not actually using the Access Runtime to interface with the access database container, then the data macros may not fire correctly nor as intended. Your mileage may vary.

If you later migrate your database to a SQL server (MySQL, Microsoft SQL, PostgreSQL) then your data macros will need to be implemented natively on the SQL server as a Trigger.

For now, I'm writing some instructions below to demonstrate how to call a VBA function from a Data Macro locally within a single Access database:

---

Create the VBA Function This will be the function that you want to call from the data Macro.

• Create this in a Module, not in a Form or Class Module.
• This has to be a function and cannot be a sub

Code:

Public Function VBAFunction(OldValue As String, NewValue As String) As String
  Debug.Print "Old: " & Chr(34) & OldValue & Chr(34);
  Debug.Print vbTab;
  Debug.Print "New: " & Chr(34) & NewValue & Chr(34)
  VBAFunction = "Worked"
End Function

---

Create the Data Macro (Going to be more descriptive here since people get lost here easy)

1. Open the Table (i.e. TestTable) in Design View
2. Find the correct Ribbon
   • In table design view, there is a contextual ribbon called Design.
   • On that ribbon, there is an option called Create Data Macros
3. Click on Create Data Macros and select After Update
   • The Macro Designer window should now open
4. Choose SetLocalVar from the Add New Action combo box
   • A SetLocalVar section appears.
   • In this section, I see Name and Expression

5. Set Name to an arbitrary value, such as: MyLocalVar

6. Set Expression to the following

   • Be sure to type the = sign, which will result in two equal signs being shown

Expression Text:

    =VBAFunction([Old].[FieldName],[FieldName])

7. Save the Data Macro and Close the Macro Designer.
8. Save the Table and Close the Table

---

Test It: Create an Update Query

Next you will need to create an Update Query that performs an update on the Table that houses the Data Macro you just created.

To test this, you can just update a string field in all records to one value.

UPDATE [TestTable] SET [TestText] = "Test"

• Run the query

Press Control + G to bring up the Immediate Window. You will notice that the Data Macro fired for every updated record.