Autofill New Record

[MichaelNYC]

I have a database that tracks agency contracts and their
renewals. The table has two primary key fields PIN# and
Renewal#. The table also contains fields for the start
and end date and the start and end dates for each of the
renewal options.

I have a function that creates a new record and copies the
current contract information to this new record using the
DAO.RECORDSET.

Besides copying the current information I would like to
also increase the renewal# field by one, and "move up" the
option start and end dates.

For example:

PIN# 123 Renewal# 1 start 1/1/04 end 12/31/04
opt1start 1/1/05 opt1end 12/31/05
opt2start 1/1/06 opt2end 12/31/06

would become:

Pin# 123 Renewal#2 start 1/1/05 end 12/31/05
opt1start 1/1/06 opt1end 12/31/06
opt2start null opt2end null


any suggestions?

Thanks
Michael

 

[Paul]

Renewal Field - use the DMAX function to get the max (highest) value for the
given Pin and add 1 to it:

lngRenewal = DMax("Renewal","tblRenewals","Pin = " & rs.Pin)+1

where lngRenewal is a variable in which you are storing the new renewal
number, rs is the Dao recordset and Pin is the pin field.


Use similar techniqe to get the max (latest) start date and use the DateAdd
function to add a year (or whatever) to it.