Access Carry Over Data

December 26, 2008 at 6:29 am 1 comment

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, 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

Entry filed under: Ms Access. Tags: .

Gambar di Form Access Filter a Form on a Field in a Subform

1 Comment Add your own

  • 1. Eraserve  |  March 5, 2009 at 1:56 am

    Thanks for this code. This may be benificial to me in a future project.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 94,725 hits

Twitter Updates

Iklan


%d bloggers like this: