working with multi-value combo box

In  the attached database, I'm using multi-value combo boxes to track employees (see tbl_Accounts).  I'd like the multiple names to appear with last name, first name and separated by a colon (Smith, Joe; Barry, Sam; Sweeny, Sue).  But instead it just shows last name.

And in my frm_SearchMulti form employees are showing with their ID number instead of name, and I'm getting duplicate rows.

How can I control what appears for these multi-value fields?
CC_Sample.accdb
Danny ReaderDirector of Advancement ServicesAsked:
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.

Dale FyeOwner, Dev-Soln LLCCommented:
First off, I would strongly recommend against multi-valued fields.  STRONGLY

Although at first blush they appear to be very useful, the actual guts of the process are hidden from the user and cannot be manipulated by the user.  Furthermore, it is extremely difficult to query records on fields that contain these multi-value fields.  The reason you are getting what looks like multiple records is that you included the [For Statement Mailing] and [Approval Signatories] columns in the query, and each of those fields contains two people checked, but the query is returning a separate row for each combination of those names:

Roberts/Hall
Roberts/Roberts
Smith/Hall
Smith/Roberts

which is why I don't use this feature

Generally, I would encourage you to learn how to create the linking tables used to identify one-many records (which is what Access does in the background when you set the "Allow Multiple Values" to Yes.  This would involve another table for each of those fields you have marked this way, and would require either a subform, probably the normal way of handling this, or maybe a multi-select listbox (more coding involved) to account for the Multiple people in each of these relationships.

Once you have learned how to do this, you will have much greater control of your queries and your tables.

HTH
Dale
Jeffrey CoachmanMIS LiasonCommented:
Like this?
(test on "For Statement Mailing" combobox)
CC_Sample.accdb

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
Jeffrey CoachmanMIS LiasonCommented:
looks something like this:
(I also had to change the "system Separator" property to a semi-colon ( ;)
mccb
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Danny ReaderDirector of Advancement ServicesAuthor Commented:
Jeffrey, Can you please direct me to the changes you made to get your result.

I see where you changed system separator, column count (from 3 to 2) and bound column (from 3 to 2).  But I applied those changes and didn't get the same results.  Thanks!
Jeffrey CoachmanMIS LiasonCommented:
Look at the rowsource for the combobox.

It is now:
SELECT [tbl_Staff].[Staff LName] & ", " & [tbl_Staff].[Staff FName] AS ProperName, tbl_Staff.RE_ID
FROM tbl_Staff ORDER BY [tbl_Staff].[Staff LName] & ", " & [tbl_Staff].[Staff FName];

...BTW, I also changed the bound column to 2
Danny ReaderDirector of Advancement ServicesAuthor Commented:
Jeffrey's help resolved the problem of how the names are appearing in my form.  But in order to avoid the duplicate row issue it seems that I will need to explore other avenues besides multi-valued fields, as Dale suggests.  Thanks!
Jeffrey CoachmanMIS LiasonCommented:
Great,
Thanks
Glad I could help
Helen FeddemaCommented:
I agree with Jeffrey about the inadvisability of using multi-valued fields.  They seem inviting to novices, but are very difficult to work with in code or queries.  If you do have to work with them, see my Access Archon articles on working with multi-valued fields, #s 159 and 196:
http://www.helenfeddema.com/Files/accarch159.zip
http://www.helenfeddema.com/Files/accarch196.zip
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.