LookupRecord Data Block

A LookupRecord data block performs a set of actions on a specific record.

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.