Using a form to collect data to be stored to many tables?

[quarky2001]

I'm a bit of a newbie to forms, but I've recently created a database primarily to keep track of our business' invoices (the structure I asked about here: https://www.accessforums.net/showthread.php?t=82434 )

My structure is like this (hoping this 3-way junction table could work, since I needed to add a way to have multiple products from one supplier on the same invoice, plus every item on the same invoice might be for a different job)





I'm alright when it comes to creating forms that would add a new supplier, or a new product, etc. but I'm struggling with how a form can create multiple records, in many different tables.

My question now is how to create a form where you can enter the following information:

SupplierName, InvoiceNumber, InvoiceDate (same for the entire orden), and then a sub-form, where you can enter, for each item purchased, ManufacturerName, ProductName, UnitOfMeasure (automatically shown based on tblProducts), UnitPrice (autofilled based on DefaultUnitPrice in tblSupplierProducts, but able to be changed on a one-time basis), and Quantity.

I probably need more guidance than I realize, but hoping someone can point me in the right direction as far as forms go.

[Gicu]

I think you need a main form bound to your invoices table, in that one you add a subform for InvoiceProducts with combo boxes to select supplierproductid (with its row source based on the tblSupplierProducts table or a query if you want to add the supplier name), jobs (with row source based on tblJobs).

Cheers,
Vlad

[kd2017]

You could try something like the attached