Allocate Payment

  • Thread starter Thread starter Simon Davies
  • Start date Start date
S

Simon Davies

Access 2K

I have a form which shows outstanding invoices by customer.
Occasionally I will recieve an amount of payment from a
customer to cover some of the invoices. is there a way
where I can enter a single value for payment and that the
payment is automatically assigned to the oldest
outstanding invoices, ie

inv1 120.23
inv2 76.36
inv3 55.55
tot 252.14

payment recieved 250.00
therefore 2.14 remains outstanding against inv3

Hope the above made sense , thanks in advance for help on
this one.

Regards

Simon
 
Yes, but it requires some code that is completely dependent upon your
table's setup.

Generically, here is what you do in the code:

1. store the total payment amount in a variable that tracks the amount
remaining to be distributed.
2. open a recordset based on the invoice table, selecting records for that
customer that are not yet paid (how you determine this depends upon your
table's structure).
3. start with the first record in the recordset.
4. check if the amount due for the invoice is less than, equal to, or
greater than the amount remaining to be distributed.
3.1. if the amount due is less than the amount remaining to be
distributed, mark that invoice as paid in full, and subtract from the amount
remaining to be distributed the amount due for this invoice.
3.2. if the amount due is equal to the amount remaining to be
distributed, mark that invoice as paid in full and end the process.
3.3 if the amount due is more than the amount remaining to be
distributed, decide how to record a partial payment, store that info into
the table's record, and end the process.
5. if step 4.1 was followed above, go to the next record in the recordset
and repeat step 4.
6. close the recordset.
 
Back
Top