allenbrowne's code for duplicating records

  • Thread starter Thread starter mhmaid
  • Start date Start date
M

mhmaid

hi
I have a db which has same idea as the sample db expenses where there are
three main tables
patients
expense reports
expense details

now , I am trying to use the following code to duplicate record in both the
main form and the subform ( exactly as in the above mentioned sample) which
is available on microsoft site

Private Sub cmdDupe_Click()

'On Error GoTo Err_Handler

'Purpose: Duplicate the main form record and related records in the
subform.

Dim strSql As String 'SQL statement.for expense reports

Dim lngID As Long 'Primary key value of the new record.



'Save and edits first

If Me.Dirty Then

Me.Dirty = False

End If



'Make sure there is a record to duplicate.

If Me.NewRecord Then

MsgBox "Select the record to duplicate."

Else

'Duplicate the main record: add to form's clone.

With Me.RecordsetClone

.AddNew





'Save the primary key value, to use as the foreign key for the
related records.

.Bookmark = .LastModified

lngID = !PATIENTID



'Duplicate the related records: append query.

If Me.[Patients Subform].Form.RecordsetClone.RecordCount > 0 Then

strSql = "INSERT INTO [Expense Reports main] ( patientid,
BatchId, BatchNumber, BatchDate, DocumentType, Cpv, CpvDate,
EntryReferenceId, EntryReferenceAmount, ROE, FcAmountXR, Currency,
TransactionDate, LastUpdateby, ChqNo, PostingRestriction ) " & _

"SELECT " & lngID & " As NewID, BatchId, BatchNumber,
BatchDate, DocumentType, Cpv, CpvDate, EntryReferenceId,
EntryReferenceAmount, ROE, FcAmountXR, Currency, TransactionDate,
LastUpdateby, ChqNo, PostingRestriction " & _

"FROM [Expense reports main] WHERE patientid = " &
Me.PATIENTID & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError

Else

MsgBox "Main record duplicated, but there were no related
records."

End If



'Display the new duplicate.

Me.Bookmark = .LastModified

End With

End If



Exit_Handler:

Exit Sub



Err_Handler:

MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"

Resume Exit_Handler

End Sub



but the subform which is "employees subform" in the sample has two tables as
source.
what should i do to make the code duplicate for the 3 tables.


note:actully , i have tried using the code for the firts two tables only,
just to test , but unforutnatly i get error in the line:

lngID = !PATIENTID

saying invalide use of null

hope someone can help with this.
as i said , my db is only a developed copy of the sample db " expenses"
 
hi
I have a db which has same idea as the sample db expenses where there are
three main tables
patients
expense reports
expense details

now , I am trying to use the following code to duplicate record in both the
main form and the subform ( exactly as in the above mentioned sample) which
is available on microsoft site

Private Sub cmdDupe_Click()

'On Error GoTo Err_Handler

'Purpose: Duplicate the main form record and related records in the
subform.

Dim strSql As String 'SQL statement.for expense reports

Dim lngID As Long 'Primary key value of the new record.

'Save and edits first

If Me.Dirty Then

Me.Dirty = False

End If

'Make sure there is a record to duplicate.

If Me.NewRecord Then

MsgBox "Select the record to duplicate."

Else

'Duplicate the main record: add to form's clone.

With Me.RecordsetClone

.AddNew

'Save the primary key value, to use as the foreign key for the
related records.

.Bookmark = .LastModified

lngID = !PATIENTID

'Duplicate the related records: append query.

If Me.[Patients Subform].Form.RecordsetClone.RecordCount > 0 Then

strSql = "INSERT INTO [Expense Reports main] ( patientid,
BatchId, BatchNumber, BatchDate, DocumentType, Cpv, CpvDate,
EntryReferenceId, EntryReferenceAmount, ROE, FcAmountXR, Currency,
TransactionDate, LastUpdateby, ChqNo, PostingRestriction ) " & _

