0

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
Guillermo
  • 47
  • 1
  • 9
  • I'm a bit confused. It looks like you want new [tblContacts] records to be assigned a sequential [JobNo] based on the year that they were created (reset to 1 for each new year) so how does [tblTrackJob].[ID] factor into this? Please edit your question to clarify. – Gord Thompson Jan 16 '14 at 16:41
  • @GordThompson The 'tblTrackJob' table contains the counter for the next 'JobID' that I will call to populate the 'JobID' field in the 'tblContracts' table. – Guillermo Jan 16 '14 at 18:37
  • If [tblTrackJob] will just hold a single "NextID" counter that will get reset to 1 for each new year then you don't really need to do it that way. You can just base the next [JobNo] value on MAX()+1 of the existing [JobNo] values in [tblContacts] for that year. – Gord Thompson Jan 16 '14 at 18:49

1 Answers1

3

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Just curious: why doing it with a macro instead of VBA ? Anyway it's interesting, since it made me discover that those "new" macros seem to handle variables, and are quite readable finally. – iDevlop Apr 29 '14 at 12:14
  • 1
    @iDevlop The main advantage is that the data macro will run regardless of how the table gets updated, so even if a user opens the table in Datasheet View and adds a record the macro will still be executed. – Gord Thompson Apr 29 '14 at 12:35
  • ok...matching SQL Server's triggers. Got it. Thanks ! I will definitely have to digg this someday. – iDevlop Apr 29 '14 at 12:42