Form with a DUPLICATE button

[GrandMasterTuck]

Hi folks! I've attached a sample database below, and I'm hoping one of you can teach me how to accomplish this task:

On my main form (frmMain), I have a button that is supposed to duplicate the Main Form record, and also duplicate all associated records from the subform. I know how to duplicate the MainForm record, but I have absolutely no idea how to get that same button to also copy all the associated records in the subform and assign them to the newly created main form record.

I would imagine it requires some VBA, but I don't even know where to begin to add that logic (I still use the Macro Editor to get buttons to do stuff). Anyone have any idea?

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format.
Can you tell us a little about the need for this functionality.

 

[GrandMasterTuck]

Hi Bob. Thanks for the reply. I've attached the requested version of the file for you.

The database is going to be used to keep track of employee assignments for a small work crew for an entire month. For each day, I'll create the date/shift they're to work, and then list each employee that's hired for the day. The system keeps track of both assignments and payroll for the employees. The duplicate button allows me to quickly create a week's worth of assignments (or more or less, if needed) with the same crew on the same assignment.

I always create my databases so they're scalable, which means even though the database will be for a small crew now, I might need to use it to handle a bigger crew later, and I don't want to do it the way the old database manager did. The last guy created ONE table with a column for every employee, and you just filled their names in each column. So for a work shift, you had WorkShiftDate, WorkShiftCode, WorkShiftName1, WorkShiftName2, WorkShiftName3 and so on, up to Name9. The problem is that, once we add new jobs that require more people (which is exactly what happened, and exactly why the boss wants the database fixed), I have to add a bunch of columns to tables, update a ton of queries and update a ton of reports just to get those few extra names in there. And that table has columns for names, addresses, hours worked that day, job assigned that day... and that's for EACH OF THE NINE GUYS. So adding two guys to the system requires EIGHT NEW COLUMNS, and EIGHT new fields on EVERY query, report...

I told him I'd redesign the thing to be more intelligent with its table-relations and such, and it will allow him to use it well into the future as new employees or jobs are added. Everything now is table driven, and everything has relationships. The real database I'm using has thousands of records that I imported from the old database (and split into the proper tables with the proper relationships), and now I'm just trying to get some fancy buttons to work right.

I know there's a way to do this... I'm just not that knowledgeable yet.

 

[GrandMasterTuck]

I just found this on another site, and tried it, but I get an error message:

'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !ScheduleDate = Me.ScheduleDate + 1
                !ScheduleShift = Me.ScheduleShift
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !ScheduleID
            
            'Duplicate the related records: append query.
            If Me.[sbfmAssignments].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblAssignments] ( ScheduleID, AssignmentName, AssignmentJob, AssignmentCode, AssignmentNote ) " & _
                    "SELECT " & lngID & " As NewID, AssignmentName, AssignmentJob, AssignmentCode, AssignmentNote " & _
                    "FROM [tblAssignments] WHERE ScheduleID = " & Me.ScheduleID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With

The error message reads: Run time error '2645', Microsoft Access cannot find the field '|1' referenced in your expression.

And it highlights this line in the code:
If Me.[sbfmAssignments].Form.RecordsetClone.RecordCount > 0 Then

And now I'm lost again. I have no idea why I'm getting that error, and I can't find ANYWHERE in my code with a vertical line and a number 1...

EDIT: Here's the site I found the code, in case anyone else wants to know: http://allenbrowne.com/ser-57.html

 

[bob fitz]

Change the Name property of the button to cmdDuplicate and then try this code in the Click event of the button

Private Sub cmdDuplicate_Click()
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    
    'Save any edits first
On Error GoTo Err_cmdDuplicate_Click

    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !ScheduleDate = Me.ScheduleDate + 1
                !ScheduleShift = Me.ScheduleShift
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !ScheduleID
            
            'Duplicate the related records: append query.
            If Me.[tblAssignments subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblAssignments] ( ScheduleID, AssignmentName, AssignmentJob, AssignmentCode, AssignmentNote ) " & _
                    "SELECT " & lngID & " As NewID, AssignmentName, AssignmentJob, AssignmentCode, AssignmentNote " & _
                    "FROM [tblAssignments] WHERE ScheduleID = " & Me.ScheduleID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_cmdDuplicate_Click:
  Exit Sub
 
Err_cmdDuplicate_Click:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDuplicate_Click of VBA Document Form_frmMain"

End Sub

 

[GrandMasterTuck]

YOU DA MAN, BOB!

OMG, that's great, sir, thanks so very much! What, exactly, did I do wrong in my block of code? They look exactly alike!

Thanks and reps given!

EDIT: Found my mistake. It was in this line:
If Me.[sbfmAssignments subform].Form.RecordsetClone.RecordCount > 0 Then

I forgot the word "subform" after the name of my subform! Stupid oversight, but that's all it takes to grind everything to a screeching halt. Thanks again, Bob Fitz!

 

[bob fitz]

Thank you for your kind words. I'm glad it worked for you

And it highlights this line in the code:
If Me.[sbfmAssignments].Form.RecordsetClone.RecordCount > 0 Then

The bit in red needs to be the name of the control that holds the subform, which in this case is tblAssignments subform
You also had a "End If" missing

            Me.Bookmark = .LastModified
        End With
    [COLOR="Red"]End If[/COLOR]

Exit_cmdDuplicate_Click:

Hope this helps.