Update field in all records after an earlier field is changed

[WalkieTalkie]

I am building a database in Access 2003 for my not-for-profit childcare facility (why do I volunteer for these things??). Its purpose is to keep track of kids, parents, and sessions attended. So far, so good. We also want it to keep track of fees charged and payments made, so we can send out an invoice to parents each month. Sounds simple enough, but I'm having the following trouble:

The fees charged are dependent upon whether the account is in debit or credit. For example, if Account #1 is >= $0.00 on 1 May, then the fee charged that day is $10.00. But if the account balance is < $0.00, then the fee charged is $11.00
The code that makes this happen, is on the After_Update event of another field called AttendStatus, and is set out below:

If Me.txtBalance >= 0 - Me.Fee Then ‘if in credit balance
                Me.Fee = “10”
Else 'if in debit
                Me.Fee = “11”
End If

This all works fine until we come to enter the payments made. Most payments are made by internet banking, and therefore aren’t entered into the database until some time after the transaction actually happened. So, the account will be charged as if it is in debit, when in reality it was in credit. For example:

Balance on 1 May is -$5.00.
Payment is made by internet on May 2 of $50, but is not entered into database yet.
Child attends on May 3, and is charged $11 because the database thinks the account is still in debit.
Payment entered into database by administrator on May 4, bringing the account into (belated) credit.
Irate parent rings up on May 5 when the invoice goes out, asking why her account was charged the penalty fee on May 3 for being in debit, when she had paid up and was in fact in credit.

What we need is some way to simply update all the Fee fields that follow the payment made.

So far, the only way I have been able to do this is by putting the same code on the On_Current event of the form. This way, after the payment is entered, the user scrolls the cursor through each record, the Fee field in each record is updated. But this is very clumsy and relies too much on the user remembering to do the scrolling – there’s just too much room for error. There must be a better way!

Can anyone help with a suggestion for a better way to deal with this issue? Maybe I’m making it too complicated and there might be a simpler way that I am missing completely?

(By the way, the data is recorded on frmTransaction, which has tblTransaction as its record source. tblTransaction has TransactionID as its primary key, related one to many with the primary table which is tblKidAccount.)

I hope I have provided enough information. I have spent many hours on this so far, with no success, so any and all help will be gratefully received!

 

[ProgramError]

Hi walkie Talkie

1) Using the banking system as a example, it usually take 3 working days for a payment to be added to an account - why not with-hold the $11 charge until 3 days after the child attended, or when a payment has been made before presenting the charge. That way it will give enough time for payments to be entered which may have been made over the internet.

2) There is also a date issue here. Is the date recorded at the time the payment is made over the internet? If so, could you use that date to reposition the payment so it appears in the appropriate position in the accounts data before the invoice is sent out, this again relates to this 3 day wait period.




Program Error
Programmers do it one finger at a time!

 

[LittleSmudge]

Why don't you do what all Banks do.

Ie. Add in an allowance for "Clearing" in your rules.

Tell clients that payments made only affect billing values AFTER the payment has been cleared into the system.

If they pay in by DDR on 2nd May then they can't expect it to be credited when they turn up at the door on 3rd.

DDRs must be paid 5 working days in advance in order to be sure that the funds are clears.
( Alternativly - Bring Cash ! ).


not a technical solution this time - but a realistic one.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ClydeData]

Why not have a check box if they have opted or online payment

If Me.OnlinePayment=True then
Me.Fee = “10”
Else
If Me.txtBalance >= 0 - Me.Fee Then ‘if in credit balance
Me.Fee = “10”
Else 'if in debit
Me.Fee = “11”

End If
End If

Hope this helps

Jimmy

 

[WalkieTalkie]

Thanks all of you for these replies. Its encouraging to me, at any rate, because I expected a whole lot of technical solutions that would show up how little I know!

We've gone through these various scenarios already, and the problem is that this service is run by a volunteer committee of 4, all of whom work full-time elsewhere (that's why we need the childcare!), so we can't really make an assurance that we will enter the payments on any particular date. We have been doing it weekly, but if the committee membership changes, or if someone gets sick, or if no-one simply has the time to do it, then it won't get done until 'later'. For this reason we have decided to try and design a programme that will automate as much as we can.

Another solution that we've thought of is to change the system, so that on invoice date if the account is in debit, we charge 10% extra. That's what phone companies etc seem to do, and now maybe I see why. Maybe if there really isn't a technical solution out there, this is what we should do. Any other ideas gratefully receievd, though!