LookupRecord Data Block
A LookupRecord data block performs a set of actions on a specific record.
Note
The LookupRecord data block is available only in Data Macros.
Setting
The SetField action has the following arguments.
Argument |
Required |
Description |
---|---|---|
In |
Yes |
A string that identifies the record to operate on. The In argument can contain the name of the table, a select query, or a SQL statement.
Note
The specified record cannot include data stored in a linked table or ODBC data source.
|
Where Condition |
No |
A string expression used to restrict the range of data on which the LookupRecord data block is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the LookupRecord data block operates on the entire domain specified by the In argument. Any field that is included in criteria must also be a field in In. |
Alias |
No |
A string that provides an alternative name for the record specified by the In argument. Often used to shorten the table name for subsequent references to prevent possible ambiguous references. If Alias is not specified, the table or query name will be used as the alias. |
Remarks
If the criteria specified by the In and Where Condition arguments specifies more than one record, then the LookupRecord data block will only operate on the first record.
If no record satisfies Where Condition or if In contains no records, then LookupRecord creates a blank record in which all of the fields contain a Null value.
Example
The following example shows how to use the SetReturnVar action to return a value from a named data macro. A ReturnVar named CurrentServiceRequest is returned to the macro or Visual Basic for Applications (VBA) subroutine that called the named data macro.
Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors
RunDataMacro
Macro Name tblServiceRequests.dmGetCurrentServiceRequest
Parameters
prmAssignedTo =[ID]
SetProperty
Control Name txtCurrentSR
Property Value
Value =[ReturnVars]![CurrentServiceRequest]
The following example shows how to use the RaiseError action to cancel the Before Change data macro event. When the AssignedTo field is updated, a LookupRecord data block is used to determine whether the assigned technician is currently assigned to an open service request. If this is true, then the Before Change event is cancelled and the record is not updated.
/* Get the name of the technician */
Look Up A Record In tblTechnicians
Where Condition =[tblTechnicians].[ID]=[tblServiceRequests].[AssignedTo]
SetLocalVar
Name TechName
Expression [tblTechnicians].[FirstName] & " " & [tblTechnicians].[LastName]
/* End LookUpRecord */
If Updated("AssignedTo") Then
Look Up A Record In tblServiceRequests
Where Condition SR.[AssignedTo]=tblServiceRequests[AssignedTo] And
SR.[ID]<>tblServiceRequests.[ID] And IsNull(SR.[ActualCompletionDate])
Alias SR
RaiseError
Error Number 1234
Error Description ="Cannot assign a request to the specified technician: " & [TechName]
End If
About the Contributors
Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.