Using Local Variables

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.

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 EditRecord

At 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:

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.

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.