Carry data over to a new record

[3divine]

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.Controls.Count - 1
Set ctl = frm.Controls(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.

 

[JohnJ]

I don't know if this is any help.

I have a form in which I wish to carry over 3 fields into the next one. For each field in the After Update event I have the following [Event Procedure]. ('Campus' is the name of the field.)

Private Sub Campus_AfterUpdate()
If Not IsNull(Me.Campus) Then
Campus.DefaultValue = "='" & Me.Campus & "'"
Me.Campus.TabStop = False
Else
Me.Campus.TabStop = True
End If
End Sub

This procedure works on a 'push' basis rather than a 'pull' one - if you follow me .

Whether you want to have to repeat this when you have a large number of fields is up to you but for me it works a treat.

John James
Melbourne, AUSTRALIA

 

[3divine]

Hello John,

Thank you so much for that very valuable information. I also have exactly 3 fields where I want to carry value.

Wish me luck as I'm very desperate!

 

[3divine]

John or anybody,

In each of my 3 fields I placed your code in the After_Update event but all it did was open my form. Then I tried to enter a new record and expected for this 3 textboxes to be automatically filled in. Obviously the code wasn't firing because the first textbox had the focus and still waiting for an entry.

Push-Pull?