.net - Automatically renumber records in an Access table after data changes have been made

Question

Here is a sample of my data:

ID - autonumber ~ is their primary key
Week - literally the week piglets were born
Sow Order - Sow number

Rules:
1. If piglets were born the same week, regardless their Sow. Piglet numbering continues
2. If piglets were born next week, numbering returns to 0001 then Rule 1.

The way piglets were to be added is, week + sow + (number of piglets). For example, (10), it will generate ten piglets 0001 to 0010 in week=01 & sow=01.

The problem is, what if the user had a mistake on the number of piglets to be added? Based on the picture, 01 01 has two piglets, what if its not just 2? Say, its 3. So user need to delete it and then numbering MUST be adjusted to..

01 | 01 | 0001  
01 | 01 | 0002  
01 | 01 | 0003  
01 | 02 | 0004  
01 | 02 | 0005 

What is the proper method to do this kind of stuff?

Answer 1

If the Access database version is at least Access 2010 then event-driven data macros can be used to enforce the numbering scheme. For a table named [Piglets]

with data macros for [AfterInsert]:

[AfterUpdate]:

[AfterDelete]:

and a named data macro [RenumberPiglets]:

the renumbering takes place automatically, even if the table is updated from an external application (e.g., VB.NET or C#).

For Access database versions 2007 and earlier, similar logic would have to be written into the data-entry form(s) used to update the table.