How to auto-update multiple selection lookup fields in Access 2007?
Posted on 2011-10-06
Let me start with an explanation of how my database is set up. I have one table that has a field with a list of employees called "personnel". There is another table that has a field called "attendees" that is a lookup field that looks up data from the personnel field. It is also a multiple selection field. Basically i pull up the table with the attendees field and use a drop down list to select all the employees that attended a meeting.
My problem is this. If I have a name changes, such as someone got married, I need to go into the personnel field and change their name. When i do that, here is what happens. When i open up the attendees field, their old name is there at the bottom of the drop down list and is selected (assuming i had selected it before) and their new name also appears in the list as unselected. It doesn't update the name in the attendees field, but rather saves the old name and moves it to the bottom of the list (I'm assuming because it no longer exists the look up fields anymore) and creates the new name in the list.
I know there is a way to auto update all the other fields if i make a change in the main field being used as the look-up field. Does anyone have any suggestions as to how I can do this? The relationship between the tables is a One-to-Many Join Type 1.
Thank you for your help!