Use a 2 field key to copy additional fields into a subform

[photoed]

I have a small photo printing business that uses a database to track jobs and prepare invoices. There are 2 job files, a Job_Header, and an associated Job_Part file. A given job will have a single Job_Header with a job number, customer data, dates, total cost for the job, etc. That Job_Header will be hooked to one or more job parts by the job number. The job parts will have a field with a single letter to distinguish the parts. This forms a 2 field, unique key for the Job_Part file.



I also have 2 invoice files, an Invoice_Header and an Invoice_Line file. These are hooked by the invoice number. The Invoice_Header has customer info, invoice totals, date, etc. The Invoice_Line has the invoicing information for a single Job_Part. To prepare an invoice for a particular job you will prepare the Invoice_Header, a main form, then an Invoice_Line for each Job_Part, a subform. It is possible to invoice only part of a job on a particular invoice or to invoice more than one job on an invoice by including those job parts (in an invoice line) as appropriate.

The Invoice_Line has a field for the job number and the letter that represents that Job_Part. There are also a number of other fields in the Invoice_Line that match fields in the Job_Part file, such as quantity and price for that Job_Part. What I would like to do when preparing an invoice is to enter the job number and the letter representing the Job_Part (a 2 field, unique key) into the Invoice_Line and have the rest of the relevant fields in the Invoice_Line filled in from the Job_Part file. I have been unable to figure a way to do this. Can anyone give me any suggestions. Please keep in mind that my Access knowledge is pretty basic. Thanks.

[ranman256]

On a form , you'd have a combo box for Jobs. (Headers)
when you pick the job#' this will fill in a list box (job details)... its a query that looks at the cbo box.

here you create an Invoice. Dbl-click the job detail listbox ,to add 1 , some, or all job items.
This is an append query to add the item from the job detail list to the Invoice detail table.

a delete query lets you remove the item, in case you added the wrong one.