Filling a form box with one table data, saving into another table's data.

Hey! so I am writing an Invoice form but I need one thing different from normal: I don’t want past invoices to change when I change item prices. But I do want customer info to change when I change that.

How I am doing this is I have 4 tables. Customer, Stock, Invoice and InvoiceLine.

  • Customer is customer info
  • Stock is all stock info
  • Invoice is for storing Invoice Number, Date, Customer and Total.
  • InvoiceLine is for storing the product codes connected to the Invoice number and their price and other information at the time, quantity, price and line total.

So I want an Invoice form and InvoiceLine sub-form. But when I type an item code in the Code section of the InvoiceLine field I want it to Auto-populate the price and info fields in the InvoiceLine subform

Similarly in the main form I want to be able to type a customer code into the customer code field, and Auto-populate a few other boxes with the customer info, but this will not be saved into the invoice table.

Lastly I want the InvoiceLine Total to equal Product x Quantity and Invoice Total field to automatically add all the InvoiceLine Total fields.

So how do I go about this? Mainly what I can’t seem to find how to do is populate a field with something and save it as something else.

Thanks for the help.