CODEWITHSUNDEEP
ms-accessvbams-access-2010
Guillermo
Guillermo

Reputation: 47

Reset counter ID to 1 for every new year in MS Access

I have two tables , tblContacts and tblTrackJob. Every time a new record is about to be added in tblContacts , id like to assign its JobNo field to the value of whatever ID is in tblTrackJob has stored. Id like the value to reset to 1 every year.

For example...

 first name, last name, JobNo, dateEntered
 bob         smith       01    1/1/2013
 john        doe         02    1/2/2013
 mary        Thomas      03    1/3/2013

 Joe         Henry       01    1/1/2014

Upvotes: 0

Views: 1750

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

Since we're dealing with Access 2010 we can create a Before Change data macro for the [tblContacts] table to assign the [JobNo] value:

BeforeChange.png

The macro will find the largest [JobNo] in the table from records with a corresponding Year([dateEntered]), and then add 1 to that value to give us the [JobNo] for the new record. If no existing records are found for the corresponding year then the new record gets a [JobNo] equal to 1.

Upvotes: 3

Related Questions