MS Access event-driven Data Macro to update table (example)

Question

I have imported some data into Access from Excel. Now I want to write a code similar to trigger which will update another table whenever I get new data from Excel.

I know I cannot write a trigger in Access, so I am trying to use a Data Macro. Can anyone provide me help in how to do this using a data macro?

Answer 1

Let's say that you have a table named [Events] and you are importing data from Excel and appending it to the table

ID  EventName              EventType           EventDate 
--  ---------------------  ------------------  ----------
 1  New Staff Orientation  Training: in-house  2013-06-07
 2  TGIF barbecue lunch    Social              2013-06-14

Say that you also have a table named [EventTypes] to keep track of the categories that can be assigned to events. The [EventType] values coming in from the Excel data need to be approved to avoid unnecessary duplication, typos, etc.. Your [EventTypes] table looks like this

EventType           Added                Approved
------------------  -------------------  -------------------
Training: in-house  2013-06-01 09:15:33  2013-06-01 09:37:16
Social              2013-06-07 10:01:23  2013-06-07 10:22:00

You can create an "After Insert" data macro on the [Events] table to insert new [EventType] values into the [EventTypes] table like this:

    SetLocalVar
            Name  NotFound
            Expression  = True

    Look Up A Record In  EventTypes
            Where Condition  =[EventTypes].[EventType]=[Events].[EventType]

        SetLocalVar
                Name  NotFound
                Expression  = False

    If  [NotFound]  Then

        Create a Record In  EventTypes

            SetField
                    Name  EventTypes.EventType
                    Value  = [Events].[EventType]

            SetField
                    Name  EventTypes.Added
                    Value  = Now()

    End If

Now if you import an Event with a new EventType...

ID  EventName              EventType           EventDate 
--  ---------------------  ------------------  ----------
 1  New Staff Orientation  Training: in-house  2013-06-07
 2  TGIF barbecue lunch    Social              2013-06-14
 3  Bathtub races          Team Building       2013-06-15

...the data macro will automatically add it to the EventTypes table

EventType           Added                Approved
------------------  -------------------  -------------------
Training: in-house  2013-06-01 09:15:33  2013-06-01 09:37:16
Social              2013-06-07 10:01:23  2013-06-07 10:22:00
Team Building       2013-06-11 08:38:37