数据更改后自动重新编号 Access 表中的记录 [英] Automatically renumber records in an Access table after data changes have been made

问题描述

这是我的数据示例:

ID - autonumber ~ 是他们的主键
周 - 小猪出生的一周
母猪顺序 - 母猪数量

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

规则:
1. 如果仔猪在同一周出生，无论母猪.仔猪编号继续
2.如果小猪下周出生，编号返回0001然后规则1.

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.

添加仔猪的方式是，周+母猪+(仔猪数).例如，(10)，它会在周=01 &母猪=01.

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.

问题是，如果用户对要添加的仔猪数量有误怎么办?根据图片，01 01 有两只小猪，如果不是只有两只呢?说，它的 3.所以用户需要删除它，然后编号必须调整为..

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?

推荐答案

如果 Access 数据库版本至少是 Access 2010，则可以使用事件驱动的数据宏来强制实施编号方案.对于名为 [Piglets]

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]

带有 [AfterInsert] 的数据宏:

with data macros for [AfterInsert]:

[更新后]:

[删除后]:

和一个命名的数据宏 [RenumberPiglets]:

and a named data macro [RenumberPiglets]:

重新编号会自动进行，即使表格是从外部应用程序(例如，VB.NET 或 C#)更新的.

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

对于 2007 年及更早版本的 Access 数据库，必须将类似的逻辑写入用于更新表的数据输入表单.

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.

这篇关于数据更改后自动重新编号 Access 表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！