CODEWITHSUNDEEP
ms-accessms-access-data-macro
jma
jma

Reputation: 497

MS Access AfterInsert macro update record

I have extremely limited knowledge of MS Access, but I'm trying to create an AfterInsert event.

Here's the table (myTable) context-- The table is going to be a list of goals for different departments, and have an Active column. This will allow a department to change it's #1 goal by creating a new record, setting it as goal = 1, and then updating the old record with goal = 1 to Active = False. We want a running history of the goals and priorities (goal number).

AutoID  Dept  Goal#  Goal               Active
00001   A     1      My first goal      True
00002   A     2      My second goal     True
00003   A     3      My third goal      True

Now, I want to add a new record because we've changed goal one.

00004   A     1      My new first goal  True

And what it should do after adding this new record is set old goal 1 Active = False.

AutoID  Dept  Goal#  Goal               Active
00001   A     1      My first goal      False
00002   A     2      My second goal     True
00003   A     3      My third goal      True
00004   A     1      My new first goal  True

I've been trying to adapt this AfterInsert format

LookupRecord In qryDMTermQuery
 Where Condition = [EmployeeID]=[tblTerminations].[EmployeeID]
 Alias
 EditRecord
    Alias
    Comment Block: Set Active field to False
    SetField
    Name: [qryDMTermQuery].[Active]
    Value: False
 End EditRecord

This is my adaptation:

LookupRecord In myTable
 Where Condition = [dept]=[myTable].[dept]
                 AND [Goal#] = [myTable].[Goal#]
                 AND [AutoID] != [myTable].[AutoID]
 Alias
 EditRecord
    Alias
    Comment Block: Set Active field to False
    SetField
    Name: [myTable].[Active]
    Value: False
 End EditRecord

Does this make sense/is this doing what I'm hoping for?

Upvotes: 1

Views: 1514

Answers (1)

Santosh
Santosh

Reputation: 12353

You were almost there. There is no need for [AutoID] != [myTable].[AutoID] as the new ID generated obviously won't match the existing. Alternatively you can use [AutoID] <> [myTable].[AutoID]

enter image description here

Upvotes: 1

Related Questions