Carry data over to new record 1

[thirty4d]

If a new record will be similar to the previous one, automatically fill text boxes with that data for the user to edit.

On my form, in the BeforeInsert event, I have:

Call CarryOver(Me)

In the module called basCarryOver this is my 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 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 & ": " & Error$, 48, "CarryOver()"
Resume Exit_CarryOver
End Select
End Sub

When I tested the code obviously did not do what it's supposed to do.

 

[Databaseguy]

For i = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(i)
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[thirty4d]

Hello Databaseguy,

Thanks for feedback!

Comment: In my form I have 3 textboxes called: txtPartnumber, txtOperationSequence and txtPartname.

In my code, do I have to hard code these controls?

Otherwise how is the code going to know which data I want to carry to which controls?