Duplicating a Record on a form across 5 tabs and 7 underlying tables

M

mbs4653

I have a form with 5 tabs, that reads data from 7 underlying tables (
or 2 tables per tab), and displays all correctly. But I need to b
able to duplicate any given record (and its corresponding informatio
on the other tabs) and append the new record to all the underlyin
tables.
When I create the duplicate record command on the main form, i
correctly duplicates the record showing on the 1st tab and 1s
underlying table. But it does not duplicate the info on the other tab
in the other tables. All tables are linked by an autonumber 'DrNum' I
which correctly increments the ID, but still does not duplicate th
other records.

Not really even sure how to go about doing this. Would greatl
appreciate help

Thank yo
 
G

Graham R Seach

You haven't stated what the relationships are between these tables, but it
may be as simple as requerying the data on them. I assume the data on the
tabs are on subforms. If so, requery each subform:
Me.fsubSubForm1.Form.Requery
Me.fsubSubForm2.Form.Requery
Me.fsubSubForm3.Form.Requery

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
M

mbs4653

Thank you Graham
All 7 tables are linked by an autonumber 'DrNum' field. I will try th
requery, but when I look in the underlying tables, only the 1st tab'
table shows the duplicated record(albeit with a new DrNum
 
M

mbs4653

I tried the Requery. That does not work. Nothing is duplicated in an
table except the master
 
G

Graham R Seach

Still not enough information. Please list the data source (table/query) for
each tab.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
M

mbs4653

Graham

‘Dr.Num’ is autoNumber and is common to all tables.

Tab 1 Data Source= Table:personalTbl (with 30 fields)

Tab 2 Data Source= Table:E_College (via a subform with 15 fields)

Tab 3 Data Source= Table:LicensingInfo (via a subform with 13 fields
and Specialty(thru another subform with 5 fields)

Tab 4 Data Source= Table:Residency (via a subform with 5 fields) an
Fellowship (via another subform with 5 fields)

Tab 5 Data Source= Table:Carrier (via a subform with 12 fields)

Hope this is enough info.

Thanks so much.
 
J

John Vinson

I have a form with 5 tabs, that reads data from 7 underlying tables (1
or 2 tables per tab), and displays all correctly. But I need to be
able to duplicate any given record (and its corresponding information
on the other tabs) and append the new record to all the underlying
tables.
When I create the duplicate record command on the main form, it
correctly duplicates the record showing on the 1st tab and 1st
underlying table. But it does not duplicate the info on the other tabs
in the other tables. All tables are linked by an autonumber 'DrNum' ID
which correctly increments the ID, but still does not duplicate the
other records.

Not really even sure how to go about doing this. Would greatly
appreciate help

Thank you
Click to expand...

I really have to be skeptical about this structure, and this whole
process. What is the nature of the data? Why is it necessary to
duplicate an arbitrary number of records, apparently storing data
redundantly?

The only way you'll be able to do it (AFAIK) is to run seven Append
queries. Bear in mind that the "child" tables should ***NOT*** be
linked autonumber to autonumber - the DrNum in the child table should
be a Long Integer, not an autonumber. How are the tables related
anyway? One to many (typical) or one to one (*very* rare and quite
possibly indicating improper normalization)?

John W. Vinson[MVP]
 
G

Graham R Seach

As John states, duplicating data is a bad idea, as is duplicating the DrNum
as an autonumber. It would be OK if DrNum is an autonumber in one table, and
a foreign key (Long Integer) in the others. But you still haven't told us
what information is being stored and how each table is related (except to
say that they all share DrNum).

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
M

mbs4653

Hope you guys are still with me as I had to take several days off....

The 7 underlying tables all contain information about a doctor and hi
office/offices. They are set up that one table list college info, on
lists residency info, another lists office info, etc. There is n
duplication of info across the tables, except for the doc number whic
is generated in the main table, and then is just a unique number in th
other tables.

When one goes to enter a new doctor, the main form automaticall
assigns that doc a number which is part of main table 'Doctor'. (thi
is the autonumber), which is on the main form. The various tabs tak
input for the college table, residency table, etc., all under tha
autonumber that was generated on the main 1st page. The autonumber i
only generated 1 time in the main and persists thru all 5 tabs and
tables. Adding a new doc and all the additional tables informatio
works smoothly.

However the customer wants to also be able to duplicate all th
information across the 5 tabs, -7 tables and have all that sam
information assigned to another doc number, where they can then go an
just edit a few fields, instead of having to type all in again.

As I said, the dup record command works well so far, in that it assign
a new doc number, and then copies all the info of the 1 tab to that ne
doc number, but does not copy the additonal tabs' info (residency
college, etc) to the other tables.

I don not know of a better way then to separate all the info. Ther
are over 100 fields. This is also how their system was 1st set up (th
table divisions) by someone else who then left them 3/4 way thru th
job
 
G

Graham R Seach

That sounds to me like you want to use one doctor's records as a template
for another. If that's the case, then you need to run several insert queries
to populate the tables as appropriate. You'll need to do that for ALL the
relevent tables, not just the doctor table. Something like this:

Private Sub DuplicateRecord_Click()
Dim sSQL As String
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lDoctorID As Long

On Error Goto Proc_Err

Set ws = DBEngine(0)
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDoctor", dbOpenDynaset)

'Open a transaction, so either all actions commit, or none do
ws.BeginTrans

'You need to copy the primary doctor table using a recordset
'so you can retrieve the primary key which is used below
With rs
.AddNew
!Surname = Me.txtSurname
!OtherNames = Me.txtOtherNames
!DOB = Me.txtDOB

lDoctorID = !DoctorID 'Get the primary key
.Update
End With

'Note: In all the following INSERT statements, do not include the
original record's primary key

'Copy the college info using the primary key retrieved earlier
sSQL = "INSERT INTO tblCollege (DoctorID, Field1, ...etc) VALUES (" &
lDoctorID & ",""" & Me.txtTextbox1 & """, ...etc")
db.Execute sSQL, dbFailOnError

'Copy the residency info using the primary key retrieved earlier
sSQL = "INSERT INTO tblResidency (DoctorID, Field1, ...etc) VALUES (" &
lDoctorID & ",""" & Me.txtTextbox2 & """, ...etc")
db.Execute sSQL, dbFailOnError

'Copy the office info using the primary key retrieved earlier
sSQL = "INSERT INTO tblOffice (DoctorID, Field1, ...etc) VALUES (" &
lDoctorID & ",""" & Me.txtTextbox3 & """, ...etc")
db.Execute sSQL, dbFailOnError

'Commit the transaction
ws.CommitTrans

Proc_Exit:
'Clean up
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Set ws = Nothing
Exit Sub

Proc_Err:
'Rollback the transaction and process the error message
ws.Rollback
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
 
Top