I apparently posted my original question in the wrong are so it's located here at the link below. Essentially I know that the macro is working when I update the one field manually. So I know all the looks ups and what not work however, when I use a query to update tblRoster, the macro doesn't work right. Is that just a limitation to data macros? Queries don't work with them well?
Table Data Macro Issues (1 Viewer)
- Thread starter dhannant
- Start date
Forgot to post the link to the original question.
https://www.access-programmers.co.uk/forums/showthread.php?p=1574688#post1574688
https://www.access-programmers.co.uk/forums/showthread.php?p=1574688#post1574688
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 16:36
- Joined
- May 7, 2009
- Messages
- 19,232
Code:
IF [TCODE] <> [Old].[TCODE]
Look Up A Record in inctblPositionRoster
Where Condition = [inctblPositionRoster].[RosterID]=[tblRoster].[SSN]
SetLocalVar
Name PositionID
Expression = [inctblPositionRoster].[PositionID]
Look Up A Record in tblPositionNumber
Where Condition = [inctblPositionRoster].[RosterID]=[PositionID]
Alias T1
EditRecord
SetField
Name T1."yourFieldNameHere"
Value "yourValueHere"
End EditRecord
End If
Last edited:
Thanks for the response however, this seems to give me an error even during manual updates now.
Description:
The identifier '[jnctblPositionRoster].[PositionID]' could not be found.
Context:
SetLocalVar PositionID, [jnctblPositionRoster].[PositionID]
Also... I think there was a type-o on the 2nd lookup as it references rosterID. That still didn't fix this issue but... figured I'd point it out.
Description:
The identifier '[jnctblPositionRoster].[PositionID]' could not be found.
Context:
SetLocalVar PositionID, [jnctblPositionRoster].[PositionID]
Also... I think there was a type-o on the 2nd lookup as it references rosterID. That still didn't fix this issue but... figured I'd point it out.
Last edited:
So I can't upload the actual database due to PII info and the fact that I'm now at home but I have a sample DB I use to work on ideas which I've attached. It's set up the exact same way as the DB at work minus fields that don't really come into play for this scenario. It doesn't have any forms since it's all table datamacros at the moment.
oh and just incase anyone is curious the personnel info in there is all made up using a vba function I wrote just to populate things like this easier. Got tired of making up fake data.
oh and just incase anyone is curious the personnel info in there is all made up using a vba function I wrote just to populate things like this easier. Got tired of making up fake data.
I have reattached your sample db. I modified your data macro and added an Update query (Query1) for tblRoster. If you change the criteria for the update query and run it you'll see that the Notes field in jnctblPositionNumber changes and shows the old value of TCode in tblRoster. No idea if this is what you want the Notes field to show but you should be able to modify from there.
ok so on tblRoster goto the AfterUpdate event and you'll see the code written as above. Basically, when you change TCode to anything other than it's current state (the only one that has a TCode assigned at the moment is Jason Vorhes... just for fun).
When that code changes it should look up the position he is assigned to on jnctblPositionRoster according to his SSN which is the Primary Key on tblRoster. Taking the PositionID it then looks up the associated Position on jnctblPositionNumber and ands "Test" to the Notes field.
That's it. Like I said... I can get it to work manually (or I used to be able to... can't get it to work with the new changes) but it doesn't work when I run the query to update TCode over a large group (up to 800 prsnl and maybe 50 or so code changes).
When that code changes it should look up the position he is assigned to on jnctblPositionRoster according to his SSN which is the Primary Key on tblRoster. Taking the PositionID it then looks up the associated Position on jnctblPositionNumber and ands "Test" to the Notes field.
That's it. Like I said... I can get it to work manually (or I used to be able to... can't get it to work with the new changes) but it doesn't work when I run the query to update TCode over a large group (up to 800 prsnl and maybe 50 or so code changes).
wow that's crazy... I was so close to the write code. Macros seem so much more difficult than vba I feel like. Anyway thanks for the help guys I really appreciate it. I'll try this out larger scale tomorrow at work but being that it works like this I see no reason it should work out.
By the way... why does it seem to throw and error when updating straight from the tblRoster? I know users should never get to the table but I tried and it doesn't seem to work. I had done it manually in the past and it worked fine.
EDIT: I think I figured out why... not all of the personnel are assigned to a position so it's not finding the number. So I need error trapping for that situation. Cool... thanks again!!
By the way... why does it seem to throw and error when updating straight from the tblRoster? I know users should never get to the table but I tried and it doesn't seem to work. I had done it manually in the past and it worked fine.
EDIT: I think I figured out why... not all of the personnel are assigned to a position so it's not finding the number. So I need error trapping for that situation. Cool... thanks again!!
- Local time
- Today, 04:36
- Joined
- Jan 23, 2006
- Messages
- 15,379
I looked at the log in the sample from Beetle, my guess is there is no record for that PositionID in table jnctblPositionRoster. I'm not sure of the difference in -8987 (SetLocalVar) and -8429 (LookupRecord).
If you lookup a record in a table and it isn't there....?
How do your jnc.. tables get populated?
If you lookup a record in a table and it isn't there....?
How do your jnc.. tables get populated?
Code:
ID SourceObject Data Macro Instance ID Error Number Category Object Type Description Context Created
23 tblRoster.AfterUpdate {962A9840-EFFB-44C0-BD01-6EEE5A47D439} -8987 Execution Macro The identifier '[jnctblPositionRoster].[PositionID]' could not be found. SetLocalVar PositionID, [jnctblPositionRoster].[PositionID] 24-Apr-18 7:09:02 PM
24 tblRoster.AfterUpdate {5CEA1D71-84A5-467B-87F1-D6AB5B485BAB} -8987 Execution Macro The identifier '[jnctblPositionRoster].[PositionID]' could not be found. SetLocalVar PositionID, [jnctblPositionRoster].[PositionID] 24-Apr-18 7:12:43 PM
25 tblRoster.AfterUpdate {FBC1889E-545D-4BE9-96FB-D76D2D26C578} -8987 Execution Macro The identifier '[jnctblPositionRoster].[PositionID]' could not be found. SetLocalVar PositionID, [jnctblPositionRoster].[PositionID] 24-Apr-18 12:25:17 PM
26 tblRoster.AfterUpdate {AB520BE7-95F9-4E79-959A-27441DA00FAF} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
27 tblRoster.AfterUpdate {3FB3A3A8-71E8-4348-ACF7-3DA9D6F2B6D2} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
28 tblRoster.AfterUpdate {83FE3034-EE64-4BFE-ABC2-4AD422566EA8} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
29 tblRoster.AfterUpdate {2237024A-A849-4ACD-A5D6-60EDB570FAB6} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
30 tblRoster.AfterUpdate {E7FA04ED-A556-45F9-8F16-DBB47442A27B} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
31 tblRoster.AfterUpdate {BD98DD70-B2C7-45F9-8C5F-F6CE76E92CDF} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
32 tblRoster.AfterUpdate {FFE8D49E-89A3-4313-969F-0C90BB39A14B} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
33 tblRoster.AfterUpdate {82390E01-00FC-449D-A379-A6D05F90742D} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
34 tblRoster.AfterUpdate {01FBF91A-917B-4BEA-B406-9C035F16B765} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
35 tblRoster.AfterUpdate {9E3B5FE5-4604-44BF-B01A-8CDD8F4D63BB} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
36 tblRoster.AfterUpdate {30ADBB73-155F-426E-94F7-76125C822F5C} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
37 tblRoster.AfterUpdate {0EA00651-F794-4B0A-97F7-85A7372C6F4A} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
38 tblRoster.AfterUpdate {5E81BAD6-C9FC-467F-8B87-E29715ED093F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
39 tblRoster.AfterUpdate {5C4E35BB-3DCF-4CA7-9092-3CFD7FD9ED02} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
40 tblRoster.AfterUpdate {BECEEF3F-62F0-41D8-8278-5FD094BE0EA5} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
41 tblRoster.AfterUpdate {7A2563EA-0BA4-4379-994E-425118A36585} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
42 tblRoster.AfterUpdate {F4EABB45-51D0-4BC5-8AA8-C5CF2403C42F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
43 tblRoster.AfterUpdate {050D0030-B446-4463-A082-66F70077FFB6} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
44 tblRoster.AfterUpdate {8A7BE3B4-3273-4558-9CEE-DC743559276F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
45 tblRoster.AfterUpdate {015A8955-95D4-4367-BD09-8661681BDFA1} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
46 tblRoster.AfterUpdate {63C524F8-50E1-4BD3-95B0-0F31D7E37FE8} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
47 tblRoster.AfterUpdate {EA546B07-E1A6-4FFF-906F-088E91FE6388} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
48 tblRoster.AfterUpdate {DC5BF1F0-1DA9-4BCA-8BE2-287FED4D8760} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
49 tblRoster.AfterUpdate {ABD5A011-F6B0-4EE6-9821-736C2E1BB2E3} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
50 tblRoster.AfterUpdate {C305486A-39F4-4857-95DF-9F33713F460A} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
51 tblRoster.AfterUpdate {C3BF16BC-F32F-4651-8639-AC4441DF9850} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
52 tblRoster.AfterUpdate {6EDEC142-3B19-40C4-8303-A88C186D6260} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
53 tblRoster.AfterUpdate {CD913380-7DD0-4953-91E5-C4B3D1D11947} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
54 tblRoster.AfterUpdate {0A4CF646-A8F2-44E2-9049-5BB0A0AFB95D} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
55 tblRoster.AfterUpdate {A9506421-7FC0-450E-ABB4-E3466DDD4D2F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
56 tblRoster.AfterUpdate {D383E015-B321-42C0-8BA3-6FFEEB4BC57D} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
57 tblRoster.AfterUpdate {840075F1-1585-4183-9FFF-F3E35BE6920E} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
58 tblRoster.AfterUpdate {0123D052-992B-491A-B161-CC04B01F0EB0} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
59 tblRoster.AfterUpdate {170E3B37-BE90-4049-9758-1380D51C7962} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
60 tblRoster.AfterUpdate {5B551F12-D866-4023-8ADE-0D4F1893471F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
61 tblRoster.AfterUpdate {7D0EFF68-BB7C-45E2-A747-6647F98C3793} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
62 tblRoster.AfterUpdate {3E3346A8-F409-4705-AAD5-63E92E5A7387} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
63 tblRoster.AfterUpdate {0115C902-7E3F-4500-B25D-3D6363889EBC} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
64 tblRoster.AfterUpdate {E7EC6018-56CD-4525-AA78-20E06E900A41} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
65 tblRoster.AfterUpdate {EB6E6FD0-2110-4FA7-8D6D-8CFB0DCC62C2} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:16 PM
66 tblRoster.AfterUpdate {DBC4BBF6-A473-4CD0-84CE-1246DC61240D} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
67 tblRoster.AfterUpdate {0BBF8CD9-8668-4BD0-A562-16FAC957C781} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
68 tblRoster.AfterUpdate {17150885-1123-446F-920D-63D143DE7A12} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
69 tblRoster.AfterUpdate {459BCC1D-6C8D-41F1-852B-CD215EA43C8B} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
70 tblRoster.AfterUpdate {759A3048-EBE6-43E3-878E-B790F7E9ADB6} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
71 tblRoster.AfterUpdate {15FF561B-E3B7-4E32-BA34-3A0C13B2CC8B} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
72 tblRoster.AfterUpdate {8A5E84F2-F454-477C-B8F9-38C43338CA97} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
73 tblRoster.AfterUpdate {4F46E0DE-F1E2-4826-A1E6-C2990857E4CD} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
74 tblRoster.AfterUpdate {56EFEAFC-9864-4BCA-A35C-C60EF9BBD0FD} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
75 tblRoster.AfterUpdate {B71D6D22-E858-4A05-8486-C68998824F23} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 12:34:17 PM
76 tblRoster.AfterUpdate {3B4F62B9-E31C-4ADE-95D4-41CFA6A25D0F} -8429 Execution Macro The query failed to execute because the identifier '[PositionID]' could not be found. LookUpRecord jnctblPositionNumber, [jnctblPositionNumber].[ID]=[PositionID] 24-Apr-18 2:58:39 PM
Last edited:
jdraw,
I came to the same conclusion after running a few tests. Normally, in the actual database at work it's all done through the use of forms. Members get populated automatically to tblRoster during an import function from excel, after that personnel are assigned to positions if required. During this same function, TCodes are updated. Eventually, the positions piece is going to coincide more with areas of responsibility (geographical regions more than likely).
I came to the same conclusion after running a few tests. Normally, in the actual database at work it's all done through the use of forms. Members get populated automatically to tblRoster during an import function from excel, after that personnel are assigned to positions if required. During this same function, TCodes are updated. Eventually, the positions piece is going to coincide more with areas of responsibility (geographical regions more than likely).
- Local time
- Today, 04:36
- Joined
- Jan 23, 2006
- Messages
- 15,379
Did you look at the thread I mentioned in post #5?
There are some comments in the thread that would suggest Table data macros are suited to AuditLogging, but perhaps not for much else.
I don't use macros, and only dabbled with table data macros to create/investigate an alternative AuditLog technique because I found errors/limtations in techniques that were referenced in forums.
I don't know your overall requirements, so can't suggest options. However, if you are working from forms and events, I'm sure you can update appropriate tables and records without table data macros. I'm not saying you should, just saying I think it is possible and common.
Good luck.
There are some comments in the thread that would suggest Table data macros are suited to AuditLogging, but perhaps not for much else.
I don't use macros, and only dabbled with table data macros to create/investigate an alternative AuditLog technique because I found errors/limtations in techniques that were referenced in forums.
I don't know your overall requirements, so can't suggest options. However, if you are working from forms and events, I'm sure you can update appropriate tables and records without table data macros. I'm not saying you should, just saying I think it is possible and common.
Good luck.
First, I agree with JDraw in terms of table level Data Macros. They really have limited use cases. They were introduced (I believe) as part of M$oft's misguided attempt at Access Web Apps, and have stuck around even after the web app debacle has been abandoned. You're better off using VBA for something like this.
Second, I didn't make any attempt at error handling in the sample I posted, but this would be simple enough by just checking the PositionID variable for Null and stopping the macro. See screen shot below for modified macro.
Second, I didn't make any attempt at error handling in the sample I posted, but this would be simple enough by just checking the PositionID variable for Null and stopping the macro. See screen shot below for modified macro.
Attachments
-
Screen1.PNG
I agree with both of you in the sense that this my least prefered method of doing this however, I've not found a better way. In my original post i mentioned I would much rather find a vba solution, but I just haven't found it. The problem is that this code isn't updated from a form. It's updated only during import from excel.
I attempted to write some code comparing the imported excel doc "Roster (RAW)" and tblRoster before it updated everything with a query however, I had to cycle to each record set and compare SSNs. If the SSN didn't match would move to the next RS in one of the tables until it found the matching SSN, THEN it could compare old and new values or added/removed values. I thought this way might be easier but I don't think it was. In the end I believe I will eventually write the code but at least for now... something is working.
and to answer your question jdraw I didn't get a chance to review it in it's entirety but I do have it open on my machine for when I get a chance. Unfortunately, I still can't get to this site from work so I'm kinda limited on time. Good news though, I tested this macro on the full DB and it worked perfectly. Since this is the first run properly I had 125 prsnl updates and all notes were made as hoped. So thank you both very much for the assistance!
I attempted to write some code comparing the imported excel doc "Roster (RAW)" and tblRoster before it updated everything with a query however, I had to cycle to each record set and compare SSNs. If the SSN didn't match would move to the next RS in one of the tables until it found the matching SSN, THEN it could compare old and new values or added/removed values. I thought this way might be easier but I don't think it was. In the end I believe I will eventually write the code but at least for now... something is working.
and to answer your question jdraw I didn't get a chance to review it in it's entirety but I do have it open on my machine for when I get a chance. Unfortunately, I still can't get to this site from work so I'm kinda limited on time. Good news though, I tested this macro on the full DB and it worked perfectly. Since this is the first run properly I had 125 prsnl updates and all notes were made as hoped. So thank you both very much for the assistance!
