Duplicate Only the Subform's Last Record--Not the Main

[6afraidbecause789]

Kudos to anyone who can explain this one--how to duplicate a group of
continuous records in a subform for use in a new subform PK ID. The 2
entry fields (combo boxes) in the subform are RoleID and StaffID, and
the PK ID is StaffandIntsID. The subform's data is put into
jtblStaffandInts, which joins a Staff and Intervention tables:

So, here's what the data could look like in the table:

StaffandIntsID InterventionID StaffID RoleID

41 24 3 1
42 24 4 2
43 24 13 3
44 24 2 4
45 24 20 5
46 24 99 6

So, for a new Intervention entered (InterventionID = 25), the data may
look like

47 25 3 1
48 25 4 2
49 25 13 3
50 25 2 4
51 25 20 5
52 25 99 6

But, the user must be able to change the staff names, such as the
first record, StaffID = 3, to some other staff. And, users must be
able to delete a staff in the new record.

This subform is linked to another subform in the main form: child
field = InterventionID, master = [sfrmIntsPastDB].Form!
[InterventionID]

Thanks

[Salad]

6a***************@gmail.com wrote:

Kudos to anyone who can explain this one--how to duplicate a group of
continuous records in a subform for use in a new subform PK ID. The 2
entry fields (combo boxes) in the subform are RoleID and StaffID, and
the PK ID is StaffandIntsID. The subform's data is put into
jtblStaffandInts, which joins a Staff and Intervention tables:

So, here's what the data could look like in the table:

StaffandIntsID InterventionID StaffID RoleID

41 24 3 1
42 24 4 2
43 24 13 3
44 24 2 4
45 24 20 5
46 24 99 6

So, for a new Intervention entered (InterventionID = 25), the data may
look like

47 25 3 1
48 25 4 2
49 25 13 3
50 25 2 4
51 25 20 5
52 25 99 6

But, the user must be able to change the staff names, such as the
first record, StaffID = 3, to some other staff. And, users must be
able to delete a staff in the new record.

This subform is linked to another subform in the main form: child
field = InterventionID, master = [sfrmIntsPastDB].Form!
[InterventionID]

Thanks

I know I don't understand the problem but...what the heck.

First, you could get the Max intervention ID. In this case it would be 24.
Dim strSQL As String
Dim dbs As Database
Dim MaxID As Long

MaxId = DMax("ID","YourTable")

'make an append query string to append the new records
strSQL = "INSERT INTO YourTable ( InterventID, StaffID, RoleID ) " & _
"SELECT " & MaxID + 1 & ", StaffID, RoleID " & _
"FROM YourTable " & _
"WHERE InterventID = " & MaxID

set dbs = currentdb
dbs.execute strSQL 'run the SQL statement

This will append all records from 24 into 25.

[6afraidbecause789]

I appreciate it guys; I'll try it later tonight.

[6afraidbecause789]

Here is my solution; thanks to your help. I put a cmd button
(cmdAppend) with the caption "Copy Staff to New Record" on the main
form. On its click event, I put this:

Dim strSQL As String
Dim dbs As Database
Dim PrevID As Long
Dim MaxID As Long

PrevID = Forms!frmInterventions.txtPreviousIntID 'Where on the
main form's open event, Me.txtPreviousIntID = Me!sfrmIntsPast.Form!
InterventionID (renamed the 'DB' subform from above)
MaxID = DMax("InterventionID", "qryStaffAndInts2") 'This query
filters interventions that are associated with only the current
student selected in main form

'make an append query string to append the new records
strSQL = "INSERT INTO jtblStaffAndInts ( InterventionID, StaffID,
RoleID ) " & _
"SELECT " & MaxID & ", StaffID, RoleID " & _
"FROM jtblStaffAndInts " & _
"WHERE InterventionID = " & PrevID

Set dbs = CurrentDb
dbs.Execute strSQL 'run the SQL statement

===============

cmdAppend's visible property is turned on/off depending on whether
there are any previous interventions entered for the student. So, I
hid a textbox, txtCount, in the footer of sfrmIntsPast that =
Count([InterventionID]).

Lastly, inside of sfrmIntsRecent, on the cmdbutton that 'saves and
goes to the staff subform,' I put:

If Me.Parent.txtCount = 0 Then
Me.Parent.cmdAppend.Visible = False
Else
Me.Parent.cmdAppend.Visible = True
End If