"SELECT " & lngID & " As NewID, BatchId, BatchNumber,
BatchDate, DocumentType, Cpv, CpvDate, EntryReferenceId,
EntryReferenceAmount, ROE, FcAmountXR, Currency, TransactionDate,
LastUpdateby, ChqNo, PostingRestriction " & _

"FROM [Expense reports main] WHERE patientid = " &
Me.PATIENTID & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError

Else

MsgBox "Main record duplicated, but there were no related
records."

End If

'Display the new duplicate.

Me.Bookmark = .LastModified

End With

End If

Exit_Handler:

Exit Sub

Err_Handler:

MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"

Resume Exit_Handler

End Sub

but the subform which is "employees subform" in the sample has two tables as
source.
what should i do to make the code duplicate for the 3 tables.

note:actully , i have tried using the code for the firts two tables only,
just to test , but unforutnatly i get error in the line:

lngID = !PATIENTID

saying invalide use of null

hope someone can help with this.
as i said , my db is only a developed copy of the sample db " expenses"
Click to expand...

This routine is merely running an append query wrapped with a little
bullet-proofing. To give you a little more information about what it's
doing:

1. It adds a new record and collects newly-generated primary-key (PK).
2. It uses an append query to insert duplicate values into table 2,
substituting the new PK value for the second form's foreign key.

All you have to do is carry out step two again with the third table
and you should be home and dry.

-- James
 
How are the tables related? It looks like there are two 1 to many
relationships:

1 MANY
-------------------------------------
patients ----> [expense reports]

patients ----> [expense details]


What is the primary key field name for the one table and what are the
foreign key field names for the many tables?

What is the record source for the main form? And what are the record sources
for the subforms?

There is a problem with the code. When you modified the code from Allen
Browne's site, you cut out too much. Right now, the code (almost) adds a
blank record to the RecordsetClone of the main form. the .UpDate is missing
as well as the field data you want to copy. Look at Allen's code again
starting at

'Save the primary key value, to use as the foreign key for the related
records.
With Me.RecordsetClone


Also, in the strSQL lines, it looks like you have the subform name or the
subform control name in the Insert Into statement instead of the *table*
name. Remember the form is for VIEWING and the table is for STORING the data.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


mhmaid said:
hi
I have a db which has same idea as the sample db expenses where there are
three main tables
patients
expense reports
expense details

now , I am trying to use the following code to duplicate record in both the
main form and the subform ( exactly as in the above mentioned sample) which
is available on microsoft site

Private Sub cmdDupe_Click()

'On Error GoTo Err_Handler

'Purpose: Duplicate the main form record and related records in the
subform.

Dim strSql As String 'SQL statement.for expense reports

Dim lngID As Long 'Primary key value of the new record.



'Save and edits first

If Me.Dirty Then

Me.Dirty = False

End If



'Make sure there is a record to duplicate.

If Me.NewRecord Then

MsgBox "Select the record to duplicate."

Else

'Duplicate the main record: add to form's clone.

With Me.RecordsetClone

.AddNew





'Save the primary key value, to use as the foreign key for the
related records.

.Bookmark = .LastModified

lngID = !PATIENTID



'Duplicate the related records: append query.

If Me.[Patients Subform].Form.RecordsetClone.RecordCount > 0 Then

strSql = "INSERT INTO [Expense Reports main] ( patientid,
BatchId, BatchNumber, BatchDate, DocumentType, Cpv, CpvDate,
EntryReferenceId, EntryReferenceAmount, ROE, FcAmountXR, Currency,
TransactionDate, LastUpdateby, ChqNo, PostingRestriction ) " & _

"SELECT " & lngID & " As NewID, BatchId, BatchNumber,
BatchDate, DocumentType, Cpv, CpvDate, EntryReferenceId,
EntryReferenceAmount, ROE, FcAmountXR, Currency, TransactionDate,
LastUpdateby, ChqNo, PostingRestriction " & _

"FROM [Expense reports main] WHERE patientid = " &
Me.PATIENTID & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError

Else

MsgBox "Main record duplicated, but there were no related
records."

End If



'Display the new duplicate.

