Search code examples
ms-access-2016

Creating a duplicate record in MS Access using Append Query

I have a table "Tbl_Invoice" with invoice details [Primary key "InvID"] and another table "Tbl_Invoice_Details" with the invoiced item details (items, quantity purchased, rate, amount, total) [primary key "InvDetID" and foreign key "InvID" linked to invoice table]. In the main form "Frm_Invoice" with data source "Tbl_Invoice", I have included a subform with item details from the "Tbl_Invoice_Details". I have two problems -

  1. The subform shows the total of all the items purchased in the footer (using the sum function from the menu bar). But I am unable to bring the total of the subform to the main form where the discount is calculated, tax added, rounded off (if necessary) and calculates the invoice amount.
  2. I want to create a duplicate record (total invoice with items, etc.) only the customer name, invoice no., date changes. I am using two separate append queries, one for the invoice details and the other for the invoiced item details. Both works. The problem is I am unable to link the new records in the subform/subtable "Tbl_Invoice_Details" with the new record in the main form / main table "Tbl_Invoice".

I am a newbie and do not have much knowledge of VBA. Please help!

Solution

  • As for your second question, use the RecordsetClone of the main form and the subform respectively to copy the main record and the subrecords:

    Duplicate records in Subform to New record