I want to create batch invoices for members of an organization and need some help in design. I've been working on it for awhile and am stuck!
I have three tables: Invoices, InvoiceItems, and Products. From a form, I want to select the members and products and then write to two tables: Invoices and InvoiceItems. I've been successful in creating invoices for the selected members, but can't figure out a way to create the matching records in the InvoiceItems table.
Each new invoice has an InvoiceID, MemberID, and InvoiceDate. Each new InvoiceItem record has an InvoiceID, ProductID, and Quantity.
I'm sure this has been done before and I am trying to reinvent the wheel! Perhaps someone can just point me to an MS Access application that creates batch invoices....I would be very happy with that! I've searched but can't find one.
Any help anyone can give me would be greatly appreciated!
Take a look at the northwind database that usually gets shipped as a free sample with Access.
Take a look at:
- Orders table (like your Invoices table)
- Order Details table (like your InvoiceItem table)
- relationships (see how orders is related to order details). Need to do the same for your Invoices & InvoicedItem tables)
- Order form.
The order form is the key to what you are asking. It's a form and an embedded subform. The subform is related to the form using master and child fields in the subforms properties. In your case it will be InvoiceID for master and child. The easy way to create the form is:
- create the main form with the Invoice fields
- create another form with the InvoiceItem fields
- add a subform (using the subform icon) to the main form and you will be able to select the InvoiceItem form as the subform. The wizard will help to create the master/child links.
The form will allow you to enter header and detail info for your invoices and store it in the appropriate tables.
You must have created the one-many relationship between Invoice and InvoiceItem.
I have a form to create individual invoices and this works fine. What I am trying to do is create invoices in batches: ie, select a group of individuals, a number of products, and then automatically create invoices for each.