Sub Total a Sub Table

[Pringle]

I have a simple Invoice System. There is a header table that contains generic
information for an invoice and then there is an item table that contains the
line items for the invoice. I have an auto number that can link many items to
a single header. Im trying to create a form to enter the Invoices and also
the line items and i want to have a total populate to update the total cost
of the invoice. This is where i am having trouble.

Its not a sub form i am using, rather it appears to be a sub table or a
linked table. when i click on the properties it says it is a child and in the
form designer under the detail the box says at the top "Table.Invoice_Items".

 

[Allen Browne]

Do not store the invoice total in the header table.

Doing so breaks a fundamental rules of data normalization: never store
dependent data.

 

[Al Campagna]

Pringle,
That's a fairly "broad" question for a specific reply.
Let me try to lay out the basic Main form/Subform setup.

A typical Invoice form does have a Main form...
(the ONE Invoice part of the relationship),
and a subform...
(The MANY Items part of the relationship).

That subform can be a "datasheet" form view, but... more often,
and in this case, it should be a "continuous" form... related to the
Main form by a common key field value.
(ex. tblInvoices/InvoiceNo to tblInvoiceItems/InvoiceNo)

Using a "continuous" subform allows you to calculate sums in the
subform Footer. In your case, the total amount due for items associated
to that invoice.
(ex. =Sum(LineTotal))

Get your Main, and continuous Subform set up, and working, and from
there,
we can do add totals
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Pringle]

Thank you for your reply. I changed the subform to a Continuous Form. I tried
to put a subtotal in both the footer of the sub form and the footer of the
main form but it dosnt calculate anything, it just leaves blank.

 

[Pringle]

I am not trying to store a value in a table, rather i want the form to
calculate and display the total so as you enter the items you can see the
total cost of the invoice.

 

[Allen Browne]

Place a text box in the Form Footer (not Page Footer) section of your
Continuous Form.

Set its Control Source to an expression such as:
=Sum([Quantity] * [UnitPrice])
substituting your field names for the names in square brackets.

Set the Format property of this text box to Currency (or something numeric.)

 

[Al Campagna]

Pringle,
Your response doesn't tell us anything...

You'll need to briefly describe the records in your subform.
Describe the subform control you want to Sum up in the sub footer.
Is it a calculated value? If so.. what is the calculation? What are
the control names involved in the calculation?
What Sum calculation did you place in the subform
footer?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Pringle]

I found out what the issue is. I was right clicking the text box and
selecting expression builder. I needed to go thru the properties using the
control source to enter the formula.

Thanks for your support on this issue.

Place a text box in the Form Footer (not Page Footer) section of your
Continuous Form.

Set its Control Source to an expression such as:
=Sum([Quantity] * [UnitPrice])
substituting your field names for the names in square brackets.

Set the Format property of this text box to Currency (or something numeric.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am not trying to store a value in a table, rather i want the form to
calculate and display the total so as you enter the items you can see the
total cost of the invoice.