What is wrong with my Access After Update Macro?

Question

I am trying to make it so if the Balance field is updated then automatically the AvailableCredit is figured.

This is for homework, and I wouldn't ask except I have already tried everything I can think of to fix this, plus things that I found on the internet. I just want to understand what I am doing wrong here.

  If [Balance] <> [Old].[Balance] Then
    EditRecord
      SetField
        Name Available Credit
        Value= [Client].[CreditLimit]-[Client].[Balance]
  End If

Answer 1

Let me start by saying: a data macro is the wrong approach for this. Ideally, one shouldn't store data that can be calculated easily at all. If you need to have it available in table view, then use a calculated field.

There's nothing really wrong with your data macro, except that you need a Before Change macro, not an After Update macro. You shouldn't update fields in an after update macro, since an update triggers the after update macro again, possibly leading to an infinite loop. Instead, use a Before Change macro, to include the change with the update.

There, just use Set Field action to change the field.