?
Solved

multivalue lookup field only shows key index on form

Posted on 2011-10-20
6
Medium Priority
?
419 Views
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?
0
Comment
Question by:knfitz
  • 3
  • 3
6 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37001561
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37001605
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:
http://allenbrowne.com/access2007.html#Mixed.

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.

JeffCoachman
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37001821
Jeff,

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

Dale
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37002253
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, ...as 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.

;-)

Jeff
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37002325
Jeff,

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

Expert Comment

by:Jeffrey Coachman
ID: 37002794
knfitz,

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

JeffCoachman

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

807 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