[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

How to duplicate records on a form

Hi,
  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.

Thanks.
0
sglee
Asked:
sglee
  • 4
  • 4
  • 3
  • +1
1 Solution
 
Nick67Commented:
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

'pseudocode
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
    .addnew
    !WhateverField = me.txtWhateverControl

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

....
    .update
end with
rs.movenext
loop

0
 
Jeffrey CoachmanCommented:
<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.>
Huh?

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


JeffCoachman
0
 
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Jeffrey CoachmanCommented:
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]=""
...etc
0
 
Jeffrey CoachmanCommented:
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?...
0
 
sgleeAuthor Commented:
Boag2000,
  Yes I replace those fields with new values.
  Question, what do you mean by " I am still hazy about on this ...",
0
 
Nick67Commented:
OK,

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
0
 
hnasrCommented:
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
0
 
Nick67Commented:
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)
    rs.MoveFirst
    Do While rs.EOF = False ' Or rs.RecordCount > 4)
        rs1.AddNew
        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
        rs1.Update
        rs.MoveNext
    Loop
End If

Open in new window

0
 
Jeffrey CoachmanCommented:
<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.

;-)

JeffCoachman
0
 
sgleeAuthor Commented:
Nick67:

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

InvoceForm-Screenshot.doc
0
 
Nick67Commented:
Ok,

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
    .AddNew
    !InvoiceDate = Me.InvoiceDate
    !InvoiceAmount = Me.InvoiceAmount
    .Update
    .Bookmark = .LastModified
    lngInvoice_NO = ![Invoice_NO]
End With
rsAdd.Close
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)
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount
Do Until rs.EOF
    With rsAdd
        .AddNew
        !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
        .Update
    End With
rs.MoveNext
Loop

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

Open in new window

Duplicate.mdb
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now