1

Hi I would like to automatically update the value of an attribute in a field in Access 2013 if another attribute in that field is edited.

In the database backend I have selected the table I want to work on, and clicked on the AfterUpdate tab in the ribbon I enter these steps and save it, but nothing seems to happen when I update the DATE_OF_DEATH field and there is no error message generated, so I can't even debug the thing, I don't know whether the Macro is even been triggered or not, or if the IF statement is failing or if the SetField statement is failing, I'm totally in the dark. I would really appreciate some advice on fixing this. Thanks.

The code is below.

If Updated([DATE_OF_DEATH])
    EditRecord
        SetField
            Name OUTCOME
            VALUE = "test"
    End EditRecord
End If
Ashley Stewart
  • 161
  • 1
  • 3
  • 12
  • This question is a dupe of [this one](https://stackoverflow.com/q/49482858/7296893), but since my answer there doesn't have upvotes nor is accepted I can't mark it as such, and thus will answer it. – Erik A Apr 16 '18 at 08:56

1 Answers1

2

The just inserted or updated 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      =[ID]
If Updated([DATE_OF_DEATH])
    For Each Record In  MyTable
    Where Condition     =[ID]=[NewID]
        EditRecord
            SetField
                Name    OUTCOME
                Value   ="test"
        End EditRecord
End If

This will require your records to have a unique identifier. Without a unique identifier, I think you can't achieve this. You can try using a Before Change macro to edit the currently updated record and use IsInsert to only affect updates, but unfortunately these don't have access to the Updated function.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you very much. Works perfectly now. :) – Ashley Stewart Apr 16 '18 at 10:12
  • Actually I've noticed that now any forms that are related to that table won't update the date_of_death field. Is there any reason why this might be? – Ashley Stewart Apr 16 '18 at 11:28
  • Eh... I'm a bit unclear what you're asking. The field only gets updated if it gets changed, and should always trigger the macro when it gets changed. – Erik A Apr 16 '18 at 11:30
  • The forms on the front end of the database won't change the date_of_death field now that I've added that macro on the backend, when I enter data into the form the value in the table in the backend doesn't change. – Ashley Stewart Apr 16 '18 at 11:40