How to auto-update multiple selection lookup fields in Access 2007?

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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not sure as most developers avoid using these new "Multivalued  Fields"
See here:

All I know is that using a standard Many-To-Many relationship, this is not an issue...
 To be fair, Many-To-Many relationships do not give you a "Slick" interface like you get with MVFs, but they work well without all the "Mystery".


Jeffrey CoachmanMIS LiasonCommented:
See this very basic sample


The form uses the Students table as the Parent table and the StudentClasses table as the Child table.

Note that when you change a class name, (obviously in the Classes table), then opening the Students form will display the updated class names in the dropdown on the subform...

jbarnetteAuthor Commented:
Thanks for the reply. I see the difference in what i have and what you example displays. I probably should have built it like that from the beginning. However, i have and it's too late to reverse it now as it's simply too large and complicated. Are you telling me that what i originally asked cannot be done? If so, i'll just accept it and just manually make the changes (there shouldn't be that many changes anyway).
Thanks again.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
<Are you telling me that what i originally asked cannot be done?>
No, I am just saying that there are so many "Issues" with Multivalued fields, that most Access developers don't use them.
Also not that there is no dirct equilvelant to these Datatypes in other Database programs (SQL server, MySQL, Oracle, al)
So when it comes time to upgrade, you *wont* be able to easily "convert" this data, ... and you will have to build many-to-many relationships anyway...

But to be fair, what you are experiencing definitely should not be happening...
Unfortunately, without having a full understanding of your DB's design, it is difficult to say...

<manually make the changes (there shouldn't be that many changes anyway)>
The issue with doing this is that this is not "automatic", so what happens when you are not around to "remember" to fix these issues.

Can you post a small sample of your DB that exhibits this issue?

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

jbarnetteAuthor Commented:
I was able to figure out the problem and correct it. The issue was that I needed to have a one-to-many relationship between the personnel table and the attendees table and then set that relationship to enforce referential integrity and cascade update related fields. That fixed it and it works great. Now when i change a record in the personnel table, it updates all the records in the attendees table with the new name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbarnetteAuthor Commented:
Was able to find solution on my own, but the comments above steered me in the right direction. Thanks for the help.
Jeffrey CoachmanMIS LiasonCommented:

As I said, I saw no reason why you had the original issue, because this is not a common problem here.

Glad you were able to work it out yourself...


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.