ACC2000: Fill Record with Data from Previous Record Automatically

ID: Q210236

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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 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 type the following line in the Declarations section:


Option Explicit

Type the following procedure:

 
Function AutoFillNewRecord(F As Form)

   Dim RS As DAO.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

Save the module as modAuto_Fill_New_Record.

Open the Customers form in Design view. Change the OnCurrent property of the form to read as follows:


=AutoFillNewRecord([Forms]![Customers])

Add a text box to the form, and set the following properties:


   Text Box
   ----------------------------------------------------------
   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 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, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type For Each...Next Statement in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

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

Keywords : kbprg kbdta AccCon FmsHowto MdlDao
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo