Microsoft KB Archive/136127

Knowledge Base

ACC: Fill Record w/Data from Prev. Record Automatically (95/97)

Article ID: 136127

Article Last Modified on 1/19/2007

---

APPLIES TO

• Microsoft Access 95 Standard Edition
• Microsoft Access 97 Standard Edition

---

This article was previously published under Q136127

SUMMARY

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

When you are creating new records by 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 shows you how to create a sample Visual Basic for Applications function called AutoFillNewRecord() that enables you to fill selected fields (or all fields) in a new record with values from the previous record automatically.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

150895 ACC95: Microsoft Access Sample Forms Available in Download Center

175066 ACC97: Microsoft Access 97 Sample Forms Available in Download Center

MORE INFORMATION

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 if it is not already there:

   Option Explicit

3. Type the following procedure:

         Function AutoFillNewRecord(F As Form)
            Dim RS As 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 form's OnCurrent property to read as follows:
   
   In Microsoft Access 97

         =AutoFillNewRecord([Forms]![Customers])
                           

   In Microsoft Access 7.0

         =AutoFillNewRecord([Form])

6. Add a text box to the form, and set the control's properties as follows:

   Name: AutoFillNewRecordFields
   Visible: No
   DefaultValue: CompanyName;ContactName;ContactTitle;Address

   Note This above 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.

REFERENCES

For more information about the For Loop, search the Help Index for "For Each...Next Statement," or ask the Microsoft Access 97 Office Assistant.

Additional query words: default value tag duplicate copy carry forward keep

Keywords: kbhowto kbprogramming KB136127

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.