Me.Bookmark = .LastModified

End With

End If



Exit_Handler:

Exit Sub



Err_Handler:

MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"

Resume Exit_Handler

End Sub



but the subform which is "employees subform" in the sample has two tables as
source.
what should i do to make the code duplicate for the 3 tables.


note:actully , i have tried using the code for the firts two tables only,
just to test , but unforutnatly i get error in the line:

lngID = !PATIENTID

saying invalide use of null

hope someone can help with this.
as i said , my db is only a developed copy of the sample db " expenses"
Click to expand...
 
thank you for reply

I have three tables

Patients
Expense Reports
Expense Details

relation ship like this

patients----Expense reports----expense details

one patient may have many expense reports
one expense report may have many expense details

primary keys:
patients=Patientid
Expense reports=ExpenseReportid
Expense details=ExpenseDetailsid

the record source for the main for which "Expense reports by patient" is the
table patients

the record source for the subform which is named as "patients subform " is

like this example( as i am not at office now and dont have the db with me at
home.but this is a simillar sample, same idea , I took it from the sample db
expenses) using the join between two tables : expense reports and expense
details


SELECT DISTINCTROW [Expense Reports].ExpenseReportID, [Expense
Reports].ExpenseRptName, [Expense Reports].DateSubmitted, [Expense
Reports].AdvanceAmount, Sum([Expense Details].ExpenseItemAmount) AS [Total
Expenses], [Expense Reports].Paid FROM [Expense Reports] LEFT JOIN [Expense
Details] ON [Expense Reports].ExpenseReportID=[Expense
Details].ExpenseReportID WHERE (([Expense Reports].patientID=forms![Expense
Reports by patient]!patientID)) GROUP BY [Expense Reports].ExpenseReportID,
[Expense Reports].ExpenseRptName, [Expense Reports].DateSubmitted, [Expense
Reports].AdvanceAmount, [Expense Reports].Paid;

actully , although i am using access for about two year , but i am not
fimiliar with codes , that why i need some help :

1.how to save the primary key Expenserportid to use it for the next sql
which is for the third table "expense details"

is it like this:
Bookmark = .LastModified

lngID = !Expensereportid
and where should I put it

2.what i should put here for the slq 2

FROM [Expense reports ] WHERE patientid = " &
Me.PATIENTID & ";"
should it be like this

FROM [Expense details] WHERE Expensereportid= " &
Me.Expensereportid & ";"
or it will not work her because the control expensereportid is not in the
main for , it is in the subform?

i have tried and i reached this stage

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim strsql2 As String 'for expense details (is this right like this)

Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.newrecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!TransactionDate = Date
!DiagnosisCategoryId = Me.DiagnosisCategoryId
!Cpr = Me.Cpr
!CountryId = Me.CountryId
!AuthorityId = Me.AuthorityId
!PatientNameId = Me.Patient
!Gender = Me.Gender
!CaseCategory = Me.CaseCategory
'Enter all the fields required
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PatientId
'Duplicate the related records: append query.
If Me.[patients Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = " INSERT INTO [Expense Reports] ( BatchId,
BatchNumber, BatchDate, DocumentType, Cpv, CpvDate, EntryReferenceId,
EntryReferenceAmount, ROE, FcAmountXR, [Currency], TransactionDate,
LastUpdateby, ChqNo, PostingRestriction )" & _
"SELECT " & lngID & " As NewID, BatchNumber, BatchDate,
DocumentType, Cpv, CpvDate, EntryReferenceId, EntryReferenceAmount, ROE,
FcAmountXR, [Currency], TransactionDate, LastUpdateby, ChqNo,
PostingRestriction " & _
"FROM [Expense Reports] WHERE patientid = " &
Me.patientid & ";"

