asked on 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
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
Microsoft Access
Last Comment
Helen Feddema
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
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!
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
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
ASKER
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!
Great,
Thanks
Glad I could help
Thanks
Glad I could help
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
http://www.helenfeddema.com/Files/accarch159.zip
http://www.helenfeddema.com/Files/accarch196.zip
(I also had to change the "system Separator" property to a semi-colon ( ;)