1

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?

June7
  • 19,874
  • 8
  • 24
  • 34
jma
  • 457
  • 7
  • 18
  • Is this a DataMacro? I don't use macros, only VBA. If you have been trying, what happens - error message, wrong results, nothing? Exactly what is the issue? Code looks okay, you tell us if it is doing what you hope for. – June7 Mar 05 '18 at 20:41
  • @June7 There's nothing the matte with using the Data Macros. If your DB is going to be used outside of an Access front end where VBA can run then macros are the only way to ensure the same logic happens to your data no matter what. – Brad Mar 05 '18 at 21:23
  • @Brad - Didn't say anything wrong with just I don't use. Never needed to. OP doesn't mention other platforms. – June7 Mar 05 '18 at 22:25
  • @June7 I don't know what this is-- if you click on an Access table, the on the Table tab, you can select options for different actions to happen for records before or after they're inserted, updated. This is an after insert command. No error was thrown, but when I added a new record, nothing happened. – jma Mar 07 '18 at 15:38
  • @Jenny, that is a DataMacro. Is Active a Yes/No type field? Advise you not use spaces, punctuation, nor special characters in naming convention (underscore would be only exception). Better would be GoalNum or Goal_Num. – June7 Mar 07 '18 at 18:04
  • Macro has compile error with `!=` so I changed to `<>`. I tried using query per the article. Nothing works. I would use VBA behind a form. – June7 Mar 07 '18 at 18:46
  • Here is article from the same source on how to debug DataMacro. http://sourcedaddy.com/ms-access/debugging-data-macros.html. I find the macro executes but fails because for some reason the record is not updatable. Doesn't matter if I use query or not. The difference with your situation is you are editing a record in the same table the new record is created in. – June7 Mar 07 '18 at 19:13

1 Answers1

1

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

Santosh
  • 12,175
  • 4
  • 41
  • 72