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

[6afraidbecause789@gmail.com]

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
jtblStaffandInt s, 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]

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

6afraidbecause7 89@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
jtblStaffandInt s, 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","Your Table")

'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@gmail.com]

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

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

[6afraidbecause789@gmail.com]

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

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!frmInterv entions.txtPrev iousIntID 'Where on the
main form's open event, Me.txtPreviousI ntID = Me!sfrmIntsPast .Form!
InterventionID (renamed the 'DB' subform from above)
MaxID = DMax("Intervent ionID", "qryStaffAndInt s2") '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 jtblStaffAndInt s ( InterventionID, StaffID,
RoleID ) " & _
"SELECT " & MaxID & ", StaffID, RoleID " & _
"FROM jtblStaffAndInt s " & _
"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.txtCo unt = 0 Then
Me.Parent.cmdAp pend.Visible = False
Else
Me.Parent.cmdAp pend.Visible = True
End If