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

Posted on 2011-10-06
Last Modified: 2012-05-12
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!
Question by:jbarnette
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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".


    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...


    Author Comment

    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.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    <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.


    Accepted Solution

    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.

    Author Closing Comment

    Was able to find solution on my own, but the comments above steered me in the right direction. Thanks for the help.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    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...



    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now