working with multi-value combo box

Danny Reader
Danny Reader used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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
MIS Liason
Most Valuable Expert 2012
Commented:
Like this?
(test on "For Statement Mailing" combobox)
CC_Sample.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
looks something like this:
(I also had to change the "system Separator" property to a semi-colon ( ;)
mccb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Danny ReaderDirector of Advancement Services

Author

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 Liason
Most Valuable Expert 2012

Commented:
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 Services

Author

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 Liason
Most Valuable Expert 2012

Commented:
Great,
Thanks
Glad I could help
Top Expert 2009

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial