根据添加记录的年份创建顺序ID值 [英] Create sequential ID value based on the year that a record is added
问题描述
我正在使用Access 2013数据库,该数据库将具有输入到数据库中并与其他属性链接的不同实用程序.每个极点将具有唯一的全局ID,为简化工作,我想添加另一个更简单的唯一ID.我希望在将新的极点导入数据库时自动填充此字段.该ID将如下所示:
I am working on an Access 2013 database that will have different utility poles entered into the database and linked with other attributes. Each pole will have a unique global ID, and to simplify working I would like to add another unique ID that is more simple. I would like this field auto populated when a new pole in imported into the database. The ID would go as follows:
SAC(年)-(递增的数字,不能重复)
SAC(year)-(Escalating number, cannot be a duplicate)
例如SAC16-20(这是2016年进入数据库的第20个极点)
ex. SAC16-20 (This would be the 20th pole entered into the database in 2016)
例如SAC15-2536(将在2015年进入第2536杆)
ex. SAC15-2536 (Would be the 2536th pole entered in 2015)
如果有人可以帮助我生成一些代码来使此自动填充ID字段起作用,我将不胜感激.
If anyone could help me generate some code to make this auto populate ID field work I would greatly appreciate it.
推荐答案
在Access版本2010和更高版本中,您可以使用事件驱动的
With Access versions 2010 and later you can use an event-driven data macro to generate the sequential ID. For example, say you have a table named [poledata]. Open it in Design View and add two fields:
alternate_id_seq – 数字(长整数)
alter_id – 文字(20)
alternate_id_seq – Numeric (Long Integer)
alternate_id – Text(20)
将更改保存到表中,然后切换到数据表视图".
Save the changes to your table and then switch to Datasheet View.
在Access功能区中,切换到表格工具>表格"标签,然后单击更改前"
In the Access ribbon, switch to the "Table Tools > Table" tab and click "Before Change"
然后输入以下宏...
then enter the following macro ...
...或将以下XML粘贴到宏编辑器窗口中
... or paste the following XML into the macro editor window
<?xml version="1.0" encoding="utf-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="BeforeChange">
<Statements>
<ConditionalBlock>
<If>
<Condition>[IsInsert]</Condition>
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">next_seq</Argument>
<Argument Name="Value">1</Argument>
</Action>
<Action Name="SetLocalVar">
<Argument Name="Name">prefix</Argument>
<Argument Name="Value">"SAC" & Year(Date()) Mod 100 & "-"</Argument>
</Action>
<LookUpRecord>
<Data Alias="pd">
<Query>
<References>
<Reference Source="poledata" Alias="pd" />
</References>
<Results>
<Property Source="pd" Name="alternate_id_seq" />
</Results>
<Ordering>
<Order Direction="Descending" Source="pd" Name="alternate_id_seq" />
</Ordering>
</Query>
<WhereCondition>[pd].[alternate_id] Like [prefix] & "*"</WhereCondition>
</Data>
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">next_seq</Argument>
<Argument Name="Value">[pd].[alternate_id_seq]+1</Argument>
</Action>
</Statements>
</LookUpRecord>
<Action Name="SetField">
<Argument Name="Field">alternate_id_seq</Argument>
<Argument Name="Value">[next_seq]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">alternate_id</Argument>
<Argument Name="Value">[prefix] & [next_seq]</Argument>
</Action>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</DataMacro>
</DataMacros>
现在,当向表中添加新行时,将自动填充[alternate_id_seq]和[alternate_id]列.
Now when new rows are added to the table the [alternate_id_seq] and [alternate_id] columns will be populated automatically.
这篇关于根据添加记录的年份创建顺序ID值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!