How to duplicate records on a form

  I have an access form which is essentially an invoice (just like the ones you receive from your vendor, it has Customer name/address/ph#, order date, order amount, invoice date .. etc  on parent table/ InvoiceMain table along with actual items (you bought) like item number, price, qty, ext cost ... etc on a child table/InvoiceItem table).
  I know that I can append a invoice record from InvoiceMain and invoice item records from InvoiceItem table into the temporary tables and append them back to InvoiceMain/InvoiceItem  tables using queires.

 But I am wondering if there is a simpler way of duplicating an existing invoice information - like a built-in function or something like that.

 I would appreciate your insight in advance.

Who is Participating?
Nick67Connect With a Mentor Commented:
Here's production code from where I do something similar.
This code finds the last record that macthes some conditions I set, and then duplicates it for the present record.

It's not completely applicable to what you are looking for, but it gives a good feel about how I attack this kind of requirement
'ok at this point I have bailed, or I have a jobid from a previous overhead with the same serial number and location

Set rs = db.OpenRecordset("Select * from tblOverheadResults where [JobID] =" & Nz(myJobID, 0) & ";", dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then
    Exit Sub
End If

response = MsgBox(mymessage, vbCritical + vbYesNo, "Add past data")

If response = vbNo Then
    Exit Sub
End If

If response = vbYes Then
    Set rs1 = db.OpenRecordset("Select * from tblOverheadResults where 1 = 2;", dbOpenDynaset, dbSeeChanges)
    Do While rs.EOF = False ' Or rs.RecordCount > 4)
        rs1!JobID = Me.JobID
        rs1!Number = rs!Number
        rs1!OverheadTypeID = rs!OverheadTypeID
        rs1!Manufacturer = rs!Manufacturer
        rs1!Make = rs!Make
        rs1!SerialNumber = rs!SerialNumber
        rs1!Length = rs!Length
        rs1!Capacity = rs!Capacity
End If

Open in new window

I am not sure what you are trying to accomplish.
Why would you want the same records to appear on the form twice?
If they are in fact separate records with duplicate information, what would you be doing with them?
If we created some method of getting a query to return each record twice, when you edited one, you'd be editing both form values.

Are you looking for some simple way, if there is a repeat sale, of duplicating the entirety of the data on the form so you could change a few relevant details and then commit the transaction?
That can be done.
It depends on your definition of 'simple' and your data.
It would involve a bit of recordset code--but no temporary tables

dim rs as recordset
dim lngInvoiceID as long 'catch the autonumber of the new record created
set rs = currentdb.openrecordset("select * from InvoiceMain where InvoiceID = " & me.invoiceID,dbopendynaset)
with rs
    !WhateverField = me.txtWhateverControl

    lngInvoiceID = ![InvoiceID]
end with
set rs = nothing
set rs = currentdb.openrecordset("select * from InvoiceItem where InvoiceID = " & me.invoiceID,dbopendynaset)
do until rs.eof
with rs
    !InvoiceID = lngInvoiceID 'use that captured InvoiceID from the first recordset work
    !WhateverItemField = me.txtWhateverItemControl

end with

Jeffrey CoachmanMIS LiasonCommented:
<I know that I can append a invoice record from InvoiceMain and invoice item records from InvoiceItem table into the temporary tables and append them back to InvoiceMain/InvoiceItem  tables using queires.>

Like Nick67, I would express the same concerns and would like to know the *specifics* behind this "Copy" you are requesting

This all being said, you can use code like this to duplicate the current record on a form (presumes that the PK is an autonumber field)

'Select the Current Record
DoCmd.RunCommand acCmdSelectRecord
'Copy the Current Record
DoCmd.RunCommand acCmdCopy
'Paste/Append the copied record
DoCmd.RunCommand acCmdPasteAppend

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

sgleeAuthor Commented:
boag2000 & Nick67,

 Thanks for the suggestions and I will try both methods later today and post results.
 To answer your question about "Why would you want the same records to appear on the form twice", I have contract customers where I invoice the same amount of dollars every month. The only difference from month to month woud be:
In InvoiceMain table: Invoice Date, Payment Rcvd Date, Check No, Chk Deposit Date ... etc.
In InvoiceItem Table: Month/Year in the description field like "Computer Service in April 2011", "Computer Service in May 2011" ... etc. In InvoiceItem table, there are usually from one to few transactions(records)

Since these are repeating sales with slight changes every month, I rather like to automate is as much as possible with least amount of code instead of creating temporary tables and queries.
Jeffrey CoachmanMIS LiasonCommented:
I am still a bit hazy on this, but it seems to me that you would then have to also automate the clearing out of the fields that will change.

So after you run the dupe code, run code like this:
Me.[Invoice Date]=""
Me.[Payment Rcvd Date]=""
Jeffrey CoachmanMIS LiasonCommented:
Are you sure it is more efficient to duplicate the record and then clear out this many fields, than it would be to simply create a new record and simply enter the values?...
sgleeAuthor Commented:
  Yes I replace those fields with new values.
  Question, what do you mean by " I am still hazy about on this ...",

So I guessed right.
The way I pseudocoded it is the way I'd attack it
The nice thing about doing it with recordset code is that you can leave out all the !FieldName = me.txtControlNames that you DON'T want to carry over.
At the end of the pseudocode, you'd want to move the form to the new record you'd create
One other approach.
In afterupdate of each field set the default value to the current value. New record will display these the default values in the required fields and you add enter the values for the rest of other fields.

thisField.DefaultValue = thisField.Value
Jeffrey CoachmanMIS LiasonCommented:
<Question, what do you mean by " I am still hazy about on this ...", >
Meaning that I don't know enough about the details of this system to make a definitive statement like:
   "OK, this is absolutely the best way to deal with this..."

So I can only post what I believe will be applicable, and you can try it for yourself....

Nick67 appears to have invested a fair amount of time into this, so consider his posts as well.


sgleeAuthor Commented:

I tried the following code and got an error on "SET RS = currentdb ..." line.
set rs = currentdb.openrecordset("select * from InvoiceMain where InvoiceID = " & me.invoiceID,dbopendynaset)

I included a screenshot of my invoicing form where it shows invoice header information along with invoice details (multiple records). I get an idea as to how you are approaching this and I like it because it does not involve any temporary tables or queries.

Can you revise your code using just a few fields from actual tables used in my system?
You can use
From InvoiceMain table: InvoiceDate, InvoiceAmount (INVOICE_NO as AutoNumber field, no duplicate)
From InvoiceDetail table: OrderDate, OrderDescription, Qty, Price, Amount   (INVOICE_NO as AutoNumber field, duplicate OK)
Form Names: Invoice_Mainform, Invoice_Items_Subform


I mocked it up in its entirety
If you use the forms, they were made in Access 2010.

I have never had great luck editing forms created in 2007/2010 with 2003.
Somehow they get corrupt
Dim rs As Recordset
Dim rsAdd As Recordset
Dim strCriteria As String

Dim lngInvoice_NO As Long 'catch the autonumber of the new record created
Set rsAdd = CurrentDb.OpenRecordset("select * from InvoiceMain where 1=2", dbOpenDynaset)
With rsAdd
    !InvoiceDate = Me.InvoiceDate
    !InvoiceAmount = Me.InvoiceAmount
    .Bookmark = .LastModified
    lngInvoice_NO = ![Invoice_NO]
End With
Set rsAdd = Nothing

Set rs = CurrentDb.OpenRecordset("select * from InvoiceDetail where Invoice_NO = " & Me.Invoice_NO, dbOpenDynaset)
Set rsAdd = CurrentDb.OpenRecordset("select * from InvoiceDetail where 1=2;", dbOpenDynaset)
MsgBox rs.RecordCount
Do Until rs.EOF
    With rsAdd
        !Invoice_NO = lngInvoice_NO 'use that captured InvoiceID from the first recordset work
        !OrderDate = rs!OrderDate
        !OrderDescription = rs!OrderDescription
        !Qty = rs!Qty
        !Price = rs!Price
        !Amount = rs!Amount
    End With

Set rs = Nothing
strCriteria = "Invoice_NO  = " & lngInvoice_NO
Set rs = Me.RecordsetClone
rs.FindLast strCriteria
Me.Bookmark = rs.Bookmark

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.