multivalue lookup field only shows key index on form

Posted on 2011-10-20
Last Modified: 2012-05-12
I'm using a record navigation control in a form mapped to a table in access. When I select something from the frop down then all the other fields on my form move to that record. The problem I'm having is a field mapped to a lookup column that allows multiple selections. I want it to list only what's selected in the lookup field. When I add the control to the form the lookup field comes over as a combo box and gives a long list with check boxes. I tried to change it to a text field but them it only shows the key index number of the record. How do I get it to show only the selected values from the lookup field?
Question by:knfitz
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    My advise is to get rid of your multivalue fields.

    Basically, these are hidden tables that you cannot directly access which allow Access to recognize a one-to-many relationship.  They mask what is really going on in the background and significantly reduce functionality within your application.

    I would create the table that contains the potential values, and a separate joining table which contains fields for foreign keys to your primary table and this "potential values" table.

    Then you can use a subform or a multiselect list box to select values from your 'potential values' table and assign them to the records in your primary table.

    Doing this will give you full control of your application.
    LVL 74

    Accepted Solution

    All I can say is that most Access developers avoid Multi Valued Fields.
    They have issues and are far from perfect when they do work.
    See Here:

    As an "Interface Tool", they are slick, ...just to be fair...

    Remember that as of this writing, there is no direct equivalent field for MVFs in SQL Server or any other Database product.
    So when it comes times to upgrade, you will have to do this the Old fashioned way anyway. (Many-To-Many Relationships.)

    Perhaps if you posted a simple version of this DB, an expert could propose a workaround, ...or solve the issue directly.

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    For full disclosure, Sharepoint (not a "database product") also has multi-valued fields, and I think that Access/Sharepoint integration is probably one of the reasons for adding this misguided "feature".

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Dale, yeah, I had heard that from Scott.

    So I see the value if this was constrained strictly to Access-SharePoint integration applications.

    It seems to me that adding it to Access as a "Datatype" made it all too easy for end users to use these fields without knowing the full consequences/repercussions.

    Like most Experts here, I try not om make statements like they are "Evil", or that users should "Never use them".

    As I stated, an "Interface option" they are a great addition, ...I just wish creating them actually exposed more of the behind the scenes Many-to-many relationships, as this is a topic that is crucial to understanding how relationships work.


    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    Concur.  The problem I see is that it hides the "behind the scenes" and newbies are not fully aware of what that means and the consequences thereof.

    I have to interface with several Sharepoint applications which use multi-value fields, and it is extremely annoying to deal with when you don't have visibility of the "linking" table through either Sharepoint or Access.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Can you post a sample of this DB?

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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!

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now