Note This article explains a technique that is demonstrated in the sample file that is named FrmSampl.mdb.
For additional information about how to obtain this sample file, click the following article number to view the article in the Microsoft Knowledge Base:
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
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:
To create and use the AutoFillNewRecord() function, follow these steps:
DefaultValue: Phone;Company Name;City;State;Zip
- Open the sample database Northwind.mdb.
- Create a module and type the following line in the Declarations section:
- 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
' 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)
F.Painting = True
- 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:
- Add a text box to the form, and set the following properties:
Note This 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.
Article ID: 210236 - Last Review: Oct 30, 2008 - Revision: 1