Re: Running total in a form...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 16:57:58 +0800

The amount paid in *other* lines of the trasaction is:

=DSum("Amt_Paid", "TransTable",
    "([Trans_ID] = " & Nz(Me.[Trans_ID], 0) &
    ") AND ([Inv_Nr <> " & Nz(Me.[Inv_Nr], 0) & ")")

So the balance left is:
=[Inv_Amt] - [Amt-Paid] - DSum("Amt_Paid", "TransTable", "([Trans_ID] = " &
Nz(Me.[Trans_ID], 0) & ") AND ([Inv_Nr <> " & Nz(Me.[Inv_Nr], 0) & ")")

I would suggest that you do not store the Inv_Amt at all. You may want to
use an unbound text box so the user can enter it and the interface can help
them, but it should not be stored in the payment table. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Gettingthere" <Gettingthere@discussions.microsoft.com> wrote in message
news:9219874F-F5C5-4274-9AB7-6212CF1293FE@microsoft.com...
>I have a form Tbl_trans. In this form you enter the Inv_Nr ,  Inv_amt,
> payment_type, Amt_Paid and Paid_with.  Now I have created a form to enter 
> the
> data,.
> The problem I have is that there may be several invoices paid with 1
> transaction, eg: 1 credit card payment maybe be linked to 3 different
> invoices.  The data entry operator needs a box on the data entry form the
> tell them the balance left on that credit card payment.  Eg: Inv1 = $25 
> but
> the payment is $75,  Inv2 has a value of $25.00 and is paid with Inv1, 
> Inv3
> has a value of $25 and is paid with inv1, therefore the balance of the 
> credit
> card transaction  starts at  (75-25) $50, then as the second inv is 
> entered
> $25 and the third $0.
> I have created a query which when run will give me a balance grouped by 
> the
> Paid_with field, works somewhat, but I cannot get it to dynamically update 
> in
> the trans_form (query as a subform) when we enter the data.  I think is
> either an requery or after update problem
>
> Unsure, I have tried a few thing but nothing works, maybe am going about 
> it
> the wrong way.
>
> Any Help appreciated
>
> Thanks
>
> Roger

Relevant Pages

  • Running total in a form...
    ... The data entry operator needs a box on the data entry form the ... tell them the balance left on that credit card payment. ... Paid_with field, works somewhat, but I cannot get it to dynamically update in ...
    (microsoft.public.access.forms)
  • Re: First year of repayment mortgage, WTF!
    ... then the difference is how much capital you've paid off. ... It will also say what your balance was at the start of the year. ... It is the amount outstanding, which *is* partly capital and partly ... payment over the interest charged for that period (which in turn is ...
    (uk.finance)
  • Re: First year of repayment mortgage, WTF!
    ... then the difference is how much capital you've paid off. ... It will also say what your balance was at the start of the year. ... Generally the balance *is* all capital, if you've been paying all the ... It is the amount outstanding, which *is* partly capital and partly ...
    (uk.finance)
  • Re: Paying Credit Card in full or not
    ... 28th it would be for zero balance and nothing would be due. ... (Actually I'm not sure if the early payment in my scenario would work if nearly all the balance was paid before the statement date. ... All this discussion makes the case for preferring debit cards of course. ...
    (uk.finance)
  • Re: Paying Credit Card in full or not
    ... then paid 1000 quid back on 21st, when my statement was created around ... 28th it would be for zero balance and nothing would be due. ... nearly all the balance was paid before the statement date. ...
    (uk.finance)