'not sure about it
strSql2 = "INSERT INTO [Expense Details] ( ExpenseReportID,
ExpenseCategoryId, InvoiceIssuerCategoryId, ExpenseDescriptionId,
ExpenseItemDetails, AccountingDate, InvoiceIssuerId, TransactionDate, Period,
AdvancePayment, PostedExpenseDetails, Deductions, Rejected, Discounts,
InvCat, InvPart, ExpenseItemAmount ) " & _
"SELECT " & lngID & " As NewID, ExpenseCategoryId,
InvoiceIssuerCategoryId, ExpenseDescriptionId, ExpenseItemDetails,
AccountingDate, InvoiceIssuerId, TransactionDate, Period, AdvancePayment,
PostedExpenseDetails, Deductions, Rejected, Discounts, InvCat, InvPart,
ExpenseItemAmount " & _
"FROM [Expense Details] WHERE Expensereportid = " &
Me.Expensereportid & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If



'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
 
I am really confused...probably your naming convention.

I have three tables

Patients
Expense Reports
Expense Details

relation ship like this

patients----Expense reports----expense details

one patient may have many expense reports
one expense report may have many expense details
Click to expand...

Why do you want to duplicate the patient in the patient table?? If one
patient can have many expense reports, then the patient info should be
entered only once in the patient table.

I would think you would only duplicate the selected expense report entry for
that patient. If you duplicate all of the expense reports and all of the
expense details, most of the entries will be wrong. (GIGO!!)

If you *really do* want to duplicate the selected patient and all of the
related expense report entries related to the patient *and* all of the
expense detail entries for each of the related expense report entries, you
will have to create a couple of recordsets and loop thru them.



BTW, you should read up on normalization and normalize your tables (at least
to the 3rd normal form). I don't see how the "TransactionDate",
"DiagnosisCategoryId" and "Cpr" are attributes of a person (patient).


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


mhmaid said:
thank you for reply

I have three tables

Patients
Expense Reports
Expense Details

relation ship like this

patients----Expense reports----expense details

one patient may have many expense reports
one expense report may have many expense details

primary keys:
patients=Patientid
Expense reports=ExpenseReportid
Expense details=ExpenseDetailsid

the record source for the main for which "Expense reports by patient" is the
table patients

the record source for the subform which is named as "patients subform " is

like this example( as i am not at office now and dont have the db with me at
home.but this is a simillar sample, same idea , I took it from the sample db
expenses) using the join between two tables : expense reports and expense
details


SELECT DISTINCTROW [Expense Reports].ExpenseReportID, [Expense
Reports].ExpenseRptName, [Expense Reports].DateSubmitted, [Expense
Reports].AdvanceAmount, Sum([Expense Details].ExpenseItemAmount) AS [Total
Expenses], [Expense Reports].Paid FROM [Expense Reports] LEFT JOIN [Expense
Details] ON [Expense Reports].ExpenseReportID=[Expense
Details].ExpenseReportID WHERE (([Expense Reports].patientID=forms![Expense
Reports by patient]!patientID)) GROUP BY [Expense Reports].ExpenseReportID,
[Expense Reports].ExpenseRptName, [Expense Reports].DateSubmitted, [Expense
Reports].AdvanceAmount, [Expense Reports].Paid;

actully , although i am using access for about two year , but i am not
fimiliar with codes , that why i need some help :

1.how to save the primary key Expenserportid to use it for the next sql
which is for the third table "expense details"

is it like this:
Bookmark = .LastModified

lngID = !Expensereportid
and where should I put it

2.what i should put here for the slq 2

FROM [Expense reports ] WHERE patientid = " &
Me.PATIENTID & ";"
should it be like this

FROM [Expense details] WHERE Expensereportid= " &
Me.Expensereportid & ";"
or it will not work her because the control expensereportid is not in the
main for , it is in the subform?

