Combo box, sort selected items first then non selected items in Multi select combo box... Ms Access

Sreeni Kambala
Sreeni Kambala used Ask the Experts™
on
How to sort selected items first then non selected items in Multi select combo box... Ms Access
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
what field decides if they are selected? What is the RowSource for your combobox?

if, for instance, you have a Yes/No field in your table that is keeping track of what is selected, you can add a column to the Query to sort and not show. True is -1. False is 0.

Author

Commented:
SELECT tbl_Courseprofile.COURSE_NAME, tbl_Courseprofile.COURSE_ID
FROM tbl_Courseprofile
ORDER BY tbl_Courseprofile.COURSE_ID;

But I would like to sort on multi select items first.check box.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Would you post an example?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I would like to see selected 4 subjects on top then non selected subjects 2 subjects.see attached file.
subject.jpg
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
by example, I meant an example database. Thanks.

Where do you want to display the selections? If in a report or query, there needs to be a field to save if it is selected.  I sometimes create a temporary YesNo field for processing for occasions like this.

Author

Commented:
ATTACHED DB,
sample.accdb
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
thanks for posting a sample.
What are you going to do with this information? where do you want the sorted list to appear? Just write it to a control on the form and do nothing else? On a Report? If a report, or for remembering, there needs to be a field to store whether or not it is selected.

It appears to me that you perhaps need a TrainerCourses table with foreign keys relating to ID in the Trainer table (that is what should be called TrainerID -- I would call the text field TrainerCode) and COURSE_ID

Author

Commented:
What are you going to do with this information? -> I use this information in edit mode, If I want to deselect one subject, it is easy to see selected items first.This will help when I have hundreds of records.

where do you want the sorted list to appear? --> Is it possible to sort in same combo box?
If not can you help me the code to populate selected items in below text box in 4 rows.

If I select 5 records, would like to see 5 rows in below text box.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
> "This will help when I have hundreds of records."


I see you are using a multi-select field but unless you are a programmer, this is a terrible thing to do because you will have to write code to process choices. Most developers don't use multi-select fields either.  While it might seem like a cool quick way to get started, they create a lot of work.  Read what I wrote about creating another table.

Author

Commented:
Storing the records in trainer profile table, column name COURSE_NAME.Please verify.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you are using a multi-select field -- much better to use a related table.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Please re-read my previous comments -- I may have edited them after you read them.  thanks.
Remote Training and Programming
Top Expert 2015
Commented:
Also, it is not possible to filter choices for a multi-select combobox if, for instance, a trainer just teaches certain types of classes.

I see you started with a template.  My recommendation, sad to say, it not to use the templates.  Better to build it yourself so it makes sense and works for a serious business.  To get started, read this free short book:

 Access Basics
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

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