ACC: Referring to a Field in the Previous or Next Record

This article was previously published under Q101081
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you two methods that you can use to obtain values fromthe previous or the next record for use in calculations. The first methoduses the DLookup()function in an expression; the second method uses twouser-defined functions.

NOTE: A demonstration of the technique used in this article can be seenin the sample file, Qrysmp97.exe. For information about how to obtainthis sample file, please see the following article in the MicrosoftKnowledge Base:
182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center
MORE INFORMATION

Using DLookup()

NOTE: This technique assumes you have a table with an ID field of a Numberdata type, and the ID values are not missing any number in sequentialorder. 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 valuesfrom a field in the previous or next record of a form, a report, or aquery.

In a Form:

To obtain a value from a field in the previous record, type the followingline 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 linefor 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 followingline 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 linefor 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 followingline 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 linein 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 previousand next records. When the current record is the first record in therecordset, the "-1" returns a Null value because there is no previousrecord. Likewise, when the current record is the last record in therecordset, the "+1" returns a Null. If you want to return a value from arecord other than the next or previous one, you can specify a differentnumber, for example, "-3" for the third previous record.

Using Code

This section assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess version 1.x and Microsoft Access version 2.0.

To retrieve a value in a field from the previous or next record in a formby using code, follow these steps:

  1. Create a new module and type the following line in the Declarations section if it is not already there:
    Option Explicit
  2. 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 ina form to create a mileage log. The custom function returns the previousodometer reading, which is used to calculate miles per gallon (MPG) for anautomobile. To use the PrevRecVal() function, follow these steps:

  1. Create the following new table, and then save it as Mileage Log:
          Table: Mileage Log      -----------------------------------------------------------------      Field Name: ID         Data Type : AutoNumber (or Counter in Access 2.0 or earlier)         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
  2. 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
  3. 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.
  4. 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 
  5. 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 by usingvalues from the previous record, please see the following article inthe Microsoft Knowledge Base:
136127 ACC: Fill Record w/Data from Prev. Record Automatically (95/97)
next running sum
Properties

Article ID: 101081 - Last Review: 12/04/2015 09:32:32 - Revision: 2.3

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbfaq kbhowto kbprogramming kbusage KB101081
Feedback