插入后访问2016表宏修改新创建的记录中的字段 [英] Access 2016 Table Macros After Insert modify fields in newly created record

问题描述

使用自定义表单，操作员将在新创建的记录上更新字段.创建新记录时，该新创建的记录中将存在一些字段，这些字段将基于另一个字段中的值进行更新.

Using a custom form, fields are updated on a newly created record by an operator. When a new record is created there are fields within that newly created record that are to be updated based on values in another field.

例如，一个字段CustID是根据主键"PK"更新的，如下所示:CustID="NW"&Format([PK],"000000").

For example a field, CustID is updated based on the Primary Key, "PK" as follows: CustID="NW"&Format([PK],"000000").

我可以使用更新查询来执行此更新，但是，我希望此更新在创建新记录后立即自动进行.

I can perform this update using an update query however, I would like this update to happen automatically immediately following the creation of the new record.

我知道我可以编写VBA代码，但是我不愿意.我应该能够在插入后使用事件驱动的表宏来执行此操作.由于某些原因，在任何人要求在信任中心中启用宏之前，我现在都无法正常工作.如果我可以使用它，我还有其他字段将根据操作员输入的值进行更新.

I know I could do this writing VBA code but I prefer not to. I should be able to do this using an event driven Table Macro, After Insert. For some reason I cannot get this to work, now before anyone asks Macros are enabled in the Trust Centre. If I can get this to work, I have other fields that will be updated based on values entered by the operator.

我将表Macro设置如下:

I have set the table Macro as follows:

After Insert
Edit Record
Set Field CustID="NW"&Format([PK],"000000")

我认为这应该起作用，但是什么也没发生.我大胆地假设编辑记录"将编辑新创建的记录，这是正确的. 我找不到任何有关如何成功使用插入后"来修改新创建的记录中的字段的信息和/或示例.

I feel this should work but nothing happens. I am making the bold assumption the Edit Record edits the newly created record, is this correct. I cannot find any information and/or examples as to how to successfully use After Insert to modify fields in the newly created record.

问题1:这可以做到吗?

Question 1: can this be done?

和2:如果可以，怎么办?

And 2: If so, how?

推荐答案

在Access Data宏中刚刚插入的记录是只读的(就像在SQL Server或其他带有触发器的解决方案中一样).您应该在表USysApplicationLog中看到一个错误指示.但是，您可以查找刚刚插入的行，然后对其进行修改.

The just inserted record in an Access Data Macro is read-only (just like in SQL Server or other solutions with triggers). You should see an error indicating that in the table USysApplicationLog. You can, however, lookup the row you've just inserted, and modify it.

您可以为此使用以下宏代码:

You can use the following macro code for that:

SetLocalVar
    Name            NewID
    Expression      =[PK]

For Each Record In  MyTable
Where Condition     =[PK]=[NewID]
    EditRecord
        SetField
            Name    CustID
            Value   ="NW"&Format([PK],"000000")
    End EditRecord

宏XML如下所示(您可以将XML粘贴到宏窗口中以创建宏):

The macro XML will look like this (you can paste the XML in the macro window to create the macro):

<?xml version="1.0" encoding="UTF-8"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
   <DataMacro Event="AfterInsert">
      <Statements>
         <Action Name="SetLocalVar">
            <Argument Name="Name">NewID</Argument>
            <Argument Name="Value">[PK]</Argument>
         </Action>
         <ForEachRecord>
            <Data>
               <Reference>MyTable</Reference>
               <WhereCondition>[PK]=[NewID]</WhereCondition>
            </Data>
            <Statements>
               <EditRecord>
                  <Data />
                  <Statements>
                     <Action Name="SetField">
                        <Argument Name="Field">CustID</Argument>
                        <Argument Name="Value">"NW" &amp; Format([PK],"000000")</Argument>
                     </Action>
                  </Statements>
               </EditRecord>
            </Statements>
         </ForEachRecord>
      </Statements>
   </DataMacro>
</DataMacros>

这篇关于插入后访问2016表宏修改新创建的记录中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！