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

hooolagon

New member
Local time
Today, 23:28
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

Last edited:

hooolagon

New member
Local time
Today, 23:28
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, 23:28
Joined
Jul 9, 2003
Messages
11,005
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:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom