Continuous forms record count

[Compressor]

I've only recently gotten to know about continuous forms and their use a bit. So for example, I used a maximum number of pre-defined (6) visits. I need to change that still, but during the development of the sales forms I already ran into a problem.

At first I was planning to have a certain maximum number of items to have on a form and create an invoice from that. But of course that would limit the number of items on one invoice which isn't good. So, now I have a continous form which adds a line containing productname, price, nr of that item etc. anytime needed.

In the previous design idea it would have been easy to calculate the total amount of money at the end of the list since the list was predefined. So I could just say invoiceamount = itemID1amount + itemID2amount + itemID3amout etc.
With the "new" design however that is impossible to do since the number of objects is unknown. So how do I create a piece of code which does that? In the continous form I have a SaleID which keeps track of which items belong to a certain Sale and a TotalItemsID autonumber which keeps track of the number of lines (items) which belong to that specific Sale.

So I need to count the total number of TotalItemsID's within a certain SaleID and then have a piece of code which creates a sum of that counted list of items.

I'm new at this, I've found the function Dcount() should be used to accomplish this, but the rest, as of yet, remains a mistery to me.

How to do this?

 

[Pat Hartman]

The simplest solution is to add a footer to the subform. In the footer, add a control with a ControlSource of:

=Sum(YourAmtField)

 

[Compressor]

Simply put that control on the footer of the parentform of the continuous subform or on the footer of the continuous subform itself?

If that really does the trick..... wow... That would be almost to good to be true.

 

[Compressor]

Ok, I have re-done most of the work on my db by now and have come to this part again. I have created the subform again, with a textbox control on the footer of the form. Since I need to store these values I tried to place this code on the on current event on the form so that it is updated everytime I add an item to the order or invoice so to speak.

Private Sub Form_Current()
    SumTotalForFooterInBtw.Value = Sum(PriceForTotalNrOfThisItemInBTW)
    SumTotalForFooterExBtw.Value = Sum(PriceForTotalNrOfThisItemExBTW)
    SumTotalForFooterBtwAmount.Value = Sum(PriceForTotalNrOfThisItemBTWAmount)
End Sub

But while the Sum statement is available in the event builder under the SQL agregate functions, it isn't recognized by the VBA IDE. So, how do I make it count the totals correctly for the same situation as outlined above, but now using the VBA IDE?

 

[Compressor]

This formula in the controlsource doesn't store the values in the table right? It calculates them when the form is opened? Or am I wrong and does it store them?

For now I've worked around it by making three additional fields and have the =Sum(xxxxx) value transferred there, so they do get stored stored for sure. But it is an additional three fields in the table again. And that's exactly what I've been "reparing/changing" the last couple of weeks.

I know that storing values that can be calculated is outside normalization boundaries most of the time, but I think in this case it would be wise to have them stored (for tax inpectors, tax calculations and bookkeeping purposes), and of course, being able to pull up a digital invoice without having to go through the whole nine yards again.

 

[Rich]

Base your form/calculations etc on a query then you don't have to go the whole nine yards again