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.

Hi

See the modified Db attached

On the Invoices form that opens at startup I have modified
the Record Source of the BillTo Combobox to include the Kilo Price

I then added an Unbound Textbox named "txtKiloPrice" to reference the Combobox ie =[BillTo].Column(3)

Then On the Subform I made a Reference to the UnboundTextbox ie =[Parent].[txtKiloPrice]

Hope this helps

Click to view attachment

Thanks so much for your response to my question, but I think I might not have been clear enough on the way I phrased it.

I don't think your solution gets me where I would like to be.

The Main form establishes the customer (and the invoice #)....which is the same for all records on the subform associated with that main form.

It was my hope to have the combo box on the main form show me all 60+ on the customer list and once I choose that...then enter the individual line items (items ordered) on the sub form. That part seemed to be working fine. So, I would have 1 main form (non-volatile invoice data) = Many subforms. (Items ordered that relate to the main form)

One use for the data included in the sub form is to create a packing list, so I thought that there was no need to re-enter the customer name on each invoice item (sub Form). This also seemed to be working fine.

The issue is, I believe it is more intuitive for the user to choose the Customer (and assign an invoice number) only once on the main form and then choose the product(s) included on this invoice on the sub form. It is then that the pricing should occur based on the dual criteria of what customer was chosen in the main form and what product in the sub form record.

Sorry I am being so wordy, but I'm trying to be unambiguous so I don't waste your time.

Hi

You need to rework your Data Input Form

You need a Main Form based on the Customer

Then subforms for the Products and the Invoice