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:
- Create a new module and enter the following line in the Declarations section: Option Explicit
- 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
- 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
- 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 fields except the ID field on the form.
- 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. 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.
