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

问题描述

这是我的数据示例：

ID - autonumber 〜是他们的主键

每周 - 每周仔猪出生

母猪订单 - 母猪号

规则：

1.如果仔猪出生在同一周，不管他们的母猪。小猪编号继续

2.如果仔猪下周出生，编号将返回0001，然后为规则1.

仔猪添加方式， week + sow +（仔猪数）。例如，（10），它将在周= 01& 10时生成十只小猪0001至0010。 sow = 01。

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

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

做这种东西的正确方法是什么？

解决方案

如果Access数据库版本至少为Access 2010，那么事件驱动的数据宏可用于强制执行编号方案。对于名为[Piglets]的表格

< stack.imgur.com/UOJfY.pngalt =AfterInsert.png>

[AfterUpdate]：

[AfterDelete]：

和命名的数据宏[RenumberPiglets]：

重新编号自动发生，即使表是从外部应用程序更新的（例如，VB.NET或C＃）。

对于Access数据库版本2007和更早版本，类似的逻辑必须写入用于更新表的数据输入表单。 p>

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?

解决方案

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.

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