根据添加记录的年份创建顺序 ID 值 [英] Create sequential ID value based on the year that a record is added

问题描述

我正在开发一个 Access 2013 数据库，该数据库会将不同的电线杆输入数据库并与其他属性链接.每个杆都有一个唯一的全局 ID，为了简化工作，我想添加另一个更简单的唯一 ID.我希望在将新极点导入数据库时​​自动填充此字段.ID 将如下所示:

SAC(year)-(递增编号，不能重复)

例如.SAC16-20(这将是2016年进入数据库的第20个极点)

例如.SAC15-2536(将是2015年进入的第2536个杆位)

如果有人能帮我生成一些代码来使这个自动填充 ID 字段工作，我将不胜感激.

解决方案

在 Access 2010 及更高版本中，您可以使用事件驱动的

然后输入以下宏...

... 或将以下 XML 粘贴到宏编辑器窗口中

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="BeforeChange"><声明><条件块><如果><条件>[IsInsert]</条件><声明><动作名称="SetLocalVar"><Argument Name="Name">next_seq</Argument><Argument Name="Value">1</Argument></动作><动作名称="SetLocalVar"><Argument Name="Name">前缀</Argument><Argument Name="Value">"SAC&quot;&amp;年(日期())Mod 100 &amp;&quot;-&quot;</Argument></动作><查找记录><数据别名="pd"><查询><参考文献><Reference Source="poledata" Alias="pd"/></参考文献><结果><Property Source="pd" Name="alternate_id_seq"/></结果><订购><Order Direction="Descending" Source="pd" Name="alternate_id_seq"/></订购></查询><WhereCondition>[pd].[alternate_id] 喜欢[前缀] &amp;&quot;*&quot;</WhereCondition></数据><声明><动作名称="SetLocalVar"><Argument Name="Name">next_seq</Argument><Argument Name="Value">[pd].[alternate_id_seq]+1</Argument></动作></声明></LookUpRecord><动作名称="SetField"><Argument Name="Field">alternate_id_seq</Argument><Argument Name="Value">[next_seq]</Argument></动作><动作名称="SetField"><Argument Name="Field">alternate_id</Argument><Argument Name="Value">[前缀] &amp;[next_seq]</动作></声明></如果></条件块></声明></DataMacro></数据宏>

现在，当新行添加到表中时，[alternate_id_seq] 和 [alternate_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(year)-(Escalating number, cannot be a duplicate)

ex. SAC16-20 (This would be the 20th pole entered into the database in 2016)

ex. SAC15-2536 (Would be the 2536th pole entered in 2015)

If anyone could help me generate some code to make this auto populate ID field work I would greatly appreciate it.

解决方案

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  –  Numeric (Long Integer)
alternate_id  –  Text(20)

Save the changes to your table and then switch to Datasheet View.

In the Access ribbon, switch to the "Table Tools > Table" tab and click "Before Change"

then enter the following macro ...

... 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">&quot;SAC&quot; &amp; Year(Date()) Mod 100 &amp; &quot;-&quot;</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] &amp; &quot;*&quot;</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] &amp; [next_seq]</Argument>
                        </Action>
                    </Statements>
                </If>
            </ConditionalBlock>
        </Statements>
    </DataMacro>
</DataMacros>

Now when new rows are added to the table the [alternate_id_seq] and [alternate_id] columns will be populated automatically.

这篇关于根据添加记录的年份创建顺序 ID 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！