Invoice schedule for leases

[Sketchin]

Hi All,

I have a reservation form where I create equipment reservations for clients. It is set up as a form with a subform. The form is where information like company name and contact person is input. The subform is where I add items to be leased, and dates the equipment is out and returned and the cost of the lease (based on the 2 dates) as well as a checkbox stating if something has been returned.
What I would like to do is have another form (probably a tab) that shows a invoice breakdown where I would invoice for X amount every 30 days (for example). I would like to have the X amount auto calculated or manually entered. So for example:

Form on Tab 1:
Widget A Jan 1 2013 to Dec 31 2013 cost $12,000

Form on Tab 2:
Widget A Jan 1 2013 to Jan 31 2013 invoice amount $1000
Widget A Feb 1 2013 to Feb 28 2013 invoice amount $1000
ETC...

I guess my main question, is what is the best way to do something like this? Are reports more capable?

I just need someone to point me in the right direction!

Thanks,

 

[CJ_London]

It is cetainly doable - doesn't really matter whether it is a form or report - that will depend on your actual requirements (e.g. do you want Access to print an invoice or are you just reviewing the data)

As to how to do it, if you are creating an invoice at the end of each month, you should populate an invoice table 'set' (Invoice Header and Invoice Line Item) - otherwise if you change your lease record, then historically your invoice will change as well.#

The calculation I would use (and again, this does depend on your requirements) is total lease*contract to date divided by contract length minus what has already been invoiced - so in your example:

Widget A Jan 1 2013 to Dec 31 2013 cost $12,000

Form on Tab 2:
Widget A Jan 12000*1/12-0=1000
Widget A Feb 12000*2/12-1000=1000
Widget A Mar 12000*3/12-2000=1000
etc

 

[Sketchin]

Hi CJ,

Thanks for the feedback. My next question is how do I physically set up the form or report? Do I just place a bunch of unbound textboxes and have the data source set to the equations?

Im not sure why this is stumping me so badly!

 

[CJ_London]

Yes, have bound controls for those fields you have such as lease cost, contract length etc then an unbound box for the equation using the calculation as the control source.

In the context of a form (a report would be pretty much the same) you would have a main form and a subform. The main form would contain your lease record and the subform would contain your payment schedule.

In terms of what this looks like you have a number of options depending on your requirements.

You might for example:
1. generate an invoice listing all payments due for the next 12 months
2. or generate an invoice listing current payment due
3. or generate a statement showing total due to date less total paid to date