i have tried and i reached this stage

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim strsql2 As String 'for expense details (is this right like this)

Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.newrecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!TransactionDate = Date
!DiagnosisCategoryId = Me.DiagnosisCategoryId
!Cpr = Me.Cpr
!CountryId = Me.CountryId
!AuthorityId = Me.AuthorityId
!PatientNameId = Me.Patient
!Gender = Me.Gender
!CaseCategory = Me.CaseCategory
'Enter all the fields required
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !PatientId
'Duplicate the related records: append query.
If Me.[patients Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = " INSERT INTO [Expense Reports] ( BatchId,
BatchNumber, BatchDate, DocumentType, Cpv, CpvDate, EntryReferenceId,
EntryReferenceAmount, ROE, FcAmountXR, [Currency], TransactionDate,
LastUpdateby, ChqNo, PostingRestriction )" & _
"SELECT " & lngID & " As NewID, BatchNumber, BatchDate,
DocumentType, Cpv, CpvDate, EntryReferenceId, EntryReferenceAmount, ROE,
FcAmountXR, [Currency], TransactionDate, LastUpdateby, ChqNo,
PostingRestriction " & _
"FROM [Expense Reports] WHERE patientid = " &
Me.patientid & ";"

'not sure about it
strSql2 = "INSERT INTO [Expense Details] ( ExpenseReportID,
ExpenseCategoryId, InvoiceIssuerCategoryId, ExpenseDescriptionId,
ExpenseItemDetails, AccountingDate, InvoiceIssuerId, TransactionDate, Period,
AdvancePayment, PostedExpenseDetails, Deductions, Rejected, Discounts,
InvCat, InvPart, ExpenseItemAmount ) " & _
"SELECT " & lngID & " As NewID, ExpenseCategoryId,
InvoiceIssuerCategoryId, ExpenseDescriptionId, ExpenseItemDetails,
AccountingDate, InvoiceIssuerId, TransactionDate, Period, AdvancePayment,
PostedExpenseDetails, Deductions, Rejected, Discounts, InvCat, InvPart,
ExpenseItemAmount " & _
"FROM [Expense Details] WHERE Expensereportid = " &
Me.Expensereportid & ";"

DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

DBEngine(0)(0).Execute strSql, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If



'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Click to expand...
 
thanks for reply

"> Why do you want to duplicate the patient in the patient table?? If one
patient can have many expense reports, then the patient info should be
entered only once in the patient table.
Click to expand...

Actually , I forgot to mention one important thing here. one patient may be
treated many times. so , for each visit , there should be a new record in the
table patients
as we have to count the visit as like a new case , and should be able to
find total cost for each visit.and the record will hold also the strart and
end date for the treatment during each visit which may be one day or in some
cases upto one year or more.

BTW, you should read up on normalization and normalize your tables (at least
to the 3rd normal form). I don't see how the "TransactionDate",
"DiagnosisCategoryId" and "Cpr" are attributes of a person (patient).
Click to expand...


the control : Diagnosiscategoryid is like Cardiac
and i have another control for diagnosis details like: tetrollogy of fallot

i need this to prepare reports based on the categories most of the times
the control Transactiondate=Date Entered
this is usful to refer in future for checking or search
cpr = personal number

so the table patients is actully holding the visits for all the patients ,
and one patienet may have more than one visit, may be its better to rename
the control patientid to CaseId

also, we may treat one patient first time under the category cardiac , next
time "may be after many years" under opthalmology and etc. so, i have to keep
a record for each visit of treatment in the table patients
 
Actually , I forgot to mention one important thing here. one patient may be
treated many times. so , for each visit , there should be a new record in the
table patients
Click to expand...

That's contradictory.

A Patient is one type of entity - a human being, with a name, birthdate, and
other personal attributes.

A visit is a different type of entity - involving a patient, a healthcare
provider, a date and time of the visit, and so on.

It makes NO sense to store visit data in the patient table or vice versa!

You need two tables in a one to many relationship. Enter biographical data
about the patient - once! - in the Patients table. Store just the unique
PatientID in the Visits table along with the other attributes of the visit.


John W. Vinson [MVP]
 
You need two tables in a one to many relationship. Enter biographical data
about the patient - once! - in the Patients table. Store just the unique
PatientID in the Visits table along with the other attributes of the visit.


John W. Vinson [MVP]
Click to expand...

Hi
I think that I should start sepletting the table to two tables
thank you for this suggestion
 
Back
Top