Repeat Subform last field entry x amount of times using command button (1 Viewer)

hooolagon

New member
Local time
Today, 01:04
Joined
May 7, 2017
Messages
6
Okay so I have a "table1" and a "table2". Table1 has a primary key autonumber which is one to many into table2, a date and a type field which has two defined lookup options. table2 only has the primary key from table1 as a number input and an ID field which is linked to table3 which contains the ID as a primary key.

So when I created a form for table1 it creates a main form and a subform using table2's only remaining field "ID". Perfect, here I can enter multiple IDs into a single table which creates multiple records automatically in table2.

Now I would like a button that would repeat the ID entry in the subform 5 times, preferably using VBA and I have no idea how to do it,

Once again, repeat the last entered field in the subform 5 times using a button

Thanks
 

Attachments

  • Repeat Subform 5 Times.accdb
    572 KB · Views: 226
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:04
Joined
Jul 9, 2003
Messages
16,245
You can download this sample file from my download provider at the following link:-

To get a free Copy, use the coupon code:- rep5times

There is a paid for version of the software, for more information see my website here:-
 
Last edited:

hooolagon

New member
Local time
Today, 01:04
Joined
May 7, 2017
Messages
6
Thank you, took me a while to understand your genius and implement it! Much appreciated
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:04
Joined
Jul 9, 2003
Messages
16,245
Heres a simpler, but not so flexible method:-

Code:
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
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:04
Joined
Jul 9, 2003
Messages
16,245
I've converted this "Sub-form Record Duplicator" into a drop-in component which you can find on my website here:-


Very simple to install just follow the video instructions.

Subform Record Duplicator - Nifty Access


I've also got around 40 other Downloads which you can peruse on Gumroad here:-
 
Last edited:

Users who are viewing this thread

Top Bottom