Provided by Allen Browne, allen@allenbrowne.com
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.
Call CarryOver(Me)
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
Home | Index of tips | Top |