Subtotal on a form (1 Viewer)

[mtagliaferri]

I have a form were I got the corrent fields:
date, account, ammount; the account field is a numeric field. What I am trying to do is to have a extra field on the same form wich will give me the total ammount for the relative account as soon as I have inserted the 3 data. What I don't knowis if I need a query to extract all the ammount for the relative account shown on the field "Account" or it can be done in a easyer way!!!

 

[Junkee Brewster]

Hi mtagliaferri

Am I to infer that your form is a tabular form, and at any given time, your form has records from multiple account numbers?

If the form was only displaying records from ONE account number at a time, I would say to simply put a textbox with the controlsource: =Sum([Amount]) (where Amount is the field you wish to calculate).

Can you please specify if your form is for a) one account or b) multiple accounts/account numbers that you need to differentiate between with your total(s)? (And then I might say you could think about having a master/subform relationship that will allow you to view your orders/accounts BY each customer/account number).

Thanks - Junkee

 

[mtagliaferri]

Hi Junkee,
my form is a multiple account (example the record value for account could be: 11001, 11002, 11003 and so on), and I need to have the total only for the account wich I am updating the record, to make a example: If I opent the form in ADD mode I will have a blank form where I will input account 11001 and ammount 100, once I have entered 100 I would like to have a field wich will give me the total for the account 11001 taking into consideration previous records containing account 11001.
Hope this will give you further information about what I am aiming to do!
Thanks, Marco

 

[Junkee Brewster]

Hi Marco,

I wanted to make something for no knowledge of VB or macros (except using MS Command Button Wizard to Open forms)

What I think you might have:

A form showing multiple account numbers. When you click the "Add New" button, a plain old data entry form opens (no links or anything).

The Total is calculated using a DLookUp of a TOTALS query I made called qryViewTotals (Field Showing Total per Account is SumOfAccount). Add to text box (something similar):

=DLookUp("SumOfAmount","qryViewTotal","Account = """ & [Account] & """")

So, once you type the Account Number, it will look up the total for that Account Number.

This will do NOTHING with a new Account Number = you won't see any current total, but it shouldn't really matter? Nor will the total UPDATE to include the Amount you've just entered.. again, there isn't much point.. But there's more that can be done to overcome that. Let me know if you need tweaks or you're stuck.

HTH - Junkee

P.S. If your form were structured differently, there are other things to be done without VB as well.

 

[mtagliaferri]

Hi Junkee, this is what I was looking for thank you very much, I just wonder if the new total can be show as soon I have entered the ammount on the new record, so that I can have a updated situation !! Hope you understand what I mean!!
Thank you again