Automatically adding new records based on date

[maff811]

Hi,

I am creating a database to keep track of rental payments. Somehow, I want to be able to automatically create a new record that will charge the tenant the weekly rent which is based on a start date. For example:

Start Date = 01/01/2017
Rent = $200 per week

On 01/01/2017, I would like to create a record that says:

Start Finish Rent
01/01/2017 07/01/2017 $200
08/01/2017 14/01/2017 $200
and so on

I have a separate table and form to input payments, but this one has me stumped.

Any ideas would be great.

Thanks in advance.

 

[plog]

Nothing in computers is "automatic". Some action/event must trigger it.

What action would trigger this records creation? Can you explain the process you envision a little more in detail? How does the computer know about the Start Date and Rent values?

 

[maff811]

I have a table with the tenant's details including start date, so it can be triggered from this date, at 7 day intervals. Another field stores the weekly rent, so I can pull that in when the new record it created.

 

[plog]

Nope, need an action/event. As you have explained it a simple UPDATE/APPEND query could work on your existing data to give you the records/data you need.

 

[maff811]

cheers, will give that a try

 

[maff811]

I have played around with basic append queries in the QBE window, but what sort of code would need to be behind it to compare the start date to current date and append the new record to the table?

 

[plog]

Compare how?

Here's a list of Access functions. Check out the Date section: https://www.techonthenet.com/access/functions/

 

[maff811]

Hi Plog,

I got half of what I want working. I am just trying to get the DLookup function to return the desired result.

THanks for your help.