0

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.

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.

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.

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.

Question 1: can this be done?

And 2: If so, how?

Erik A
  • 31,639
  • 12
  • 42
  • 67
John Albrett
  • 9
  • 1
  • 2
  • Welcome to Stack Overflow! You seem to be asking for someone to write some code for you. Stack Overflow is a question and answer site, not a code-writing service. Please [see here](http://stackoverflow.com/help/how-to-ask) to learn how to write effective questions. – Derek Brown Mar 26 '18 at 02:27
  • Try the before update event – wazz Mar 26 '18 at 06:49
  • @DerekBrown He has attempted to write his own code. That small section is how an Access Data Macro looks. Imo this question is fine. It's narrow enough and reproducible. – Erik A Mar 26 '18 at 08:17
  • Possible duplicate of [Access, After Insert and the missing ID](https://stackoverflow.com/questions/9801744/access-after-insert-and-the-missing-id) – June7 Mar 26 '18 at 08:39
  • @June7 How is that a dupe? The objectives are very different, modifying the just inserted record, or creating a new record in a different table. So is the problem. Here, the problem is that the inserted record is read-only in an after insert macro. – Erik A Mar 26 '18 at 08:43
  • Yep, just reread that question the third time and finally see the difference. Removed close vote. I see you posted an answer I was trying to develop. I don't use macros so was proving challenging. – June7 Mar 26 '18 at 08:48

1 Answers1

1

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

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>
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you Erik for such a comprehensive answer. I didn't realize that newly created records were read only. Again, thank you. – John Albrett Mar 27 '18 at 03:16
  • If this answers your question fully, consider accepting the answer, indicating that this question has been answered. Read more: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Erik A Mar 27 '18 at 05:37
  • For local var, expression should be MyTable.PK. At least in Access 2013 – Basil Peace May 23 '19 at 20:29