Running total in a form...

[Gettingthere]

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

 

[Allen Browne]

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.

 

[Gettingthere]

Thankyou for that.

But where do I put it (be Nice)
These would be in two unbound text boxes?
Could I use "balance left" as a default value (or beforeupdate) for the
amount_paid field?

Thankyou

Regards


Roger

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.

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

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

 

[Allen Browne]

Yes, you can put the expression into the Control Source of a text box.

If you want to program it, you can use the BeforeInsert event procedure of
the subform to DSum() the values and default the unallocated amount. We are
not going to write it for you, but it's not difficult.

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

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

Thankyou for that.

But where do I put it (be Nice)
These would be in two unbound text boxes?
Could I use "balance left" as a default value (or beforeupdate) for the
amount_paid field?

Thankyou

Regards


Roger

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.

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

 

[Gettingthere]

Thankyou for your help

rgds

Roger

Yes, you can put the expression into the Control Source of a text box.

If you want to program it, you can use the BeforeInsert event procedure of
the subform to DSum() the values and default the unallocated amount. We are
not going to write it for you, but it's not difficult.

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

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

Thankyou for that.

But where do I put it (be Nice)
These would be in two unbound text boxes?
Could I use "balance left" as a default value (or beforeupdate) for the
amount_paid field?

Thankyou

Regards


Roger

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.


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