Exclude Control Fields in CarryOver Procedure

[mjoachim]

I am building an Access form and want most of my fields to carry forward automatically when advancing to the next record. I found code on AllenBrowne.com that works well, but am unable to successfully get the call command to exclude fields that I DO NOT want carried forward to the new record.

I have tried to mimic the code that he provided for this purpose:
Call CarryOver(Me, strMsg, "Notes", "EmployeeID")

...and receive the following error message:
"Compile error: Wrong number of arguments or invalid property assignment"

My code that currently works is simply:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Call CarryOver(Me)
End Sub

I would like this call command to exclude a few fields, including: "Hours", "OtherHours"

Thank you for any assistance!

<< Edit (NeoPa) : This code added later for clarity >>

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

[Seth Schrock]

On lines 18 and 22 you would need to test to make sure that the control isn't one that you don't want to move over. As an example, say you don't want the controls named txtFirstName and txtLastName moved over:

If Not IsNull(rst(ctl.Name)) And Not (ctl.Name = txtFirstName Or ctl.Name = txtLastName) Then

[mjoachim]

Don't the lines "For i = 0 To frm.Count-1" and "Set ctl = frm(i)" automatically cycle through every control on the form?

Should I be using an if statement in the procedure itself to exclude controls that I want skipped? Is there a better way?

[Seth Schrock]

Yes, line 15 does loop through every control on your form, including labels, buttons, subforms, tab controls, etc. That is why lines 17 and 21 are included so that something happens only if the control is either a textbox or a combobox. Then lines 18 and 22 make sure that there is something in those controls. My added code makes sure that the control name isn't one that you want excluded.

[NeoPa]

How about setting a value in the .Tag property of each Control that indicates whether or not it needs to be carried over? That way the check is simpler and cleaner.

[Seth Schrock]

Why didn't I think of that? That would be a much easier method. I have actually done that too. Thanks NeoPa.

[mjoachim]

Seth: I initially missed the code you added. Once I fixed what I had broken in testing, I was able to confirm that your approach does work. Thanks!

NeoPa: Can you elaborate on your comment? I see that there is a property "Tag" under the Other tab of each control. Just entering a value there does not prevent the control from copying forward. Would use of that property require additional code to have that examined?

[Seth Schrock]

You would replace lines 17 through 25 with

If ctl.Tag = "Transfer" Then
    If Not IsNull(rst(ctl.Name)) Then
        ctl = rst(ctl.Name)
    End If
End If

You would then put Transfer in the Tag property of each control that you want to be transferred.

[mjoachim]

Great! Both excellent solutions. I will swap out my code with the Tag approach.

Thanks!

[NeoPa]

Seth has done a fine job of illustrating the concept. It could also work as :

If (ctl.Tag = "Transfer") And (Not IsNull(rst(ctl.Name))) Then _
    ctl = rst(ctl.Name)

I agree with the selection of his post as Best Answer, but I merged the code into the OP in order that it will make immediate sense to anyone reviewing the thread quickly (It refers to code lines from your code).

[zmbd]

I often use the tag property for other activities, i.e. group security; thus, the tag option doesn't work for me. However, there is an old school method illustrated here using a hidden control and a value list:
How to fill a record with data from a previous record automatically in Access 2000 and 2002 and Office Access 2003
I've been using this method since Acc97 days.

BOL
-z

[NeoPa]

It's true. The .Tag property is so flexible it can be used for various things. Clearly, only use it in such a way that its needs never overlap, or find a way of ensuring all required needs are met somehow. I sometimes find myself using it for multiple needs by using multiple values separated by commas.

Nevertheless, it's really the concept that we're pushing here. If you understand why to use the .Tag property then you can more easily find alternative ways of handling it when it's not available.