Subform Record Duplicator

Subform Record Duplicator

Nifty Access – “Subform Record Replicator” allows you to provide your user with the facility to quickly add sets of duplicate records in a subform. All they need do is specify the number of Records they want to add, select a record to copy, and hey presto you’ve got a load of copies.

Subform Record Duplicator

Subform Record Duplicator

Video 1 (3:46)

The video alongside demonstrates how to use it. The video also shows how to set it up in your MS Access application. Just add a “Command Button” next to your subform, along with a “Text Box” to indicate the number of rows you want to add. Have the command button call the function. Provide the function with two parameters, the first one being the name of the subform window (Subform/Subreport Control) the one containing the subform you want to add the records to. Also provide the name of the textbox containing the number of Records you want to add…

The “Free Option” in this case requires a bit of work. Go to the original thread:- Repeat Subform last field entry x amount of times using command button  where I answered the question which prompted me to create this Subform Record Duplicator. In the thread you will find the original code. Modify this code for your particular subform.

I’ve recently updated my answer to the OP’s question and improved the sample solution file. It’s available from my download provider for free. Click on the button below:-

Video 1 (3:46)

Nifty Access YouTube Thumb Nail
Play Video

Video Index

00:03  when you press the command button it has five records
00:08 but you need to select a record, if a records not selected it won’t work
00:20 to set this up you need the module “basGenericAdd”
00:26 you also need to know the name of the subform/subreport control containing the subform
00:34 I refer to it as a subform window
00:37 I gave it the name “subFrmWinStudent
00:46 go to the code behind form
00:54 you need to call the function “fAddSubfrmRecords”
00:59 you need to include the name of the subform window containing the subform you want the code to operate on
01:07 you also need to tell it where to find the number of Records to add
01:29 you need a particular record source for the subform
01:50 changing the record source to directly access the table causes the code to fail
02:00 you get a nice message box explaining the issue
02:32 what you need to do is create a query
02:47 go to the SQL view of the query and copy out the text
02:59 however watch out there’s a gotcha” Hidden in the text there is a “Control Line Feed” (Vbcrlf)
03:05 simply fix it by moving the text onto one line
03:21 go to the subform students
03:27 go to the property sheet
03:29 select the forms property “Record Source” and pressed paste
03:37 and now look it’s adding 5 records correctly

… …

Simpler, but not so flexible method:-

					Private Sub Command13_Click()
Dim X As Integer

    If Not IsNull(Me.subFrmWinT2.Form!txtID) Then
        For X = 1 To Me.txtInsertThisMany
            CurrentDb.Execute fSQL_SelectedRecord(Me.subFrmWinT2.Form!txtID)
        Next X
        MsgBox " >>> Please Select a Row Containing DATA!!!"
    End If

End Sub

Private Function fSQL_SelectedRecord(lngID As Long) As String

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

strSQL1 = "INSERT INTO Table2 (table1ID, customID ) "
strSQL2 = "SELECT table1ID, customID "
strSQL3 = "FROM Table2 "
strSQL4 = "WHERE (((ID)="
strSQL5 = "));"

strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4 & lngID & strSQL5

fSQL_SelectedRecord = strSQL0

End Function      'fSQL_SelectedRecord

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.