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

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)

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
        
        Me.subFrmWinT2.Requery
    Else
        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)="
'2
strSQL5 = "));"

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

fSQL_SelectedRecord = strSQL0

End Function      'fSQL_SelectedRecord

Subform Record Duplicator