Duplicate Main/Sub Forms through MultiSelect List Box

I need to duplicate a Mainform and Subform records, but instead of duplicating the current record  I need to duplicate the current record for a MultiSelect List box looping through each ID selected in the list box and duplicating into the main recordset and the subform recordset.  I was hoping someone could provide a sample of code I could work off of. Thanks.
skennellyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Why?

In other words, this sounds a bit odd, and would seem to create redundant data.

So perhaps you could *first* take a step back and explain your ultimate goal here...?

JeffCoachman
skennellyAuthor Commented:
No problem. I am designing a fitness workout program. The program has a main form with (Name, Date, WorkoutType, etc.) linked to a subform which contains the workout data (Exercise, Sets, Repetitions, etc.) for the workout. Currently I have a duplicate button on the main form that duplicates the current record (main and subform) for the Client selected in the CmboBox. If I want to duplicate the record for another client, I need to select that client in the CmboBox.

So, I would like to add an unbound Multi-Select list box containing a list of all of the clients. I would then select (x) amount of clients from the list box. When I select the Duplicate button - the main and subform records would be duplicated for all of the clients selected in the list box.

To do this, I believe I would need to loop through the Client list box to build the criteria statement. Then I would need to select the underlying recordsets from the main and subforms, then connect it to the build criteria to add the workout to each of the clients' programs. The whole process would be done programmatically.

I hope this explanation is more helpful. Thanks.
Jeffrey CoachmanMIS LiasonCommented:
Great,
Thanks for the explanation, makes more sense now...


The kicker here is that you will have to be good a VBA to pull this off.

Here is the popular link to copy main and subform records:
http://support.microsoft.com/kb/208824

Then join that with something like this (sample attached) to do this for multiple customers
In your case you listbox would be filled with customers instead on reports.
And your code on the Button click (near the bottom) would have a snippet *roughly* like this:

...
Dim lngSelected As Long
   
        For lngSelected = 0 To Me.lstReports.ListCount - 1
            'Run the code from the MS Site
        Next lngSelected
...


Goo Luck...

;-)

JeffCoachman
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

skennellyAuthor Commented:
Thanks for your response. I have been working on the code for quite some time before asking the question. I will check out the samples and get back to you . Thanks again.
Jeffrey CoachmanMIS LiasonCommented:
Yeah, basically if you can get the "Copy" code working for one Main Record ID, then the list box code simply loops the Main ID's that selected in the listbox.
(And does the "Copy" for each one...

;-)

Jeff
skennellyAuthor Commented:
After much trial and error, I was able to solve it with the following code. I have only pasted the part of the Procedure relevant to the question. Thanks.

    With Me.RecordsetClone

    For Each var In Me!cmboAthleteID.ItemsSelected
    Criteria = Me![cmboAthleteID].ItemData(var)


        'Duplicate the main record: add to form's clone.
        DoCmd.GoToRecord , , acNewRec
        Forms!frmWorkoutDetail!AthleteID = Criteria
        Forms!frmWorkoutDetail!WorkoutType = Forms!frmTemplateDetail!WorkoutType
        Forms!frmWorkoutDetail!WorkoutDate = Forms!frmTemplateDetail!WDate
        
        DoCmd.RunCommand acCmdSaveRecord
            lngID = Forms!frmWorkoutDetail!WorkoutID
            'Duplicate the related records for the Strength Detail Subform: append query.
            If Forms!frmTemplateDetail![frmTemplateDetailSub].Form.RecordsetClone.RecordCount > 0 Then
                strSQL = "INSERT INTO [qryStrengthDetail] (WorkoutID, ExerciseCategoryID, ExerciseID, ExerciseOrder, Reps, Weight, Cluster, ExerciseTime, Side, StrengthNotes, WorkoutCode) " & _
                    "SELECT " & lngID & " As NewID, ExerciseCategoryID, ExerciseID, ExerciseOrder, Reps, Weight, Cluster, Time, Side, Notes, Code " & _
                    "FROM [qryTemplateDetail] WHERE TemplateID = " & Forms!frmTemplateDetail.TemplateID & ";"
                DBEngine(0)(0).Execute strSQL, dbFailOnError
            Else
                MsgBox "There is no Strength Workout to duplicate."
            End If
                Forms!frmWorkoutDetail.frmStrengthDetailSub.Form.Requery
    
Next var

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skennellyAuthor Commented:
The bulk of the question was solved by myself, but the answers provided led me to research coding of multi-select list boxes so I would like to give partial credit. Thanks.
skennellyAuthor Commented:
I was able to solve it on my own.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.