Sub Total from a form to a field in another form (1 Viewer)

I wanted to take the values from the frmInvoiveItems' [LineTotal] field, subtotal all of them for a given invoice ([InvoiceApplied]), and place them in a new field under frmInvoiceInfo that I haven't created yet.

(I.E. I have three different items under frmInvoiceItems for one invoice. They all have a different price. I want to add all of their prices together, and display them in a "Total" field in the frmInvoiceInfo table. This new "Total" field should be the total amount due minus the "Deposit" value and anything in tblTransactions [I still have to figure out how to assemble that too... another question for another time]).

Is all of this possible without me having to store the information in a query? How?

If you can calculate it why save it? Have a look here:

http://allenbrowne.com/casu-14.html

vbaInet said:

If you can calculate it why save it? Have a look here:

http://allenbrowne.com/casu-14.html

Click to expand...

This has been helpful so far, but...

From what I'm understanding, I'm to create a field in tblInvoices (via query) that calculates the totals I want?

My endgame with this field is to have it show up on the rptPrintableInvoices, however. So would I just make that field, have it show up in the form design for frmInvoiceInfo, and then make another report?

The point of that link is to stress the point that you're to perform all the calculations in a query and not save calculated values.

Are you struggling with the calculations? Perhaps you can explain more.

vbaInet said:

The point of that link is to stress the point that you're to perform all the calculations in a query and not save calculated values.

Are you struggling with the calculations? Perhaps you can explain more.

Click to expand...

Initially: I was intending to just place a new field for the information I wanted into frmInvoiceInfo. I wanted it this way specifically because I need it to show up in rptPrintableInvoice whenever I go to print it.

It would be convenient to be able to store (not simply view them in isolated occasions) the totals in a specific field, so that we can always return to see what a customer paid several months down the line (or if they were short, etc), and keep track of what the report we handed a given customer said on it.

Originally, I was going to go about doing this by creating a field in frmInvoiceData using design view's record source section. I was planning for this field to reference the column, '[LineTotal]' and append its field value wherever the '[InvoiceApplied]' value (from the InvoiceItems form) matched the Invoice_ID in the frmInvoiceInfo table. This was a bust. Any way that I attempted to do that would return an error, so I took to the forums for help. I couldn't get so far as to even bother with the deposits (the easy part), or finding a way to link it to tblTransactions for additional payments aside from the deposit.

Now: I just want to make a column for Totals in the Invoice Data form's table. This 'Total' will be the cost of all of the invoice items on an invoice, minus the deposit on the invoice, minus any payments under transactions. I still want this total to show up on printed invoice reports.

Where I'm stuck, is on how to add all of the InvoiceItems line totals together in order to even start doing this.

Just have this at the back of your mind... whatever you can calculate in a query, you can display in a form or a report.

Upload some same sample (fictitious) data showing what you currently have, and what you would like to achieve. A spreadsheet would suffice.

vbaInet said:

Just have this at the back of your mind... whatever you can calculate in a query, you can display in a form or a report.

Upload some same sample (fictitious) data showing what you currently have, and what you would like to achieve. A spreadsheet would suffice.

Click to expand...

Attached is a version of the invoice with enough fictitious data for the relevant functions in question.

After entering frmCustomerInfo, you'll notice frmInvoiceItems as a subform.

In it are LineTotal values that equate to $335.50 ($8.50 + $327).

In the table above it is a Deposit for $150.

The initial intent was to create a column next to deposit that would sub total out the $8.50 and $327 for $335.50- and then subtract the $150 in the deposit field and the PaymentAmount in tblTransactions (the $185.50 value in this instance).

But, I never made it past sub totaling the values in a different table than the source.

I'm guessing that all of the source tables need to be referenced in a query (tblInvoices for frmInvoiceInfo; tblInvoiceItems for frmInvoiceItems; and tblTransactions) and these fields need to be plugged into a formula to get the functionality I'm asking for. That's the big correction I'm seeing, and the way to fix my problem based on what I've learned from this thread.

Now I'm trying to figure out what to do from there. I don't think I've bound the Transaction_ID column to any forms yet. I'd assume that'd be necessary to proceed...

It's the details that I'm fixated on now ><

See attached... scroll to the bottom.

By the way, I found your form's background image distracting.

vbaInet said:

See attached... scroll to the bottom.

By the way, I found your form's background image distracting.

Click to expand...

Thanks a million- but I am trying to walk backwards through how you did it.

I'm noticing in the control source for the deposit sum that you just referenced [frmInvoiceInfo], but [txtDepositSum] wasn't a column there...

(=[frmInvoiceInfo].[Form]![txtDepositSum] )

I'm trying to get a better understanding of how you made the reference here

Edit: I see the new fields are actually part of the tables they reference, but am still trying to piece together the exact steps that led to their creation.

Look at the footer section of both subforms and it will make sense.

vbaInet said:

Look at the footer section of both subforms and it will make sense.

Click to expand...

Works perfectly ^^ thanks a million for the help.

I'll use what I learned to go ahead and work toward completing the rest of the file.

Good luck with the rest of it!