One order, multiple invoices

[qa12dx]

dt is short for date. access doesn't like u using certain names for field name, like Date.

as for tables, unless u r working w massive record volumes, break it down only when you need 1 record to relate to multiple records. here for example, u have a orderID, under which u can have multiple products etc.

making too many tables makes querys run slower as it has to do more processing. i feel the simplier the better.

i tend to make repatetive text value in a certain collumn, a seperate table. like Department. i store the id (autonumber) of the dept_tbl in the records table. when you join on number fields queries run faster. for text access would have to read the whole value, analyse and join. also index the field where you r storing this number.
also don't use spaces in name for fields in tables or queries. use underscore, for eg, time entered would be time_entered.....

[jerem]

ok thanks... I will try.

[qa12dx]

don't worry. make the db, when you get stuck post the db here i'll help you solve ur problem.

[jerem]

Thank you. Still working on it. The way I want to accomplish that is with tables: Order, OrderDetail, Invoice, InvoiceDetail. When Invoice button is clicked in the OrderDetail Form, a macro creates a record in the invoice table and as many records InvoiceDetail as there are corresponding records in OrderDetails. Then through a Form + subform, the user can delete or change quantities on the invoice. I just need to figure out that macro... probably using "for each record in"... and then use parameters, SetLocalVar and SetReturnVar. Will see if I manage to do that.

[qa12dx]

its easier than that. made this db to help another user. Click Example2 button. see how i did it. the subform is linked to form, and it will auto input id's..

[jerem]

Your example does not show me how to have more than one invoice per order. It only shows how to use form and subform to create multiple items for one order. What if now I want to create a partial invoice for that order? Say that the order has 3 items but I want to create 2 invoices with each time half of the quantities ordered? There should be 2 invoices against my OrderID and 3 InvoiceDetail items per OrderDetail ID.

[orange]

You may wish to review post #13 again - the general approach is given there.

Good luck with your project.

[burrina]

Sounds to me that he is asking for a Link Criteria to open the new form with the same data. I use a similar approach in my db that copies over the OrderId,name,address,city,state,etc.. Something like Forms![NewForm]!OrderID = [OldForm]!OrderID Anyway if you have your main form and subform linked correctly, copying over the data should be easy. Just my 2 Cents.

[burrina]

That is the proper way, make no mistake about it. Your issue a relational one which can be solved by code and design.

[jerem]

This is old but was never solved. The way I did this is by creating a QuoteInvoice table. When ever an Invoice is created against a quote, a record is created in the QuoteInvoice table with the Quote.ID and the Invoice.ID. Reconciliation is done with a query.