I am a volunteer (and fairly novice Access user) attempting to write an invoicing system for a not for profit that is doing a demonstration project to farm raise Siberian Sturgeon for caviar.
Because the 60+ customers (tblCustomers) they already have can negotiate prices on each of the 24 or so different products (tblProducts) that are sold I created (thanks to UtterAccess contributor Doctor9) a join table (tblCustomerPricing).
Now, I need to be able to apply the proper price once I have established the customer and product in the invoicing form.
The invoicing form contains a main form (frmInvoices_Caviar) in which a combo box allows me to choose the customer for the invoice and
a sub form (frmInvoicesSubformCaviar) in which a combo box allows me to choose the product for an invoice item.
There is also a text box (PricePerKilo) in the sub form which has as its control source the field PricePerKilo in tbl InvoiceItemsSubform. Yes, I know this duplicates data in another table, but I need to preserve it for history so write it to the invoice detail table at the time the record is created.
What I want to accomplish is to autofill the text box (PricePerKilo) once I have entered the customer info on the main form and the product info on the sub form.
any help or examples in how to write either an expression or code would be greatly appreciated.
A copy of the DB as it currently exists is attached.