Inserting the value from the previous record in Access

Applies to
Microsoft Access 97 and 2000

In Access, you can insert the value from the same field in the previous record when you create a new record. For example, you can copy the supplier name from the previous record into the Supplier field of a new record. You can insert the value manually by using the CTRL+APOSTROPHE (') key combination. You can also insert the value automatically by using a DLookup function in an expression.

Using the Keyboard

Use the keyboard if the number of records you want to fill is relatively small.

To insert the value from the same field in the previous record by using the keyboard

  1. Open a form in Form or Datasheet view, a query in Datasheet view, or a report in Print Preview and scroll to a new record.
  2. Click the field in which you want to insert the value.
  3. Press CTRL+APOSTROPHE (').

Using the DLookup Function in an Expression

Use the DLookup function if you want to fill a large number of records.

 Note    This technique assumes that you have a table with an ID field that has a Number data type, and the ID values are not missing any number in sequential order.

To insert the value from the same field in the previous record on a form

  1. Open the form in Design view.
  2. Right-click the control where you want to insert the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and FormName:

=DLookup("[FieldName]","TableName","[ID]=Forms![FormName]![ID]-1")

To insert the value from the same field in the previous record in a report

  1. Open the report in Design view.
  2. Right-click the control where you want to enter the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and ReportName:

=DLookup("[FieldName]","TableName","[ID]=Reports![ReportName]![ID]-1")

To insert the value from the same field in the previous record in a query

  1. Open the query in Design view.
  2. In the Field row of the query design grid, type the following text, substituting your own values for FieldName and TableName:

Expr1: DLookup("[FieldName]","TableName","[ID]=" & [ID]-1)