Solved

Duplicate Main/Sub Forms through MultiSelect List Box

Posted on 2012-03-28
9
286 Views
Last Modified: 2012-04-06
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.
0
Comment
Question by:skennelly
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37777934
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
0
 

Author Comment

by:skennelly
ID: 37778322
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778647
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778685
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:skennelly
ID: 37779914
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37783206
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
0
 

Accepted Solution

by:
skennelly earned 0 total points
ID: 37793359
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

0
 

Author Comment

by:skennelly
ID: 37793385
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.
0
 

Author Closing Comment

by:skennelly
ID: 37815434
I was able to solve it on my own.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now