The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article describes two ways to obtain values from the previous or next
record for use in calculations. The first method uses the Dlookup()function
in an expression; the second method uses two user-defined functions.
MORE INFORMATION
Using Dlookup()
NOTE: This technique assumes you have a table with an ID field of a numeric
data type. And, the ID values are not missing any number in sequential
order. If your table does not meet these criteria, then you should use the
"Using Code" method described later in this article.
You can use the following sample Dlookup() expressions to obtain values
from a field in the previous or next record of a form, a report, or a
query.
In a Form:
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]+1")
In a Report:
To obtain a value from a field in the previous record, type the following
line for the text box's ControlSource property:
=DLookUp("[Field]","Table","[ID]=Reports![Report1]![ID]-1")
To obtain a value from a field in the next record, type the following line
for the text box's ControlSource property:
=DLookUp("[Field]","Table","[ID]=Reports![Report1]![ID]+1")
In a Query:
To obtain a value from a field in the previous record, type the following
line in the Field row of the query grid:
Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]-1)
To obtain a value from a field in the next record, type the following line
in the Field row of the query grid:
Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]+1)
NOTE: In these sample expressions, the "-1" and "+1" indicate the previous
and next records. When the current record is the first record in the
recordset, the "-1" returns a null value because there is no previous
record. Likewise, when the current record is the last record in the
recordset, the "+1" returns a null. If you want to return a value from a
record other than the next or previous one, you can specify a different
number, for example, "-3" for the third previous record.
Using Code
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access version 1.x and Microsoft Access version 2.0.
To retrieve a value in a field from the previous or next record in a form
using code, follow these steps:
- Create a new module and type the following line in the Declarations
section:
Option Explicit
- Type the following procedures:
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. If you are using a
version of Microsoft Access earlier than 7.0, remove the underscore
from the end of the line when re-creating this code.
'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Function PrevRecVal (F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As Recordset
On Error GoTo Err_PrevRecVal
' The default value is zero.
PrevRecVal = 0
' Get the form recordset.
Set RS = F.RecordsetClone
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select
' Move to the previous record.
RS.MovePrevious
' Return the result.
PrevRecVal = RS(FieldNameToGet)
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function
'*************************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'**************************************************************
Function NextRecVal (F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As Recordset
On Error GoTo Err_NextRecVal
' The default value is zero.
NextRecVal = 0
' Get the form recordset.
Set RS = F.RecordsetClone
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select
' Move to the next record.
RS.MoveNext
' Return the result.
NextRecVal = RS(FieldNameToGet)
Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function
The following example demonstrates how to use the PrevRecVal() function in
a form to create a mileage log. The custom function returns the previous
odometer reading which is used to calculate miles per gallon (MPG) for an
automobile. To use the PrevRecVal() function, follow these steps:
- Create the following new table, and then save it as Mileage Log:
Table: Mileage Log
-----------------------------------------------------------
Field Name: ID
Data Type : AutoNumber (Data Type: Counter in earlier versions)
Indexed: Yes (No Duplicates)
Field Name: Date
Data Type : Date/Time
Field Name: Odometer
Data Type : Number
FieldSize: Double
Field Name: Gallons
Data Type : Number
FieldSize: Double
Primary Key: ID
- View the Mileage Log table in Datasheet view and enter the following
sample data:
ID Date Odometer Gallons
---------------------------------
1 6/21/94 77917.8 10.2
2 6/25/94 78254.7 9.6
3 6/30/94 78582.3 10
4 7/5/94 78918.4 10.4
5 7/10/94 79223.4 9.4
- Use the Form Wizard to create a new tabular form based on the Mileage
Log table. Include all the Mileage Log table fields except the ID field.
- View the form in Design view and add the following three text box
controls to the form:
Text Box 1
-----------------------------------------------------
Name: PrevOdometer
ControlSource: =PrevRecVal(Form,"ID",[ID],"Odometer")
Format: Fixed
Text Box 2
-----------------------------------------------------------------
Name: MilesDriven
ControlSource: =iif([PrevOdometer]=0,0,[Odometer]-[PrevOdometer])
Format: Fixed
Text Box 3
---------------------------------------
Name: MPG
ControlSource: =[MilesDriven]/[Gallons]
Format: Fixed
- View the form in Form view. Note that the form displays the following
information:
Date Odometer Gallons PrevOdometer MilesDriven MPG
-------------------------------------------------------------
6/21/94 77917.8 10.2 0 0 0
6/25/94 78254.7 9.6 77917.80 336.90 35.09
6/30/94 78582.3 10.0 78254.70 327.60 32.76
7/05/94 78918.4 10.4 78582.30 336.10 32.32
7/10/94 79223.4 9.4 78918.40 305.00 32.45
REFERENCES
For information about entering data automatically into a form using
values from the previous record, please see the following article in
the Microsoft Knowledge Base:
ARTICLE-ID: Q136127
TITLE : ACC: Fill Record w/Data from Prev. Record Automatically
(95/97)