15 reoccurring events each month semiautomatically posting to a table -- how to?????

[TenOc]

I have 15 events that recur each month. Somewhat like 15 tenants paying rent. While all the events are not due on the same day of the month I usually wait until around the 20^th of each month to post these 15 events to my actual rent paid table. The tenants are very good about paying the rent on time so I do not need to make any adjustments. I do have a table that list the date and amount of each rent called rent due.



I am looking for some hints on how to semi-automatically post these 15 events without having to post each manually. Any hints or templates would be greatly appreciated.

[June7]

VBA procedure that runs INSERT SELECT action SQL to batch create records.

The real trick is figuring out what event to put code into. Could be button click, form open, or AutoExec macro.

If Day(Date()) = 20 Then
CurrentDb.Execute "INSERT INTO RentPaid(TenantID, Rent, DatePaid) SELECT TenantID, Rent, Date() AS DatePaid FROM RentDue"
End If

Set TenantID and DatePaid fields as compound index to prevent duplicate entries.

[Gicu]

You could add code to a autoexec macro or to the Load event of the opening form and check for the day of the month:

If Day(Date) =20 then
'prompt if to add the 15 rent records
    If msgbox ("Do you want to add this month's rent records?",............) = vbYes then
        CurrentDb.Execute "INSERT INTO RentPaid(TenantID, Rent, DueDate) SELECT TenantID, Rent, #" & Me.tbxDate & "# AS DatePaid FROM RentDue"
    End If
End if

[TenOc]

Thanks All