MS-ACCESS - Before insert/after insert/update trigger required

Question

I have a PropertyOwnership table which has a PercentOwnership field. Please note that any property can have multiple owners, hence we require the percent ownership for each owner.

When adding a record into the PropertyOwnership table, I would like to insert a trigger that will ensure the correct percentage value is added i.e. when we add all the percent ownership of the property, it should not be greater than 100.

I have tried creating a event driven data macro for after insert/update trigger as followed:

Macro Name: InvalidOwnership

Parameter 1 name: PropertyNameTemp
Parameter 2 name: PercentOwnershipTemp

SetLocalVar Name: RunningPercent
Expression: 0

lookup each record in PropertyOwnership where PropertyName = PropertyNameTemp
   SetLocalVar Name: RunningPercent
   Expression      : RunningPercent + PercentOwnership

If PercentOwnershipTemp + RunningPercent > 100

RaiseError.

I use the above data macro within the After insert and update trigger (table event macro) but it doesnt seem to work.

Please assist :)

Update your tags with correct version of ms-access. And properly describe "it doesn't seem to work" — dbmitch, Jun 04 '18 at 03:06

Santosh · Answer 1 · 2019-01-21T17:45:12.497

Use For Each Record in block instead of LookUp Each Record as it will operate only first record.

When the RunningPercent is greater than 100 it will raise error which can be seen from Application Log, however the record will be inserted in table.

<?xml version="1.0" encoding="UTF-8"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
   <DataMacro Name="InvalidOwnership">
      <Parameters>
         <Parameter Name="PropertyNameTemp" />
         <Parameter Name="PercentOwnershipTemp" />
      </Parameters>
      <Statements>
         <Action Name="SetLocalVar">
            <Argument Name="Name">RunningPercent</Argument>
            <Argument Name="Value">0</Argument>
         </Action>
         <ForEachRecord>
            <Data>
               <Reference>PropertyOwnership</Reference>
               <WhereCondition>[PropertyName]=[PropertyNameTemp]</WhereCondition>
            </Data>
            <Statements>
               <Action Name="SetLocalVar">
                  <Argument Name="Name">RunningPercent</Argument>
                  <Argument Name="Value">[RunningPercent]+[PercentOwnership]</Argument>
               </Action>
            </Statements>
         </ForEachRecord>
         <ConditionalBlock>
            <If>
               <Condition>[RunningPercent]&gt;100</Condition>
               <Statements>
                  <Action Name="RaiseError">
                     <Argument Name="Number">1234</Argument>
                     <Argument Name="Description">Invalid OwnerShip</Argument>
                  </Action>
               </Statements>
            </If>
         </ConditionalBlock>
      </Statements>
   </DataMacro>
</DataMacros>

MS-ACCESS - Before insert/after insert/update trigger required

1 Answers1

For Each Record in