INF: Fill Record w/ Data from Prev. Record Automatically (7.0)
  
PSS ID Number: Q137117
Article last modified on 09-29-1995
 
7.00
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access version 7.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
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.
 
MORE INFORMATION
================
 
One technique to speed 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 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
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:
 
1. Open the sample database Northwind.mdb.
 
2. Create a module and make sure the following line appears in the
   Declarations section:
 
      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 we are not on the new record.
      If Not F.NewRecord Then Exit Function
 
      ' Go to the last record of the form recordset (to autofill from).
      Set RS = F.RecordsetClone
      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 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. Open the Customers form in Design view. Change the form's OnCurrent
   property to read as follows:
 
      =AutoFillNewRecord([Form])
 
5. Add an unbound text box to the form and set the control's properties as
   follows:
 
      Name: AutoFillNewRecordFields
      Visible: No
      DefaultValue: CompanyName;ContactName;ContactTitle;Address
 
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 fill in, you can leave the DefaultValue property
blank or omit putting the AutoFillNewRecordFields text box on your form.
 
REFERENCES
==========
 
For more information about the For Loop, search for "For," and then
"For Each...Next Statement" using the Microsoft Access for Windows 95 Help
Index.
 
Additional reference words: 7.00 default value tag
KBCategory: kbusage
KBSubcategory: FmsHowto
=============================================================================
Copyright Microsoft Corporation 1995.
