INF: Referencing Fields in Previous or Next Record in a Form
PSS ID Number: Q120489
Article last modified on 10-04-1994
PSS database name: ACCESS

2.00
WINDOWS

---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access version 2.0
---------------------------------------------------------------------

SUMMARY
=======
This article describes how to reference a value in a field in the previous
or next record in a form for use in a calculation or expression.
This article assumes that you are familiar with Access Basic and with
creating Microsoft Access applications using the programming tools provided
with Microsoft Access. For more information on Access Basic, please refer
to the "Building Applications" manual.

MORE INFORMATION
================
The following example demonstrates how to create and use two sample user-
defined functions to retrieve the value in a field from the previous or
next record on a form:

1. Create a new module and enter the following line in the Declarations
   section:
      Option Explicit
2. Create the following procedures in the module.
   NOTE: In the following sample code, an underscore (_) at the end of a
   line is used as a line-continuation character. Remove the underscore
   from the end of the line when re-creating this code in Access Basic.
      '*************************************************************
      ' FUNCTION: PrevRecVal()
      ' PURPOSE: Retrieve a value from a field in the previous form
      '          record.
      ' PARAMETERS:
      '    F        - The form to get the previous value from.
      '    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 to retrieve the value from.
      ' 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
3. Create the following new table and then save it as Mileage Log:
      Table: Mileage Log
      -------------------------------
      Field Name: ID
         Data Type: Counter
         Indexed: Yes (No Duplicates)
      Field Name: Date
         Data Type: Date/Time
      Field Name: Odometer
         Data Type: Number
      Field Name: Gallons
         Data Type: Number
      Primary Key: ID
4. 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
5. Use the Form Wizard to create a new tabular form based on the Mileage
   Log table. Include all the fields except the ID field on the form.
6. 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
7. View the form in Form view. The following information will be
   displayed:
      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 other methods of referring to a previous record, please see the
following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q101081
   TITLE     : Two Methods to Refer to a Field in a Previous Record

Additional reference words: 2.00 running sum
KBCategory: kbusage
KBSubcategory: FmsHowto

=============================================================================
Copyright Microsoft Corporation 1994.