Trying to run code for specific fields in a Table monthly.

[zakmsipanic]

Hi I need help. Im new to Access and:



I have table called FS_Disbursements and its corresponding splitform

In my table there are fields like startdate, duration, enddate, obligatedamount, amountpermonth, amountleft, totalamountdisbursed and other fields related to each record.

Ideally, what i want to do is every month, each record should do the logic:

            totalamountdisbursed = totalamountdisbursed + amountpermonth            amountleft = amountleft - amountpermonth
            duration = duration - 1

**Note that not all records have the same start and enddate and the format is dd/mm/yyyy

I have no idea how to make each record do the logic above every month. Please Help. Thanks!

[davegri]

I suspect you have a record for each account in the table.
For each account I would add a new record each month with the new calculated amounts in that new record. That way you would have a monthly history or trail back to the beginning. Add a field to each record 'UpdateDate'.

As for running once per month, each time the database is opened, compare the UpDateMonth with the current month. If the current month is greater, do the calculations, add the record with the new upDateDate. Going from December to January requires comparing the year also.

Does this sound like a viable option?