How to generated Payment Due Dates and Amounts on Report

[kvest]

Hello,
I am nearing the end of a project and am having difficulty convincing the users that they do not need a report (paperbased) that they currently use. They are requesting I recreate it in Access, but have no idea where to begin. Here is the design...

They currently use a report that takes a total payment amount and divides it by the payment schedule (bi weekly or monthly) and calculates future payment due dates. The length of the payments and amounts vary from account to account and any with a length of less than 30 days are paid in full when they start.

I can easily calculate the next payment due date by using the start or last payment date plus the payment schedule, but how would I gets this to run out into the future until the account is paid in full? Plus have the amounts subtract down until the total is paid off?

Once the accounting portion of the database is complete, this paper version of this form is obsolete except for the need to generate it and give it to the payee so they know all of their due dates and amounts (for whatever the value of that is).

The current report is in Excel and has some VBA code buttons on it that total this information and run it forward (I didnt create that). Here are my questions...

1. What would I need to do to create this type of report for printing from Access?
2. Could I just link the db to this excel file someway and let them keep using it?
3. Should I just tell them to forget it or ignore this request and let them keep using the excel file if it is that important to them.

I really dont want to put alot of time into setting this up if all they are doing is printing it and handing it out. I dont see alot of value in that....

Thanks for you opinions and thoughts in advance...

 

[mikey69]

The quick fix is to link to the Excel file and print it. The more robust method is to use Visual Basic coding to do the necessary calculation to create a temporary table based on whatever data has been input by the users and then run a report based on that temporary table.

if you are familiar with VB coding, you should achieve this pretty quickly, in an environment that would be easy to maintain.

Access makes all things possible. It even makes them intelligible

 

[kvest]

Mikey69,
I got the link working and that is a good fall back option, however, I would still like to move this entire form into Access. My VBA expert has dropped the ball on this part of the project and hasnt even started on it. I have the report all laid out and ready for data, but I am very new to VBA. Can someone give me a direction to go?

The fields that I think will be needed are:

StartDate
PaymentSchedule (Bi-Weekly (14 days) or Monthly (28 days))
DailyRate - Amount charged per Day
NumberOfDays (15-365) 14 days or less are Paid in full.
AccountNumber - Primary Key (Main table and PaymentTable)

Can someone give me some ideas?

Thanks

 

[mikey69]

I could give you some help, but not for a couple of weeks, because I'm far from home and don't have easy access to the internet. Pse post again if you can wait till early January.

Access makes all things possible. It even makes them intelligible

 

[kvest]

That would be great. I will take whatever help I can get.

 

[mikey69]

Will be back home around 6 Jan, will try to respond shortly thereafter.

Access makes all things possible. It even makes them intelligible

 

[mikey69]

Hi kvest - Now that I've stopped wandering, I've had a chance to read your previous posts a bit more carefully. I think the most important thing you said is that you don't want to invest a lot of time. With that as a given, the Excel link route is still the route I'd suggest. To get the application going reliably in Access would require significant VBA coding, because I think you'd be pretty quickly asked to provide facilities for recording payments received, for setting up new loans and so on. The bit about paying off small outstanding amounts is another red flag to me. One thing that we all have to bear in mind is just how much hair we have to tear out to get what our users call "simple" systems going.

Another issue would be hoow many of these accounts are active at any time. An Excel system may be just right in terms of return on time invested.

One final thought. If you do decide to go the Access route, you really should find a VBA guy nearby, or hone up your own skills. Don't know where you are - I live in a small town in South Africa and I've tried the remote support game. It works but it can end in disaster.

Sorry to pour cold water, but I try to be realistic. Post again if you need more discussion or email me at youmikeATmweb.co.za.

Access makes all things possible. It even makes them intelligible