By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 28 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Jul 28 '08 #2

P: n/a
I appreciate it guys; I'll try it later tonight.
Jul 28 '08 #3

P: n/a
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
Aug 1 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.