ACC: Fill Record w/ Data from Prev. Record Automatically 1.x/2.0

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When you are creating new records using a form, you may want to speed the data entry process by having fields in the new record fill automatically with values from the previous record. This article demonstrates a sample Access Basic function called AutoFillNewRecord() that enables you to automatically fill selected fields, or all fields, in a new record with values from the previous record.

MORE INFORMATION

One technique to speed up repetitive data entry for the field containing 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 below. You can call this function from a form's OnCurrent property 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 an unbound text box and set the DefaultValue property with a semicolon-delimited list of field names to automatically fill. For example:

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

1. Open the sample database NWIND.MDB.

2. Create a module and add the following line to the Declarations section:

         Option Explicit
   

3. Type the following procedure appropriate for your version of Microsoft Access:

   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.

         Function AutoFillNewRecord (F As Form)
            Dim RS As Dynaset
            Dim I As Integer, RetVal
            Dim FillFields As String, FillAllFields As Integer
   
         On Error Resume Next
   
            ' Exit if we are not on the new record.
            RetVal = F.Bookmark
            If Err = 0 Then Exit Function
            Err = 0
   
            ' Go to the last record of the form recordset (to autofill from).
            Set RS = F.Dynaset
            RS.MoveLast
   
            ' Exit if we cannot move to the last record (no records).
            If Err <> 0 Then Exit Function
   
            ' Get the list of fields to auto fill.
            FillFields = ";" & F![AutoFillNewRecordFields] & ";"
   
            ' If there is no criteria field, then set flag indicating
            ' ALL fields should be auto filled.
            FillAllFields = Err <> 0
   
            DoCmd Echo False
   
               ' Visit each field on the form.
               For I = 0 To F.Count - 1
               ' 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, ";" & F(I).ControlName _
                   & ";") > 0 Then
                     F(I) = RS(F(I).ControlSource)
               End If
            Next
   
            DoCmd Echo True
   
         End Function
   
   

4. Open the Customers form in Design view. Change the form's OnCurrent property as follows:

         =AutoFillNewRecord([Form])
   

5. Add an unbound text box to the form with the following properties:

         ControlName: AutoFillNewRecordFields
         Visible: No
         DefaultValue: Company Name;Contact Name;Contact Title;Address
   

For information on how to do this in Microsoft Access 7.0 and 97, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q136127
   TITLE     : ACC: Fill Record w/Data from Prev. Record Automatically
               (95, 97)