So far in this chapter, you’ve been studying data macros attached to specific table events. Access 2010 also supports creating named data macros. A named data macro is a data macro attached to a table, but not to a specific event. Named data macros execute only when called from another data macro, a user interface macro, or from Visual Basic code. Logic that is in a named data macro can interact with data in any table, require parameters before executing, and return data to the calling data macro, user interface macro, or Visual Basic code procedure. You can attach named data macros to any local client table or web table. You could, for example, attach all named data macros to one table or attach different named data macros to different tables based on their purpose. The Back Office Software System sample web database includes more than a dozen named data macros attached to various tables. In the next sections, you’ll explore some examples of these named data macros, as well as create a new named data macro.
In the Back Office Software System sample data copy web database (BOSSDataCopy.accdb), a table called tblErrorLog is used to record any application errors that might occur during the operation of any client objects in this hybrid application. The database administrator can monitor this table for any errors occurring in the operation of the database and then analyze the information recorded in the fields for debugging purposes. The database administrator would like an easy method to delete all the records in this error table after examining the errors and fixing the underlying issues. We can create a named data macro for this purpose, which can then be called from other areas of the application. To create a new named data macro for this purpose, we’ll attach it to the tblErrorLog table for easy reference. Open the BOSSDataCopy.accdb sample web database, and then open the tblErrorLog table in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group and then click the option called Create Named Macro, as shown in Figure 7-43.
Figure 7-43. Click the Create Named Macro option under the Named Macro button to start creating a new named data macro attached to the tblErrorLog table.
Access opens the Logic Designer with an empty macro design surface, as shown in Figure 7-44. You’ll notice one difference immediately on the macro design surface that you did not see when creating data macros attached to table events in the preceding sections. At the top of the macro design surface, you can see a section called Parameters. Named data macros allow you to create parameters, which you can use to pass information into the data macro. Creating parameters for named data macros is optional, but Access always displays the Parameters block at the top of the macro design surface whenever you are working with named data macros. (We’ll explore parameters for named data macros later in this chapter.) The list of program flow constructs, data blocks, and data actions that you can use in named data macros is the same for After events except with the addition of one more data action called SetReturnVar. (We’ll discuss the SetReturnVar action later in this chapter.) See Table 7-3 if you want to review the list of elements available in After events.
Figure 7-44. When you create named data macros, Access displays a Parameters block at the top of the macro design surface.
Let’s first add a Comment block to this named data macro to document its purpose. Drag a Comment block from the Action Catalog onto the macro design surface. Enter the following text into the new Comment block:
Loop through all records in the client error table and delete them.
To delete all the records in the tblErrorLog table, we need to use the ForEachRecord data block. Drag a ForEachRecord data block from the Action Catalog to beneath the Comment block, or select ForEachRecord from the Add New Action box at the bottom of the macro design surface. Access creates a new ForEachRecord block, as shown in Figure 7-45.
The ForEachRecord data block takes three arguments:
For Each Record In. Required argument. A name of a table, query, or SQL statement to look up a record in.
Where Condition. Optional argument. An expression that Access uses to select records from the table, query, or SQL statement.
Alias. Optional argument. A substitute or shorter name for the table.
You’ll notice in the lower-right corner of the ForEachRecord data block there is an Update Parameters link. If you are using a query for your data source that includes parameters, you can update the parameters using this link. We’ll discuss using parameters with named data macros later in this chapter.
Inside Out: Creating Records in a ForEachRecord Block
You cannot use the CreateRecord action inside a ForEachRecord block in Access 2010. This is a design limitation that prohibits you from creating many records at one time. You can, however, work around this limitation by using a combination of a ForEachRecord block and an After Update event.
Both the tblLaborPlanDetails and tblSchedule tables in the Back Office Software System web database have an After Update event that checks to see if a Boolean field in the table is True. If the field is True, the After Update event runs a CreateRecord action to create new records in the tblSchedule table. We use a named data macro to loop through the records that we want to copy in the tblSchedule or tblLaborPlanDetails tables and set those Boolean fields to True. The After Update events fire and create new records. We then execute another named macro to reset the Boolean field back to False for all the records that we previously updated. You can study the After Update events and named data macros attached to tblLaborPlanDetails and tblSchedule to see examples of this functionality.
Click the drop-down list on the For Each Record In argument and select the tblErrorLog table. We don’t need to provide anything for the Where condition or Alias arguments because we don’t need to restrict our logic to a subset of the records in the tblErrorLog table; we want to delete all the records. Now, tab into or click the Add New Action box just beneath the Alias argument and then select the DeleteRecord action from the box. Access creates a DeleteRecord action inside the ForEachRecord block, as shown in Figure 7-46.
The DeleteRecord action only has one argument—Alias—and it’s an optional argument. We don’t need to provide an alias for this example.
That’s all the logic we need in this named data macro to delete all the records in the tblErrorLog table. When Access executes this named data macro, it comes into the ForEachRecord data block, finds the first record in the table if any exist, and then deletes the record. Access then repeats the loop by going to the next record in the table and deleting that record as well. Access continues this process until it cannot find any more records in the tblErrorLog table. If there were actions following the ForEachRecord loop, Access would execute those actions after it deleted the last record in the table.
You’ve completed creating your first named data macro, but now you need to save it and give it a name. Unlike data macros attached to table events, named data macros require unique names. To save your new named data macro, click the Save button in the Close group on the Design contextual tab, or click the Save button on the Quick Access Toolbar. Access opens the Save As dialog box, as shown in Figure 7-47. Save the named data macro with the name ClearLogsTest.
Each named data macro attached to a table must have a unique name. You are allowed to have named data macros with the same if they are attached to different tables. If you attempt to save a named data macro with the same name as a named data macro attached to the same table, Access displays a warning message, as shown in Figure 7-48. If you click Yes, Access overwrites the existing named data macro with the new logic that you just defined.
We mentioned earlier that named data macros must be called for Access to execute them. If you want to test out a named data macro, you must therefore call a RunDataMacro action from a table event, a user interface macro, or from Visual Basic. In Chapter 21, you’ll learn how to call named data macros from user interface macros, and in Chapter 25, “Automating Your Application with Visual Basic,” (on the companion CD) you’ll learn how to call named data macros from Visual Basic. For simplicity of this example, we’ll create a data macro attached to a table event that will trigger the named data macro to execute.
Close the tblErrorLog table, if you still have it open, and then open the tblAppointments table in Datasheet view. Let’s call the new named data macro you created to run whenever you insert a new record into the tblAppointments table. Click the Table contextual tab and then click the After Insert button in the After Events group to open the Logic Designer. To call the named data macro to run, you need to use the RunDataMacro action. By now, you should be very familiar with the different techniques for adding a new data action to the macro design surface. In this case, we’ll do something different because we are calling a named data macro. In the Action Catalog, expand the In This Database node near the bottom of the screen. Next, expand the Tables node beneath In This Database, and then expand the tblErrorLog node to see the named data macros attached to this table, as shown in Figure 7-49.
Figure 7-49. Expand the In This Database node in the Action Catalog to see a list of all the named data macros in the database.
Now, drag the ClearLogsTest named data macro that you created in the previous section onto the macro design surface. Access adds a new RunDataMacro data action to the macro design surface and also fills in the required Macro Name argument for you, as shown in Figure 7-50. The Macro Name argument requires the name of the table and the name of the named data macro separated by a period. Access also provides a drop-down list for this argument where you can select from a list of all the named data macros attached to the tables in your database.
Figure 7-50. Access creates a new RunDataMacro action with the Macro Name argument filled in when you drag a named data macro on to the macro design surface.
To test out the named data macro, save the changes to this After Insert event and then create a new record in the tblAppointments table. After you insert a new record into the table, Access executes the RunDataMacro action and then deletes all the records in the tblErrorLog table. You can verify this by opening the tblErrorLog table and seeing that no records exist in the table.
When you need to rename or delete named data macros, or even delete data macros attached to table events, Access provides a useful tool to assist you. Open the tblErrorLog table in Datasheet view, click the Table contextual tab, click the Named Macro button in the Named Macros group, and then click Rename/Delete Macro from the drop-down list, as shown in Figure 7-51.
Access opens the Data Macro Manager, as shown in Figure 7-52. The Data Macro Manager displays a list of all the tables in your database that have data macros attached to table events, as well as all named data macros. You can expand the size of the Data Macro Manager by clicking the corner of the dialog box and dragging the window to a larger size. Beneath each table name, Access lists each table event or named data macro on a separate line in the Data Macro Manager. For table events, Access lists the name of the event, the words Event Data Macro, and a Delete link. For named data macros, Access lists the name of the named data macro, the words Named Data Macro, a Rename link, and a Delete link.
If you want to rename a named data macro, click the Rename link next to the named data macro that you want to rename. Access unlocks the name of the named data macro so you can change it, as shown in Figure 7-53. Access immediately saves the new name when you click away from the name box or click the Close button.
We don’t need the named data macro we created in the previous section because it duplicates logic functionality we already have in the ClearErrorTable named data macro. To delete the test named data macro that you created previously, click the Delete link next to the ClearLogsTest named data macro. Access displays a confirmation message, as shown in Figure 7-54. If you are deleting a named data macro attached to a table that you currently have open, you also need to save the changes to the table after you close the Data Macro Manager. Click Yes to delete the ClearLogsTest named data macro.
So far in this chapter, all the data macros you’ve created and tested executed without problems. In a working application used by many users, however, it’s quite possible that Access can and will encounter errors executing data macros. Access can also run into errors while you are in the development phase of creating, testing, and debugging your data macros. Access manages any errors it encounters executing data macros through a special system table called USysApplicationLog. This special table serves three purposes:
Access uses it to log any data macro failures that it encounters while executing data macros attached to table events and named data macros.
You can use the table for debugging purposes when designing and testing data macros by utilizing the LogEvent data action.
Access uses this table to record any compilation errors when publishing or synchronizing web objects to a SharePoint server running Access Services. (You’ll learn about publishing and synchronizing with Access Services in Chapter 22.)
In the previous section, you deleted the test named data macro called ClearLogsTest attached to the tblErrorLog table. In the After Insert event of the tblAppointments table, we still have a RunDataMacro action that calls the now deleted ClearLogsTest named data macro. If we add a new appointment record to the tblAppointments table, Access attempts to run a named data macro that no longer exists. Let’s try this out and see how Access responds to this problem. Open the tblAppointments table in Datasheet view, create a new appointment record, and then save the record. You’ll notice that Access does not display any error dialog boxes or messages to you, even though we know that it must have encountered a problem. Access does, however, provide you a small error indication message in the status bar. If you look at the lower-right corner of the status bar, you can see the words “New Application Errors,” as shown in Figure 7-55.
Figure 7-55. Access displays a message in the status bar when it encounters an error while executing data macros.
If you hover your mouse over New Application Errors in the status bar, Access displays a tooltip explaining that there are new errors in the USysApplicationLog table. These words in the status bar also serve as an entry point to open this error table. Click New Application Errors, and Access opens the USysApplicationLog table in Datasheet view, as shown in Figure 7-56.
The USysApplicationLog table contains the following fields: SourceObject, Data Macro Instance ID, Error Number, Category, Object Type, Description, Context, and Created. You cannot see all the information in this table in Figure 7-56, so we’ve reproduced the information in Table 7-4.
Table 7-4. USysApplicationLog Table Results
Field Name | Data |
|---|---|
SourceObject | tblAppointments.AfterInsert |
Data Macro Instance ID | {D76F9C0A-910E-402D-B20E-7BC802F11274} |
Error Number | -8979 |
Category | Execution |
Object Type | Macro |
Description | The data macro ‘tblErrorLog.ClearLogsTest’ could not be found. |
Context | RunDataMacro tblErrorLog.ClearLogsTest |
Created | 3/1/2010 8:23:14 PM |
The SourceObject field lists the name of the table and the specific event where Access encountered an error. In our example, you can see that Access logged an error for the AfterInsert event attached to the tblAppointments table. The Data Macro Instance ID is the unique ID that Access uses to track the execution of the data macro. The Error Number is the specific internal error number that Access uses to record errors while executing data macros. In the Category field, you can see Access lists this error as an Execution error. (In Chapter 22, you’ll learn that Access also writes compilation errors to this USysAppicationLog table when you publish a web database to a SharePoint server running Access Services.) The Object Type field indicates the specific type of object where Access encountered an error; in our example, it was a macro. In the Description field, Access lists more specific information about the nature of the issue that it encountered. Access informs you that it could not find the ClearLogsTest data macro attached to the tblErrorLog table. Access lists the specific context where it hit an error in the Context field. Access gives you information that it was trying to execute a RunDataMacro action that calls the ClearLogsTest named data macro attached to the tblErrorLog table. The last field, Created, lists the date and time when Access hit the error.
By default, there is no USysApplicationLog table when you create a new client or web database. Access creates this error table the first time that it encounters errors during data macro execution or if you use the LogEvent data action in a data macro. (You’ll see examples of the LogEvent data action later in this chapter.) If the USysApplicationLog table already exists in your database, Access appends new records to the table instead of creating an additional table. If you delete this table from the Navigation pane, Access recreates another table when it needs to log an error. You can also access the USysApplicationLog table at any time from the Backstage view. Click the File tab on the Backstage view, click Info, and then click the View Application Log Table button, as shown in Figure 7-57.
Figure 7-57. Click the View Application Log Table button on the Backstage view to open the USysApplicationLog table.
Access does not display the USysApplicationLog table in the Navigation pane by default because the table starts with the prefix USys. To see user-defined system tables—tables that start with the prefix USys—you need to change your navigation options to display system objects. To do this, right-click the menu bar at the top of the Navigation pane and then click Navigation Options on the shortcut menu. Access opens the Navigation Options dialog box, as shown in Figure 7-58.
Under Display Options, select Show System Objects, and then click OK. You’ll notice that Access now displays the USysApplicationLog table in the Navigation pane, as shown in Figure 7-59. You’ll also notice that Access now displays another table with the USys prefix—USysRibbons—as well as several tables that start with the MSys prefix. The USysRibbons table holds the data needed to create custom ribbons for the Back Office Software System sample web database. (You’ll learn all about creating custom ribbons in Chapter 26, “The Finishing Touches,” on the companion CD.) Tables that start with the MSys prefix are system tables created by Access to control your database application.
Caution
You should not attempt to modify any tables that start with the MSys prefix; you might cause irreparable damage to your database!
Let’s change the navigation options back to what they were so we don’t see all the extra Access system tables. Right-click the menu bar at the top of the Navigation pane and then click Navigation Options on the shortcut menu. Clear the Show System Objects option on the Navigation Options dialog box and then click OK.
You should now delete the After Insert event that you created earlier for the tblAppointments table. Remember, we deleted the ClearLogsTest named data macro, which means Access will continue to hit errors whenever you add new records to the tblAppointments table. To do this, open any table in Datasheet view, click the Table contextual tab, click the Named Macro button in the Named Macros group, and then click Rename/Delete Macro from the drop-down list to open the Data Macro Manager again. Click the Delete link next to the After Insert event under the tblAppointments table and then close the dialog box.
Inside Out: Access Logs Save Conflicts to USysApplicationLog Table
When Access attempts to commit data using the SetField action inside an EditRecord block, it could encounter a save conflict because someone else is editing the data in the same record. If this happens, Access retries the SetField action up to five times. If Access fails to commit the requested changes on the fifth attempt, Access logs an entry into the USysApplicationLog table indicating that it encountered a save conflict and could not commit the data. If you have data macro logic attached to table events and named data macros in your application, we recommend that you periodically check the USysApplicationLog table for any types of errors, including save conflicts.
In named data macros, you can define parameters to pass in information to the named data macro and use them in the data blocks and data actions. With parameters, you can pass in information to the named data macro from other data macros, forms, user interface macros, or Visual Basic. In the Back Office Software System sample web database, many of the named data macros attached to the web tables include parameters. Let’s study an example that uses dates for parameters. Open the tblSchedule in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group, click Edit Named Macro in the drop-down list, and then click DeleteDateRangeRecords, as shown in Figure 7-60.
Figure 7-60. Click DeleteDateRangeReocrds to view the named data macro attached to the tblSchedule table.
Access opens the Logic Designer and displays the logic that we created for this named data macro, as shown in Figure 7-61.
The logic for the DeleteDateRangeRecords named data macro is as follows:
Parameter Name: ParamStartDateDelete
Parameter Description: Beginning date to delete records
Parameter Name: ParamEndDateDelete
Parameter Description: Ending date to delete records
Comment Block: This named data macro deletes all records in tblSchedule within a spe-
cific date range. The start and end dates for the range are given by parameters.
ForEachRecord In tblSchedule
Where Condition = [ScheduleDate]>=[ParamStartDateDelete] And
[ScheduleDate]<=[ParamEndDateDelete]
Alias
DeleteRecord
Record AliasAt the top of the macro design surface, you can see two parameters that we created. You must provide a name for each parameter that you create, up to 64 characters in length, but the description is optional. We recommend that you enter a description for each parameter that you create, for documentation purposes. To create a parameter for a named data macro, click the Create Parameter link in the upper-right corner of the Parameters block. Access adds an additional parameter line in the Parameters block. Figure 7-62 shows you an example of adding a new parameter to this named data macro. To remove a parameter, click the Delete button next to the parameter line.
Figure 7-62. Click the Create Parameter link when you need to define new parameters for named data macros.
This named data macro deletes all records in the tblSchedule table, but only within a specific date range. The date range is determined by the two date parameters. In the Where condition argument for the ForEachRecord data block, we use these parameter values just like table fields by adding brackets around them. Our Where condition is as follows:
[ScheduleDate]>=[ParamStartDateDelete] And [ScheduleDate]<=[ParamEndDateDelete]
By using parameters, this named data macro can be very flexible because we are not hard-coding specific dates into the logic. We can use this named data macro at any time in the application by passing in any date range, and Access takes care of the work by deleting only the records in the tblSchedule table within that date range. In Chapter 21, you’ll learn how to call named data macros from forms and pass in parameters from data in form controls.
You can use a local variable in data macros to store a value that can be used throughout the execution of the data macro. Local variables are very useful when you need Access to calculate values during the execution of the data macro or remember something for later use in the data macro. You can think of a local variable in a data macro as writing yourself a note to remember a number, a name, or an email address so that you can recall it at a later time in the data macro. All local variables have a unique name. To fetch, set, or examine a local variable, you reference it by its name. Local variables stay in memory until the data macro finishes executing, you assign it a new value, or until you clear the value.
Let’s examine a named data macro that uses a local variable so you can understand how this works. Open the tblInvoiceHeaders table in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group, click Edit Named Macro in the drop-down list, and then click VerifyInvoiceBalanced. Access opens the Logic Designer and displays the logic that we created for this named data macro, as shown in Figure 7-63.
Figure 7-63. This named data macro uses a local variable to calculate a total of invoice detail items.
The logic for the VerifyInvoiceBalanced named data macro is as follows:
Parameter Name: ParamInvoiceNumber
Parameter Description: Invoice Number to use
Comment Block: This named data macro will check to see if a specific invoice is
balanced. It checks to see if the invoice amount total matches the total from the
invoice detail line items. If they match, the invoice is marked as balanced. If the
totals do not match, the invoice is marked as not balanced.
Comment Block: Set a local variable to calculate the running sum of the detail record
amounts.
SetLocalVar
Name: VarRunningTotal
Expression: 0
Comment Block: Do a loop through all the related detail records for the specific
invoice. For each record, add the report group amount to the running total.
ForEachRecord In tblInvoiceDetails
Where Condition = [InvoiceID]=[ParamInvoiceNumber]
Alias
SetLocalVar
Name: VarRunningTotal
Expression: [VarRunningTotal]+[tblInvoiceDetails].[ReportGroupAmount]
Comment Block: Now, look up the corresponding invoice record in the table tblInvoice-
Headers.Compare the running total amount to the saved invoice amount. If they match,
mark the IsBalanced boolean field as True (balanced). If they don't match, mark it as
False (unbalanced).
LookupRecord In tblInvoiceHeaders
Where Condition = [InvoiceID]=[ParamInvoiceNumber]
Alias
EditRecord
Alias
If [VarRunningTotal]=[tblInvoiceHeaders].[InvoiceAmount] Then
Comment Block: Invoice is balanced.
SetField
Name: tblInvoiceHeaders.IsBalanced
Value: True
Else
Comment Block: Invoice is not balanced.
SetField
Name: tblInvoiceHeaders.IsBalanced
Value: False
End If
End EditRecordAt the beginning of this named data macro, we use the SetLocalVar action to define a new local variable. The SetLocalVar action takes two arguments:
Name. Required argument. The name of the local variable.
Expression. Required argument. The expression that Access uses to define the local variable.
We named our local variable VarRunningTotal and initially set it to a value of zero. Inside the ForEachRecord data block, Access loops through each record in the tblInvoiceDetails table where the InvoiceID equals the InvoiceID passed in as a parameter. Within the ForEachRecord data block, we use another SetLocalVar with the same name; however, we use the following expression in the Expression argument:
[VarRunningTotal]+[tblInvoiceDetails].[ReportGroupAmount]
With this expression, Access adds the existing current value of the local variable to the amount in the ReportGroupAmount field of the tblInvoiceDetails table. When Access finds the first record match, it adds 0 to the amount in the ReportGroupAmount field. On each subsequent loop through the ForEachRecord data block, Access keeps a running sum of the total amount of invoice line items. In the second half of the named data macro, we use the LookupRecord data block to look up the main parent invoice record stored in the tblInvoiceHeaders table with the given InvoiceID parameter. We then have an If condition block that compares the completed running sum total stored in our local variable with the InvoiceAmount field in the tblInvoiceHeaders table. If the amounts are equal, we know that the invoice is balanced with its child detail record amounts. Finally, if the invoice is balanced, we set the Boolean IsBalanced field to True, and if it is not balanced, we set the field to False. You’ll find working with local variables can be very useful when defining complex business logic at the data layer.
Inside Out: Committing Data in Nested Data Blocks
If you are nesting the ForEachRecord or LookupRecord data block, you can only use the SetField data action to write data to the outermost block. You can read values from tables or queries within the nested blocks, but you can write data only to the outermost block.
You can use a return variable in data macros to return data to the object that called the named data macro. In a sense, you can think of a return variable as the opposite of a parameter. You use parameters to push data into a named data macro, and you use return variables to pull data out of named data macros. Return variables are very useful when you need Access to read values from a table or query during the execution of the named data macro and perhaps perform different steps based on that value. Return variables can even be returned from the data layer up to the user interface level. All return variables have a unique name. To fetch, set, or examine a local variable, you reference it by its name. Return variables stay in memory until the data macro finishes executing, you assign it a new value, or until you clear the value. You can only set return variables in named data macros; however, you can retrieve them from After events, other named data macros, user interface macros, and Visual Basic.
Let’s examine a named data macro that uses return variables so you can understand how this works. Open the tblSettings table in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group, click Edit Named Macro in the drop-down list, and then click GetCurrentValue. Access opens the Logic Designer and displays the logic that we created for this named data macro, as shown in Figure 7-64.
Figure 7-64. The GetCurrentValue named data macro uses return variables to return data to the caller.
The logic for the GetCurrentValue named data macro is as follows:
Parameter Name: ParamValue
Parameter Description: What field value to return
Comment Block: This named data macro gets the current value of a field value in this
table based on a parameter and returns that back to the caller.
LookupRecord In tblSettings
Where Condition
Alias: TS
If [ParamValue]="Version" Then
Comment Block: Set ReturnVar to current value of Version field
SetReturnVar
Name: RVVersion
Expression: [TS].[Version]
Else If [ParamValue]="Range" Then
Comment Block: Set ReturnVar to the current value of RangeLimit field
SetReturnVar
Name: RVRange
Expression: [TS].[RangeLimit]
Else If [ParamValue]="Available" Then
Comment Block: Set ReturnVar to the current value of SiteAvailable field
SetReturnVar
Name: RVAvailable
Expression: [TS].[SiteAvailable]
Else If [ParamValue]="LogEventEmail" Then
Comment Block: Set ReturnVar to the current value of
LogEventsForMissingEmailAddress field
SetReturnVar
Name: RVLogEventEmail
Expression: [TS].[LogEventsForMissingEmailAddress]
Else If [ParamValue]="SendEmailOnError" Then
Comment Block: Set ReturnVar to the current value of the
SendEmailForAppErrors field
SetReturnVar
Name: RVSendEmailOnError
Expression: [TS].[SendEmailForAppErrors]
Else If [ParamValue]="AdminEmail" Then
Comment Block: Set ReturnVar to the current value of the AdminEmailAddress field
SetReturnVar
Name: RVAdminEmailAddress
Expression: [TS].[AdminEmailAddress]
Else If [ParamValue]="AllEmailInfoForErrors" Then
Comment Block: For this parameter value, send back the settings for both the
SendEmailOnError and AdminEmailAddress fields so the
caller doesn't need to make two trips.
SetReturnVar
Name: RVSendEmailForError
Expression: [TS].[SendEmailForAppErrors]
SetReturnVar
Name: RVAdminEmailForErrors
Expression: [TS].[AdminEmailAddress]
End IfThe tblSettings table holds application-specific settings in several fields. By storing these settings in the table, we can then use data macros to retrieve these values at any time. The GetCurrentValue named data macro uses a large If block inside a LookupRecord data block. The If/ElseIf conditions check the value of the parameter ParamValue being passed in from the caller. We then use the SetReturnVar data action to define a new return variable. The SetReturnVar action takes two arguments:
Name. Required argument. The name of the return variable.
Expression. Required argument. The expression that Access uses to define the return variable.
We set a unique name for each return variable inside the various ElseIf condition blocks. For the Expression argument of each SetReturnVar action, we use an alias of the table name and read the data from a specific field. In the last ElseIf condition block, we return data from two fields with two different return variables to save the caller from having to make two RunDataMacro calls for related application settings. You could optionally create a named data macro that returns all data from the fields with return variables in one call, but we didn’t want to be passing around data when it would not be needed. By itself, this named data macro does not do anything more than read values from the tblSettings table. However, the real power of the return variables is the ability of the object calling this named data macro to use these values.
To see how this data in return variables can be used, close the Logic Designer for this named data macro and then close the tblSettings table. Now, open the tblErrorLog table in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group, click Edit Named Macro in the drop-down list, and then click LogError. Access opens the Logic Designer and displays the logic that we created for this named data macro, as shown in Figure 7-65.
This named data macro is quite lengthy, so we’ll break up our discussion of the logic behind this named data macro into two parts. The logic for the first part of the LogError named data macro is as follows:
Parameter Name: ParamModule Parameter Description: Module Name Parameter Name: ParamProcedure Parameter Description: Procedure name Parameter Name: ParamErrorNumber Parameter Description: Error Number Parameter Name: ParamErrorString Parameter Description: Error description Parameter Name: ParamUser Parameter Description: User name Parameter Name: ParamTime Parameter Description: Time of error Comment Block: Log an unexpected error while running client objects from within Access. Write this information to the custom client error logging table. CreateRecord In tblErrorLog Alias: SetField Name: tblErrorLog.Module Value: [ParamModule] SetField Name: tblErrorLog.ErrorNumber Value: [ParamErrorNumber] SetField Name: tblErrorLog.ErrorString Value: [ParamErrorString] SetField Name: tblErrorLog.Procedure Value: [ParamProcedure] SetField Name: tblErrorLog.ErrorTime Value: [ParamTime] SetField Name: tblErrorLog.UserID Value: [ParamUser]
The LogError named data macro includes six parameters. We pass in all six of these values to record any unexpected application errors with the client objects. We then use the CreateRecord block to create a new record in the tblErrorLog table. Inside the CreateRecord block, we use the SetField action and pass in the data from the parameters into the appropriate fields.
In Figure 7-66, you can see the second half of the LogError named data macro. Note that in Figure 7-66, we collapsed the Parameters block so you can see the rest of the logic.
Figure 7-66. This is the second half of the LogError named data macro attached to the tblErrorLog table.
The logic for the second half of the LogError named data macro is as follows:
Comment Block: Get the ID of the record we just created and store it in a local
variable.
SetLocalVar
Name: LVNewRecordID
Expression: [LastCreateRecordIdentity]
Comment Block: Check the tblSettings table to see if an e-mail should be sent to the
administrator when a new client application error occurs.
RunDataMacro
Macro Name: tblSettings.GetCurrentValue
Parameters
ParamValue: "AllEmailInfoForErrors"
If [ReturnVars]![RVSendEmailForError]=True Then
Comment Block: Send an e-mail to the administrator. In the body of the message,
list the ID of the record number that got added to the table using
LastCreateRecordIdentity.
SendEmail
To: =[ReturnVars]![RVAdminEmailForErrors]
CC:
BCC:
Subject: Unexpected error occurred during application execution.
Body: ="BO$$ application experienced an unexpected error during client execution.
Please review error logs for more information. The record ID of this new
entry is " & [LVNewRecordID] & " in the client error log table."After the CreateRecord block is complete, Access immediately commits that record to disk. We then use the SetLocalVar action to create a new local variable. For the expression argument, we use the LastCreateRecordIdentity property. The LastCreateRecordIdentity property returns the ID value of the last record that Access just committed to disk using the CreateRecord data block.
The next step we take is to use the RunDataMacro action. For the Macro Name argument of the RunDataMacro action, we use the GetCurrentValue named data macro attached to tblSettings, which you saw in the previous section. You’ll notice in Figure 7-66 that Access displays a Parameters section beneath the Macro Name argument. When you add a named data macro that includes parameters to the macro design surface, Access shows those parameters to you by providing a text box to enter the parameters. In our example, we pass in the AllEmailInfoForErrors parameter to get both the values of the SendEmailForAppErrors Boolean field and the AdminEmailAddress text field from the tblSettings table. We then use an If block to test the value we got back from one of the return variables. To reference a return variable, add the [ReturnVars] qualifier in front. Our If condition expression is as follows:
[ReturnVars]![RVSendEmailForError]
If the value is True, this means that we should send an email to the database administrator stating that Access encountered an error in the application. Inside the If block, we use the SendEmail action to send an email to the database administrator. For the To argument, we use an expression that uses the return variable of the email address from the RunDataMacro action so we know to whom to send the email. In the Body argument, we use an expression to display a custom message. Inside the expression, we concatenate our message with the local variable that contains the ID of the record that Access just committed to the tblErrorLog table.
As you can see, return variables are a very useful feature with data macros. When you use them in conjunction with parameters, you can create some very complex business logic at the data layer and even pass information back up to the user interface layer.
The Back Office Software System sample web database includes many named data macros attached to several web tables. Table 7-5 lists all the named data macros with a short description of their purpose. You can explore these samples for additional examples of how to design and use named data macros. In Chapter 21, you’ll learn how to call some of these named data macros from user interface macros.
Table 7-5. Named Data Macros in BOSS Web Database
Table Name | Macro Name | Description |
|---|---|---|
tblErrorLog | ClearErrorTable | Deletes all records from the tblErrorLog table. |
ClearServerErrorTable | Deletes all records from the USysApplicationLog table. | |
LogError | Logs client application errors to tblErrorLog table. | |
tblInvoiceHeaders | AuditInvoiceTotals | Audits all invoices within a given date range. |
AuditInvoiceTotalsOneVendor | Audits all invoices within a given date range for a specific vendor. | |
VerifyInvoiceBalanced | Checks to see if a specific invoice is balanced. | |
tblLaborPlanDetails | ApplyLaborPlanToSchedule | Loops through records in tblLaborPlanDetails and updates two fields in preparation for creating new records in tblSchedule. |
CleanupUpdatedRecords | Loops through records in tblLaborPlanDetails and clears two fields that were initially set with ApplyLaborPlanToSchedule named data macro. | |
CopyDateRangeRecords | Loops through records in tblSchedule within a date range and updates two fields in preparation for creating additional new records in tblSchedule. | |
tblSchedule | CopyDateRangeRecordsCleanup | Loops through records in tblSchedule and clears two fields that were initially set with the CopyDateRangeRecords named data macro. |
CopySingleDateRecords | Loops through records in tblSchedule for a specific date and updates two fields in preparation for creating additional new records in tblSchedule. | |
CopySingleDateRecordsCleanup | Loops through records in tblSchedule and clears two fields that were initially set with the CopySingleDateRecords named data macro. | |
DeleteDateRangeRecords | Deletes all records in tblSchedule within a given date range. | |
DeleteSingleDateRecords | Deletes all records in tblSchedule for a given date. | |
EmailScheduleAllEmployees | Emails weekly schedule details to all employees. | |
EmailScheduleOneEmployee | Emails weekly schedule details to a specific employee. | |
EmailScheduleOneEmployee | Adjusts date values of sample data to work easily with data around the current time frame. | |
| tblSettings | AdjustSampleDate | Dynamically changes some of the sample data dates in the tables. |
| tblSettings | GetCurrentValue | Gets application settings data from the tblSettings table. |