Fill Record With Data from Previous Record Automatically

Wednesday, January 08, 2003 23:30:08

Obstensively, you should be able to go by Microsoft Knowledge Base Article 136127, but I found it didn't work; that is, it worked perfectly on the example Northwind database, but wouldn't hit a single lick with my actual database. (That said, my copy of it is located on this page right here.)

I found another solution courtesy of this site. But then, that solution gives the very infamous "Access won't close," which--even after hours and hours of work trying to clear it up (with help from Microsoft Access MPVs galore)--nonetheless still will not work. But the code for it is still in case you want it.

At any rate, here is the solution that worked for me:

Enter the following code in the "on click" event procedure of a button:

On Error GoTo Err_Command5_Click

	DoCmd.GoToRecord , , acPrevious
	DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
	DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
	DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

	'Paste Append
	' This Code will BACK UP to the previous record, copy it
	' and paste into the current one. With this code, you must click
	' NEW RECORD first. Other samples of this code do this for you

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click

Solution 2: CarryOver Function

Create a new module, name it something like modInsertNewRecord. Here is the code:

Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose:  Carry the values over from the last record to a new one.
' Usage:   In a form's BeforeInsert event procedure, enter:
'                   Call CarryOver(Me)
' Notes:    This example limited to text boxes and combo boxes.
'           Text/combo boxes must have same Name as the fields they represent.
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim i As Integer

    Set rst = frm.RecordsetClone
    If rst.RecordCount > 0 Then
        rst.MoveLast
        For i = 0 To frm.count - 1
            Set ctl = frm(i)
            If TypeOf ctl Is TextBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            ElseIf TypeOf ctl Is ComboBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            End If
        Next
    End If
    rst.Close

Exit_CarryOver:
    Set rst = Nothing
    Exit Sub

Err_CarryOver:
    Select Case Err
    Case 2448         'Cannot assign a value
        Debug.Print "Value cannot be assigned to " & ctl.Name
        Resume Next
    Case 3265         'Name not found in this collection.
        Debug.Print "No matching field name found for " & ctl.Name
        Resume Next
    Case Else
        MsgBox "Carry-over values were not assigned, from " & ctl.Name & _
            ". Error #" & Err.Number & ": " & Err.Description, vbExclamation, "CarryOver()"
        Resume Exit_CarryOver
    End Select
End Sub

Type the following code into the command button on your form used for inserting the previous record as a new entry (obviously, an "on click" event procedure):

DoCmd.GoToRecord , , acNewRec
Call CarryOver(Me)

Microsoft Knowledge Base Article 136127 Solution

Sunday, March 03, 2002 17:57:53

(Derived from Microsoft Knowledge Base 136127)

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.

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. You can do this in Visual Basic with the following SendKeys statement:

SendKeys "^'"

(The last 2 characters are an apostrophe and a quotation mark, not 3 apostrophes)

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 if it is not already there:

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 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 form's OnCurrent property to read as follows:

In Microsoft Access 97

=AutoFillNewRecord([Forms]![Customers])

In Microsoft Access 7.0

=AutoFillNewRecord([Form])

Add a text box to the form, and set the control's properties as follows: Name: AutoFillNewRecordFields
Visible: No
DefaultValue: CompanyName;ContactName;ContactTitle;Address