Table afterupdate Event issues (1 Viewer)

I have a roster called tblroster with PK as SSN. I have another 2 tables that work with roster called tblPositionNumber and jnctblPositionRoster. Basically, I import an excel doc that updates a code on tblroster. I have a notes field on tblPositionNumber that needs to be updated when this code changes.

I have an afterupdate event set up like this (the codes field name is TCode):

This triggers if the code doesn't match the old code:
if [TCode]<>[old].[TCode]
SetLocalVar
Name: PositionID
Expression = Dlookup("[PositionID]","[jnctblPositionRoster]","[jnctblPositionRoster].[RosterID]=[tblRoster].[SSN]")

Look Up A Record In tblPositionNumber
Where Condition = [jnctblPositionNumber]=PositionID
Edit Record
SetField


ok so the set fields don't matter. The thing is that all of this works when done manually. I update the TCode on tblRoster, it looks up the persons positionID, finds them on the junction table and sets the field that I want properly however, when done with a query as part of the import/update function I've created in vba, it has trouble. It was just finding a random PositionNumber and trying to update that... at least as far as I could tell it was random. But every single person that had changes showed the same PositionNumber when they obviously aren't. It does find the right SSN but just the wrong position.

Anyone have any idea why it would do that? I would really prefer to do this in VBA but I can't seem to find a way to "monitor" tblRoster during update. It also does me no good to annotate every field based on the fact that TCode is present since TCode can come, go and change weekly so I would need some way of comparing old values to the new updated ones since it only matters if it's new or changed.

Hopefully this wasn't too long to turn people of to reading it and someone can give me an idea. Thanks for any assistance!!

When you want us to help solve a code problem, you really need to post the code rather than just a summary of it. Having a summary of what you are trying to do is very helpful and that's what most people omit.

Not sure what you mean. It's not a sample... that's the code. I just used the macro function names like you would find in the editor since it's not vba. Only thing missing is the setfield part.

Edit: the other thing is that like I said... the code works manually but not when done with a query so I'm really looking to see if that's normal or if someone has an idea

Sorry, you mentioned VBA and I wasn't expecting to be looking at a macro since we have a separate forum for macros.

Is there only one row in jnctblPositionRoster that matches the RosterID? If not, the results returned from the DLookup() will be random.

Beyond that, you might want to move this question to the Macro forum where someone who works with macros can help you. Please link to this question so you don't double post. I would move it for you but I don't remember how.

Sorry I didn't get back to you earlier but I am apparently unable to access this site from work. I guess that's something new. Anyway... yes the the junction table only allows one RosterID input. From tblRoster to jcntblPositionRoster it's a one-to-one.

I will link to this question as you suggested. To be honest... I thought I put it in the right section since it's a table datamacro. Thanks for the help.