Selectively Carry Data Over to the Next Record

[MDAO]

Hi there,

I have created a database for some simple inventory record. Since many of the data from an application to another are similar, I have inserted a carryover module. Therefore, Every new record will be auto-filled with the data from the previous one. I was just wondering how I can selectively carry the data over. In other words, I want to leave some of the blanks empty on a new page of the form. Below are the codes I have copied in the internet from here to there. I didn't write them myself.

Thanks a Lot.
Dao

The Module:

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



In the form:
Private Sub Form_BeforeInsert(Cancel As Integer)
Call CarryOver(Me)
End Sub

[June7]

What is your question? Is there an issue with the code? What happens - error message, wrong results, nothing?

Unless you want the form to open with data from latest record, set the Default Value property with data entry of the first new record. http://access.mvps.org/access/forms/frm0012.htm

[MDAO]

The code is working fine. It carries every entries in the previous record to the next, but I was wondering if there is a way that it will carry some, but not all.

[June7]

Modify the code.

Could eliminate the looping that checks for type of control and instead explicitly reference specific controls by name. How many are there that need to be populated? This might be faster than looping that has to hit every single control (textboxes, comboboxes, labels, etc.).

Or put a condition within each loop that skips a control if it has a specific name.

Or use the control Tag property. Set the Tag property with some value. Then within the loop have a condition that checks for the value in the Tag property and if condition is true, populate the control.

Or since fields have same name as controls, loop through the recordset fields instead of the form controls collection. Also use the Tag property to identify controls for populating. Something like:

                For j = 0 To rs.Fields.Count - 1
                    If Me.Controls(rs.Fields(j).Name).Tag = "Populate" Then
                        Me.Controls(rs.Fields(j).Name) = rs.Fields(j)
                    End If
                Next j

[MDAO]

I am basically a noob that knows no coding. I know the concepts of what you are saying, but no what to do myself. Can you make it simpler for me please?

[Missinglinq]

And, of course, using <Ctrl> + <'> for any Control will populate it with the data that was last entered for that Control, even if the Form has been closed and then re-opened.

Linq ;0)>

[June7]

Which option do you want? I already show an example that uses the Tag property.

Dim rst As DAO.Recordset
Dim j As Integer
Set rst = frm.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveLast
For j = 0 To rst.Fields.Count - 1
   If frm.Controls(rst.Fields(j).Name).Tag = "Populate" Then
       frm.Controls(rst.Fields(j).Name) = rst.Fields(j)
   End If
Next j
End If

[MDAO]

Which option do you want? I already show an example that uses the Tag property.

Dim rst As DAO.Recordset
Dim j As Integer
Set rst = frm.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveLast
For j = 0 To rst.Fields.Count - 1
   If Me.Controls(rst.Fields(j).Name).Tag = "Populate" Then
       Me.Controls(rst.Fields(j).Name) = rst.Fields(j)
   End If
Next j
End If

So do I just copy and paste the code into the event procedure page of the form? How do I decide which field to be not carried?

[June7]

The suggested code is to replace the code in the CarryOver sub. Ooops, I goofed - instead of Me use frm. I edited the earlier post. However, if the form is a subform, I doubt this sub will work.

Set the Tag property of controls you want to carry forward. Which ones is entirely up to you.