MS-Access Tips for Serious Users

Provided by Allen Browne, allen@allenbrowne.com

Carry data over to new record

If a new record will be similar to the previous one, you may wish to automatically fill text boxes with that data for the user to edit. dBase provided a "carry" mechanism as early as the mid '80s. Use a form's BeforeInsert event to achieve this result in Access.

1. Open the form in Design View.
2. In the Properties Box, select the BeforeInsert event, and type the left square bracket. Access responds by filling in [Event Procedure].
3. Click the "..." button. Access responds by opening the code window.
4. Between the Sub ... and End Sub lines, enter:

Call CarryOver(Me)

1. Save the form.
2. In the Database window/Nav Pane, select the Modules tab and choose New.
3. Paste this code, and then save the module with a name such as basCarryOver:

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

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