Multiple lines on an invoice

B

Beaner200

Hi

I have 2 tables linked together by ClientID. The main table is the Client
table and the second table is the Invoice table.

I have created a form that will produce an invoice and the main part of it
has the client ID, name, address, etc. Then I created a subform, based on a
query of the invoice table. This subform, is in datasheet view so the user
can enter more than 1 item into the invoice details. These 2 forms are
linked by clientID and/or InvoiceNo. I have tried both and am getting the
same results.

The problem that I am experiencing is that when I am entering more than 1
line of invoice items into the subform, each line is generating a new invoice
number. I would like the invoice number to remain the same as the invoice
number automatically genterated on the main form.

The invoice number in the Invoice table is set to indexed, duplicates ok.

Thanks
 
P

PC Datasheet

First, you need another table, InVoiceDetails. The invoice table should hold
the information about the Invoice as a whole such as CustomerID and Invoice
Date and the InvoiceDetails table should hold the information about the line
items, Qty, ProductID, Price, Discount and Extended Price. See the Northwind
example database for how the tables are designed. You then create a subform
based on the InvoiceDetails table and add the subform to the Invoice form.
Then you add the Invoice form to the Client form as a subform.
 
B

Beaner200

That worked wonderfully. THANKS!!
Now what I have come across is calculating the fields in the invoice. In
the subform, is the Price, PST, GST. There are multiple lines of items that
will be on one invoice. I am trying to get the invoice to subtotal Price,
PST and GST.

I have put the code: =frmInvoiceSubform.Form!ClassPrice
I tried putting it in the form footer and in the details sections, but it is
only calculating 1 row at a time, and I need it to calculate all rows.

Thanks in advance for any assistance.
 
P

PC Datasheet

You need to calculate the totals in a total field in the subform and then
refer to the total field in the main form. For the total field in the
subform, your fields are probably all calculated fields:
Price = Quantity x Unit Price
PST = PSTRate X Price
GST = GSTRate x Price
So add a textbox named TotalPrice in the Pagefooter of the subforn and put
this expression in the controlsource:
=Sum(Quantity *UnitPrice)
Do the same for PST and GST.
In the mainform add a textbox below the subform and put this expression in
its controlsource:
=[NameOfSubformControl].Form!TotalPrice
Do the same for PST and GST.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com
 
Top