2

I have been trying to set-up a Data Macro on one of the tables in Ms Access to add a 'path' and increment a level based on a parent record in the same table.

Before Change, if the parent node doesn't exist then default values are used. Otherwise, the parent's own path and level are looked up and the parent value of the current record is added on... Well that's the theory.

figure: BeforeChange Data Macro

The first part of the If does in fact work, however, I can't get the Else condition to pull or populate the values into the current record.

Am I missing something or should this all be done After Insert/Update?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Paul
  • 4,160
  • 3
  • 30
  • 56
  • 1
    Perhaps something like [this](http://i.stack.imgur.com/dV8Mg.png)? – Gord Thompson Mar 12 '15 at 16:39
  • I see - so you look up the values inside the lookup block and assign to variables, then carry out the actual `SetField` on the outside of the block. So am I right in thinking that operations on the inside of the `Lookup Record` block are primarily concerned with the fields from that lookup? (I'll just modify my trigger and see what happens)... – Paul Mar 13 '15 at 09:13
  • @GordThompson: Tried it and it works a treat. Please put this in as an answer and I'll credit it for you. Thank-you! – Paul Mar 13 '15 at 09:24

1 Answers1

4

The Look Up A Record In ... step creates a "block" of actions that will be taken if the lookup succeeds (and skipped if the lookup fails). It works like an If ... block in that respect.

So, we need to set the default values before the LookupRecord block, and use the resulting values (default or otherwise) in SetField actions after the LookupRecord block, like so:

BeforeChange.png

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • You've been a great help, Gord. The trigger works perfectly now and forms the paths as required (eg: .0.1.4.5.) and increments the level. – Paul Mar 13 '15 at 12:48