Countdown box on a form

[David57]

Hi!
I’m going crazy with my DB for a problem I’m trying to solve.
Just to get the idea of the problem, I have 2 tables:
TblA with fields [IDTblA] and [Customer]. The related form is named frmA
TblB with fields [IDTblB], [Payments], [RemainingDebt].
I have also created a form FrmB based on Customer (from TblA) with subform SubFrmB (the latter showing all the fields from TblB) .
So far so good.
Now my problem.
The first data entered in field [RemainingDebt] of TblB for each customer is his initial debt (for unpaid invoices). This field shows no further data.
The field [Payments], of the same table, also gets for each customer the amount of his initial debt and, with minus sign, the payments (made at various dates) for reducing their initial debt.
The filtered SubfrmB shows, for each selected customer:
- in field [RemainingDebt], first row, his initial debt as unique item;
- in field [Payments] his initial debt and, (with minus sign), the list of payments made at various dates.
In the SubfrmB footer I have created a Txt_Sum field with the formula “=Sum([Payments]”.
At any time, Txt_Sum shows for each customer his remaining debt.
Now I need that, every time I register a new payment of a selected customer, in the field [Payments] of the filtered SubFrmB, his initial debt, shown as unique item in field [RemainingDebt] in the same subform, should be changed into the value stored in Txt_Sum (which represents his updated debt).
In other words I need a “countdown” box either in the main form or in the its subform showing the current remaining debt of a selected customer. Any idea?

[PhilOfWalton]

A quick answer is that there are numerous ways to do a "Running Sum".
I suggest you look them up and see which one takes your fancy.

If you get stuck, please come back

Phil

[David57]

Hi!
Thank you Phil for answering.
You will be probably wondering why I need the unique item in field RemainingDebt to be a “running sum” bound to be always in the same row.
The fact is that in that same row of subformB there is also a [Duedate] field and a [Yes/No] field.
When issuing a new invoice, both fields get data. Field [Yes/No] is checked only when the relevant invoice is paid. Now, the [RemainingDebt] gets a [DueDate], too, corresponding to the oldest unpaid invoice.
I have created a query which returns a list of delayed payments drawn from the unchecked [Yes/No] boxes.
The list reports also the days of delay and the amount in delay. If [RemainingDebt] is not a running sum the query returns the original debt and this would not be correct.
I have tried an update query but, so far, unsuccessfully. Perhaps I need one more Txt_Box showing the original starting debt, but here I get stuck. My knowledge of Access vba (and English) is rather basic.

[jforbes]

I would move [RemainingDebt] into the [Customer] Table. For the InitialBalance, create a record with a Negative Payment in the [Payments] Table. I would create an Update Query that updates [Customer].[RemainingDebt] with the Sum of[Payments]. You can then run the Update Query after every insert or update to [Payments]. Then Bind Txt_Sum to [Customer].[RemainingDebt] and make it read-only.

Depending on your version of Access, you could make a Data Macro to run the Update Query as needed.

[David57]

Hi!
Thank you very much, JForbes, for answering.
I have moved [RemainingDebt] into [TblCustomers] and have been trying the following code:

[Private Sub Payments_AfterUpdate()
Dim mySQL As String
Dim PaymentsSum  As Single
Dim IdCst As Single
PaymentsSum = Forms!FrmB!SubfrmB.Form!Txt_Sum
IdCst = Forms!FrmB!IDTblCustomer
mySQL = "UPDATE TblCustomer"
mySQL = mySQL & " SET TblCustomer.RemainingDebt = " & PaymentsSum
mySQL = mySQL & " WHERE IDTblCustomer = " & IdCst
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
End Sub

It still doesn’t work. Something wrong with this code?

[jforbes]

I would change this line:

PaymentsSum = Forms!FrmB!SubfrmB.Form!Txt_Sum

to something like this. (This is just a guess at what the syntax would be; an example):

PaymentsSum = DSUM("[Payments]", "[Payments]", "IDTblCustomer=" & IdCst)

You are much better off using some code to specifically determine the Sum and then using it to update the Customer Record than allowing the Form to calculate it as the Form can become inaccurate depending on Filtering and timing. Also, there are a lot of ways to accomplish this and this is just one way.


Then, if you are still having trouble, it would be helpful to know what kind of error you are experiencing. As well as what steps you have attempted to debug your code. You've mentioned you are new to VBA, so you may want to look through this link for some pointers on how to Debug your code: Debugging in VBA

[David57]

[Payments] is a field of TblB and SubFrmB. Please let me know what you are referring to in the DSUM function.

[jforbes]

DSUM()
Again, you are better off calculating the Sum of Payments for a customer explicitly that to rely on the accuracy of a calculated sum on a SubForm.

[NeoPa]

David:
[Payments] is a field of TblB and SubFrmB. Please let me know what you are referring to in the DSUM function.

I suspect you're referring to a control on SubFrmB, rather than a field. Forms hold no data. They simply display, and allow editing of, data from elsewhere. They have fields indirectly, but fields are not controls.

In this case :

PaymentsSum = DSUM("[Payments]", "[Payments]", "IDTblCustomer=" & IdCst)

The second parameter tells us that the source of the data is a table or query called [Payments], which I hope answers your question.

[jforbes]

Thanks for addressing this NeoPa.

[David57]

Hello JForbes,
Basically, I need that the running sum should be located within TblB (Payments Table) as one of its fields.
I thought, at first, that a different location (main form FrmB for instance) would do, but this conficts with other requirements which would be too long to explain here.
Any idea would be hugely welcome.
David

[PhilOfWalton]

If it's any help, this will give you a running sum of payments

SELECT Payments.MemberID, Payments.PaymentDate, 
Format([PaymentDate],'yyyymmdd') AS PayDate, 
Payments.PaymentAmount, 
Format(ESum("PaymentAmount","Payments","MemberID = " & [MemberID] 
& " AND Format(PaymentDate, 'YYYYMMDD') <= " 
& Format([PaymentDate],'yyyymmdd')),"Currency") AS RunningSum
FROM Payments
ORDER BY Payments.MemberID, Format([PaymentDate],'yyyymmdd');
 

This works for me.
I suspect my MemberID is your CustomerID.

Just to explain. The payment date has to be converted into year, month, day format and sorted that way for the comparison to work

I think you may be having some problems, because, if I were writing the program, I would offset the payments against the invoice, rather than the customer.
You can still have a current balance for that customer which would be:-
His Opening Balance (Held on the Customer Table)
+ Running Sum of Invoices
- Running Sum of Payments.

Phil

[jforbes]

David57,

It's very important to know what the requirements are for [RemainingDebt]. If [RemainingDebt] is to be a populated field in the [Payments] table, so that there is a stored value for each payment, then your, my, and PhilOfWalton's approaches are wrong.

So which is your requirement:

• Do you need to store the [RemainingDebt] on each [Payment] Record, and display it that way?
• Can this value that can be calculated (or pre-calculated) and displayed along with the Customer?
• Can you calculate the total to be displayed on each [Payment] record when it is shown on the Screen? And not Stored?

[PhilOfWalton]

Much of the problem is that David is being rather coy about what he is doing with invoices, hence my remark in the last post.
It depends on whether he wants to treat Invoices as a negative transaction and payments as a positive transaction both in the same table, or whether he has separate tables for invoices and payments, and if so, is there a relationship set up between the invoice and the payment?

There has been a lot of concentration on payments, but scant attention paid to invoices. It obviously depends on the nature of his business, but when I was involved in the clothing business, it was pretty well 1 payment for 1 invoice.

The running total method I suggested certainly would work if there was a single transaction table in that the running balance would show on each line. I guess that if there were 2 tables, it would be necessary to create a Union Query of payments and invoices, sorted by date and then apply the running sum.

Phil

[David57]

Thanks everybody for helping.
Since I need to make [RemainingDebt] in TblB a running sum, I had the idea of adding to this table one more field named [ED] (short for ExistingDebt), its type being Yes/No.
[ED] is checked “Yes” just for those customers (not so many after all) having a previous debt (Initial Debt) for unpaid invoices.
As explained in my previous posts, my aim is to get in TblB an updated [RemainingDebt] after each payment made by filtered customers who do have debts.
When dealing for the first time with a customer having an Initial Debt, I enter its amount both in fields [Payments] and [RemainingDebt] of TblB. Payments made by customers, at various dates, are entered,
with negative sign, in field [payments] only. The algebric sum of field [Payments] reports, at any time, the amount still due.

I’ve been trying this code:

Private Sub Payments_AfterUpdate()
Dim mySQL As String
Dim PaymentsSum  As Single
Dim IdCst As Single
IdCst = Forms!FrmB!IDTblA
PaymentsSum = DSum("Payments", "TblB", "CustomerName=" & IdCst)
mySQL = "UPDATE TblB"
mySQL = mySQL & " SET TblB.RemainingDebt = " & PaymentsSum
mySQL = mySQL & " WHERE [CustomerName] = " & IdCst
mySQL = mySQL & "AND  [ED]=Yes"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
Me.Requery
DoCmd.GoToRecord , , acNewRec
End Sub

It seems to work fine with only one exception: the RemainingDebt is updated with the algebraic sum of [Payments] existing immediately previous to the recording of a new payment made by a customer while I need it to be updated with the current sum of [payments] after every new payment.
I do rely in your help.

David

[David57]

Thank you very much Phil.
I've been trying your code and it really worked all right.
You have been most helpful.

David

[NeoPa]

Hi David.

We're very happy that you found a solution here. Unfortunately, I had to reset your post as the Best Answer. I don't believe you were the source of those ideas.

[David57]

Hi NeoPa.
Sorry I forgot to check it as "Best Answer". Thank you for resetting my post.
David