Microsoft KB Archive/137117

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

= 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 AutoFillNewRecordfunction 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:

  Open the sample database Northwind.mdb.   Create a module and make sure the following line appears in the Declarations section: Option Explicit   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   Open the Customers form in Design view. Change the form’s OnCurrent property to read as follows: =AutoFillNewRecord([Form])   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.