Creating Multiple records from one form with 1 field different (due date)

[russ1985]

I have table which holds payments on a client account. If the client pays on a repeating contract I need a form to basically create a record for each future transaction. IE if the client pays monthly on a year contract it would create 12 records in the transactions table with the due date set to the first of each month.

The fields in the transactions table.

TransactionID(autonumber); Client id(number); Amount(currency); Datedue (short date) ; Paid(y/n box);

The fields in the repeattransactions form

Client id; Start Date; Amount; Frequency (weekly, monthly, quarterly, annually)


The only thoughts I have had is that I can get this form to open another form which pulls the data from my current form and calculates the date on the value in my current form then creates the record.

I don't know whether this will work or whether there is a better, quicker way to do this.

could anyone help me with this or forward some literature about repeating record creation i would be very grateful.

 

[jzwp22]

I would only create a record in the payment table when an actual payment is received. I would also use a different structure for that payment table. Something like this:

TransactionID(autonumber)
Client id(number)
AmtPaid(currency)
dtePaid (date)



In most circumstances you do not store a calculated value which is what your due date is. You can calculate the due date of any future payment given the start date of the contract, the current date, the frequency and the duration of the contract (i.e. the end date of the contract). You would use some of the date functions available in Access to do that. One which you will definitely need is the dateadd() function.

Just as a general note, it is best not to have spaces or special characters in your table and field names. They can cause issues, and in many cases you will have to enclose all of those fields in square brackets in queries, forms and reports etc. which is a lot of extra typing!