ACC2000: Referring to a Field in the Previous Record or Next Record (210504)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210504
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

Method 1: Using DLookup()

NOTE: This technique assumes that you have a table with an ID field of a Number data type, and that the ID values are not missing any number (or numbers) in sequential order. If your table does not meet these criteria, 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.

NOTE: You must insert the correct data field, table, form, report, and ID field names in the following sample expressions.

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("[MyField]","MyTable","[ID]=Forms![MyForm]![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("[MyField]","MyTable","[ID]=Forms![MyForm]![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("[MyField]","MyTable","[ID]=Reports![MyReport]![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("[MyField]","MyTable","[ID]=Reports![MyReport]![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("[MyField]","MyTable","[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("[MyField]","MyTable","[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.

Method 2: Using Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To retrieve a value in a field from the previous or next record in a form by 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:
    '*************************************************************
' 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 DAO.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 DAO.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:
  1. Create the following new table, and then save it as Mileage Log:

    Field Name: ID
    Data Type : AutoNumber
    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 additional information about entering data automatically into a form by using values from the previous record, click the article number below to view the article in the Microsoft Knowledge Base:

210236 ACC2000:Fill Record with Data from Previous Record Automatically

Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto kbinfo KB210504
©2004 Microsoft Corporation. All rights reserved.