MS-Access / Getting Started

---

Working with Return Variables

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.

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 If

The 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.

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.

You can see the second half of the LogError named data macro. Note that we collapsed the Parameters block so you can see the rest of the logic.

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 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]![RVSendEmailOnError]

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-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.

Table-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 ApplyLaborPlan-ToSchedule 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.
	AdjustSampleDate	Gets application settings data from the tblSettings table.