1

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!

Divine Atman
  • 23
  • 2

2 Answers2

0

For Question one look at the two images, i have attached.

Design view for form

Form view showing outcome

Name of subform in my case is BILLINGDETAILS Name of control in subform is TOTALPAID,with caption Text2(caption is not relevant to the outcome)

Look closely at how the TOTALPAID control in subform is referenced from the Main form.

For your question two, if you can share the tables relationship window and image of the form link fields , you will get someone in this forum that will answer it.

Sola Oshinowo
  • 519
  • 4
  • 13
  • I tried your method using the following code - =[Amount]=Sum([Inv_Details_SubF].[Form]![Txt_Amount]) ,but it shows error. – Divine Atman Feb 21 '22 at 11:55
  • I tried adding an unbound textbox in the subform where I put the formula =Sum([Txt_Amount]) but still is show #Error. – Divine Atman Feb 21 '22 at 12:10
  • what type of field is txt_amount in tha table. it must be a numeric data type – Sola Oshinowo Feb 21 '22 at 13:02
  • The field txt_Amount is a currency field in the table. Should I change it to a number? – Divine Atman Feb 21 '22 at 21:46
  • Edit - I am adding a screenshot of the table design view and relationship among the related tables if it helps. ![Invoice table][1] ![Invoice details table][2] ![Relation between tables][3] [1]: https://i.stack.imgur.com/6CrLb.jpg [2]: https://i.stack.imgur.com/yukip.jpg [3]: https://i.stack.imgur.com/jyY1s.jpg – Divine Atman Feb 21 '22 at 22:15
  • No, a currency field is a type of number, so that aspect is settled. I thought in your post, you stated that you have a sum function at the footer, but didn't know how to bring it to show at the main form? – Sola Oshinowo Feb 21 '22 at 22:27
  • Yes, I added a footnote and then used your formula and it worked. Thank you. – Divine Atman Feb 22 '22 at 12:00
  • Happy to help,do mark the question as answered. – Sola Oshinowo Feb 22 '22 at 16:59
0

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

Gustav
  • 53,498
  • 7
  • 29
  • 55