MS-Access / Getting Started

---

Debugging Data Macros

You're likely to encounter unexpected errors or unintended results when you're designing data macros attached to table events and complex named data macros attached to tables for the first time. You might even be wondering if Access is even executing your data macros at all if you see no visible results. You have several tools available in the Visual Basic Editor for debugging Visual Basic code. Data macros, unfortunately, do not have a rich set of tools available for debugging purposes. You cannot, for example, set breakpoints on data macro logic to halt execution. You also cannot single-step through the macro logic as you can with user interface macros.

The best tools you have for debugging data macro logic are the LogEvent data action and the USysApplicationLog table. You learned previously that Access logs any unexpected errors that it encounters during data macro execution to the USysApplicationLog table. You can write data to this table as well as use the LogEvent data action. The LogEvent data action takes only one required argument-Description.

Earlier in this tutorial, you studied the data macro logic attached to the After Delete event of the tblTerminations table. When you delete a termination record, the After Delete logic looks up the employee's record in the tblEmployees table and sets the Active field back to True. In the LookupRecord data block, we used the Old property to refer to the EmployeeID that Access just finished deleting. Our data macro logic, again, is as follows:

LookupRecord In qryDMTermQuery
 Where Condition = [EmployeeID]=[Old].[EmployeeID]
 Alias
 EditRecord
    Alias
    Comment Block: Now set Active field to True
    SetField
	Name: [qryDMTermQuery].[Active]
	Value: True

 End EditRecord

What would happen, though, if you forgot to use the Old property in the Where condition argument and instead just referenced the value of the EmployeeID field from tblTerminations? Your expression would look like the following:

[EmployeeID]=[tblTerminations].[EmployeeID]

When Access evaluates this expression in the context of the After Delete event, the current value of EmployeeID in tblTerminations is Null because Access already deleted the record. When Access executes the LookupUpRecord data block, it tries to find an employee record in the saved query (the saved query is pulling the EmployeeID from the tblEmployees table) where the EmployeeID is Null. Access does not find such a record and therefore never executes the actions that are inside the LookupRecord block. As a result of not executing those actions, Access does not change the employee's Active field back to True. If you navigate to that employee's record, you can see no change to the Active field. Access is doing exactly what you told it to, but it might not be readily obvious what the problem is, or even if Access executed the data macro, because you're not seeing the results you want and Access is not displaying any errors.

To help debug and find the cause of the data macro logic not returning the results you want, you can take advantage of the LogEvent data action. For this specific example, the first thing that you should verify is whether Access is even running the data macro logic. (This is more important with named data macros rather than with Before and After events.) You can test this by adding a LogEvent as the first action in the After Delete event. So we added a LogEvent to the top of the macro design surface beneath the Comment blocks.

For the Description argument of the LogEvent action, we used a simple message of "Data Macro Started." If you save your changes, create a new termination record, and then delete the termination record, Access creates a new record in the USysApplicationLog table. When you use a LogEvent data action, Access uses an Error Number of 1. In the Description field, you can see the custom message that we entered into the Description argument.

At this point, you at least know Access is firing the After Delete event and starting your data macro logic. The next step in this example is to verify that Access is entering the LookupRecord data block and finding a record. We can test this by adding another LogEvent action as the first action inside the LookupRecord block. For the Description argument, we'll enter another simple message such as "Found Record".

If you save your new changes, create another new termination record, and then delete the termination record, you'll notice that Access creates another new record in the USysApplicationLog table for the first LogEvent, but not the second. This should give you an immediate clue that Access did not enter into the LookupRecord block based on the Where condition expression that you defined. By utilizing the LogEvent data action, you've narrowed down the cause of your issue considerably, and you can then focus your attention on why Access is not finding a record based on your Where condition argument.

The LogEvent action can also be especially helpful when checking the value of local variables and return variables. During the normal execution of data macros, you cannot see the current value of any local or return variables. When debugging data macro logic that involves local and return variables, it can very useful to know what the current value of those variables are at any given time, especially if they are being changed dynamically, for instance, inside a ForEachRecord loop. You can add LogEvent actions throughout your data macro logic and capture the current value of your variables by using expressions such as the following (with your variable names, of course):

=[LocalVariableName]

or

=[ReturnVars]![ReturnVariableName]

After Access finishes executing your data macro logic, you can examine the values of your local variables and return variables in the USysApplicationLog table at different points in time to help determine what Access is doing during the data macro execution. You can use this information to assist with debugging your logic. When you have everything working just the way you want, you can remove the LogEvent actions if you don't want to keep logging information to the USysApplicationLog table.

When Access encounters an error during data macro execution, Access records the error number and error description in the USysApplicationLog table. The error numbers and error descriptions that you see in the USysApplicationLog table might differ if you are working in an unpublished web database versus a web database that you published to a SharePoint server. You should be aware of these differences when testing your data macro logic before and after publishing to a SharePoint server.