How to fill a record with data from a previous record automatically in Access 2000 and 2002 and Office Access 2003

One technique that you can use to speed repetitive data entry for the field that contains the insertion point is to press CTRL+APOSTROPHE (') to retrieve the value from the previous record.

Another technique is to use the AutoFillNewRecord()function described later in this article. You can call this function from a form's OnCurrent property event procedure to fill all the fields in a new record using data from the previous record. If you want to fill only selected fields, you can create a text box and set the DefaultValue property with a semicolon-delimited list of control names to automatically fill, for example:

   Text box:
   -----------------------------------------------
   Name: AutoFillNewRecordFields
   Visible: No
   DefaultValue: Phone;Company Name;City;State;Zip
 

To create and use the AutoFillNewRecord() function, follow these steps:

1. Open the sample database Northwind.mdb.
2. Create a module and type the following line in the Declarations section:

   Option Explicit
   

3. Type the following procedure:

    
   Function AutoFillNewRecord(F As Form)
   
      Dim RS As DAO.Recordset, C As Control
      Dim FillFields As String, FillAllFields As Integer
      
      On Error Resume Next
      
      ' Exit if not on the new record.
      If Not F.NewRecord Then Exit Function
      
      ' Goto the last record of the form recordset (to autofill form).
      Set RS = F.RecordsetClone
      RS.MoveLast
      
      ' Exit if you cannot move to the last record (no records).
      If Err <> 0 Then Exit Function
      
      ' Get the list of fields to autofill.
      FillFields = ";" & F![AutoFillNewRecordFields] & ";"
      
      ' If there is no criteria field, then set flag indicating ALL
      ' fields should be autofilled.
      FillAllFields = Err <> 0
      
      F.Painting = False
      
      ' Visit each field on the form.
      For Each C In F
         ' Fill the field if ALL fields are to be filled OR if the
         ' ...ControlSource field can be found in the FillFields list.
         If FillALLFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
            C = RS(C.ControlSource)
         End If
      Next
      
      F.Painting = True
      
   End Function
   
   

4. Save the module as modAuto_Fill_New_Record.
5. Open the Customers form in Design view. Change the OnCurrent property of the form to read as follows:

   =AutoFillNewRecord([Forms]![Customers])
   

6. Add a text box to the form, and set the following properties:

   
      Text Box
      ----------------------------------------------------------
      Name: AutoFillNewRecordFields
      Visible: No
      DefaultValue: CompanyName;ContactName;ContactTitle;Address
    

   Note This sample setting for the text box works only in the Northwind sample database. For your own database, you can set the DefaultValue property with a semicolon-delimited list of control names.
   

When you go to a new record, the CompanyName, ContactName, ContactTitle, and Address fields are filled in automatically. If you want all fields to automatically be filled in, you can leave the DefaultValue property blank or omit putting the AutoFillNewRecordFields text box on the form.