Microsoft KB Archive/88670

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

Article ID: 88670

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q88670



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



SUMMARY
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 AutoFillNewRecordfunction 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

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

 Open the sample database NWIND.MDB. Create a module and add the following line to the Declarations section:

Option Explicit  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 = &quot;;&quot; & F![AutoFillNewRecordFields] & &quot;;&quot;

' 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, &quot;;&quot; & F(I).ControlName _               & &quot;;&quot;) > 0 Then F(I) = RS(F(I).ControlSource) End If        Next

DoCmd Echo True

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

=AutoFillNewRecord([Form]) Add an unbound text box to the form with the following properties:

ControlName: AutoFillNewRecordFields

Visible: No

DefaultValue: Company Name;Contact Name;Contact Title;Address</ol>

When you move to a new record, the Company Name, Contact Name, Contact Title, and Address fields are filled in automatically. If you want all fields to fill in automatically, you can leave the DefaultValue property blank or omit putting the AutoFillNewRecordField text box on your form.

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

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

Additional query words: duplicate copy carry forward keep

Keywords: kbhowto kbusage KB88670

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.