• Article

Exercise 4: Creating a Data Macro

One of the most exciting new areas of Access 2010 involves the new Macro Designer as well as the new data macros. The new Macro Designer provides developers with a much easier and efficient environment to work in. Data macros meanwhile, provide developers with a way to centralize business logic by attaching macros to table data events. Data macros are similar to triggers in SQL Server. Rather than embed business logic throughout your forms, you can associate Data Macros with tables so that anytime a user updates a table, your business logic executes at the appropriate time.

In Exercise 4, you will get some experience using the new Macro Designer to build a data macro that enforces some business logic on the Job Applicant database.

Task 1 – Create a Macro to limit Offers Outstanding

In task 1 you will create a data macro that helps prevent HR from accidentally making multiple, simultaneous offers for the same position. Specifically, the macro will prevent someone from changing an applicant’s status to Offer Outstanding if there is already an applicant with an outstanding offer for the same position.

  1. Open the Job Applicant database that you used for Exercise 3
  2. Open the Contacts table

  3. Click on the Table tab of the ribbon. Note the Before and After events associated with the table that you can attach data macros to.

    Figure 32

    Table Tab

  4. Click on Before Change
  5. In the drop-down box on the Logic Designer (the design time environment for Macros), select the If item to create a (If…Then) statement. The Before Change event occurs anytime there is a change made to the table – specifically, just before the change is committed. For the needs of this macro, you only need to consider the changes to the table where the record changed contains the value “Offer Outstanding” for the Status. You will use this initial (If…Then) statement to perform this check.
  6. In the If field type St and notice that IntelliSense appears with potential objects of interest. Select the Status item. Notice that the Macro Designer adds the brackets around the name for you.

  7. After [Status] type =”Offer Outstanding”

    Figure 33

    If Statement

  8. In the dropdown under Then, select SetLocalVar from the drop-down box. SetLocalVar allows you to create or modify a local variable. For this macro, you’ll create a local variable named ItemID that stores the ID of a conflicting record (if there is one).
  9. Set the value of the Name argument to ItemID

  10. Set the value of the Expression argument to 0. Later in the macro, you will check the value of ItemID and if it is anything other than 0, you will raise an error.

    Figure 34

    SetLocalVar

  11. Below the SetLocalVar step, select LookupRecord as the next action. Observe that the designer adds a Lookup Record block.
  12. Choose Contacts from the Look Up a Record In drop-down box
  13. For the Where Condition, enter [Status]="Offer Outstanding" and [Contacts].[Position]=[Position] and [Contacts].[ID]<>[ID] - the condition is true when there is an outstanding offer on the same position for a different applicant.

  14. For the Alias argument, enter ExistingOffer

    Figure 35

    Lookup Record

  15. Within the Lookup Record block, add a new SetLocalVar action. The purpose of this step is to record the ID of the conflicting record (if one is found).
    1. Name: ItemID

    2. Expression: [ExistingOffer].[ID]

      Figure 36

      SetLocalVar

  16. The final step is to raise an error if there is a conflicting record. Click on Look Up a Record In on the designer surface to highlight this block of actions. This makes it easier to see the correct location to place the next action. For the next step, you want to use the highlighted drop-down list below.

    Figure 37

    Raise an Error

  17. Below the Lookup Record block but before the End If statement, add a new If action.
  18. Set the Conditional Expression argument to [ItemID]<>0
  19. Within this If block, add a new RaiseError action
    1. Error Number: -5000
    2. Error Description: There is already an offer outstanding for this position
  20. At the very end of the macro, add a new Comment action. This is how you embed comments in the design surface for future reference.
  21. Enter the following comment: This macro ensures that only one applicant per position can have a status of “Offer Outstanding”.

  22. Ideally, this type of comment would be at the beginning of the macro. To do this,

    1. Click outside of the comment so that it is no longer selected
    2. Drag the comment to the top of the designer

    Figure 38

    Completed Data Macro

  23. Close the Before Change macro by clicking Close on the ribbon
  24. Click Yes to save your changes

Exercise 4 Verification

Data macros are helpful because they centralize business logic at the data level rather than duplicate it throughout the UI. To demonstrate this, you’ll verify this Exercise twice – the first time directly on the table and the second time using one of the forms in the database

Verification Task 1 – Verify directly on the table

In order to verify that you have correctly performed all steps of exercise 4, proceed as follows:

  1. In the Contacts table, change the Status of the first two records in the table to Open
  2. Change the Position of the first two records in the table to Sales Manager (or some other Position that you have entered). Scan the table to verify that there is not any Offer Outstanding for this position. Observe that you were able to make these changes without any errors. This demonstrates that the macro is not interfering in places that it should not.
  3. Change the Status of the first record in the database to Offer Outstanding. Observe that you can make this change since it is the only offer outstanding for this position.
  4. Change the Status of the second record to Offer Outstanding. Note that Access does not attempt to commit any changes until you move off the record.

  5. Click on another record in the table. Observe that an error message is generated:

    Figure 39

    Error Message Verification

  6. Click OK to dismiss the error
  7. Change the Status of the record back to Open
  8. For the next verification task, make a mental note of the two names you have been using for this verification.
  9. Close the Contacts table

Verification Task 2 – Verify using a form

  1. Open the Main form
  2. On the left hand side of the form, click on the Name of the applicant whose Status is Open.
  3. Change the Status to Offer Outstanding. Observe that Access does not attempt to commit the change until you move off the record.

  4. Click on a different name on the left hand side of the form. Observe that Access attempts to commit the record and executes the logic in the Before Change event macro.

    Figure 40

    Error Message Verification

  5. Click OK to dismiss the error message
  6. Change the Status back to Open

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents