Carry data over to new record (old version)

        1 votes: *****     1,334 views      No comments
by Allen Browne, 20 April 2005    (for Access 95+)

MS-Access Tips for Serious Users

Provided by Allen Browne,

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
        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
    End If

    Set rst = Nothing
    Exit Sub

    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

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Carry data over to new record (old version)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).