How to reposition an item in a listbox to the top of the list?

I have a combo box that is populated with a list of items.  When the user selects one of the items from the dropdown, it is inserted into a a table.  A listbox displays all records in the table and is based on a query.  After the combo selection, the listbox is requeried and sorted on the records that have been entered.  I would like to display the most recent selection of the combo box at the top of the listbox list and leave the rest sorted.  How do I move the most recently added item to the top of the listbox list?  There may only be 4-6 items in the listbox but could be more.  Probably not enough to scroll.  I would like to see the item selected in the combo box at the top of the listbox list with the other items displayed beneath it without disturbing there sort order.

Who is Participating?
GRayLConnect With a Mentor Commented:
If you want the most recently selected at the top, you should add a boolean field to the table and ORDER BY fldBoolean, fldWhatEver, fldWhatEver being the field you normally sort on.  Then in the Click event of the combo box you run an update query which - stores the record just clicked, then runs an update query setting all fldBoolean to False, then use the stored record to find the record in the list box, and then set fldBoolean to True.
Rey Obrero (Capricorn1)Commented:
what is the rowsource of the listbox?
leachjAuthor Commented:
The row source is an sql select statement.  Selecting all records from the diagnosis table that match the patient id.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Rey Obrero (Capricorn1)Commented:
post the sql statement, and to which field does the selected item from the combo box goes?
Dale FyeCommented:
Generally, you will have to provide a sorting mechanism if you want to do these sorts.

If the table that is used for the listbox contains an autonumber field, then you could use that as the sort mechanism, something like:


Since Autonumber fields are always increasing, the order of fields added to that table will be inverted by using the DESC operator in the ORDER BY clause.
leachjAuthor Commented:
SELECT tblDemographics.IDNO, tblPatientDiagnosisICD9.Diagnosis, tblPatientDiagnosisICD9.ICD9 FROM tblDemographics LEFT JOIN tblPatientDiagnosisICD9 ON tblDemographics.IDNO=tblPatientDiagnosisICD9.IDNO WHERE (((tblDemographics.IDNO)=[forms]![frmDemographics]![idno])) ORDER BY tblPatientDiagnosisICD9.Diagnosis;

The selected item in combo box is written to IDNO and Diagnosis.  
As you can see, it is already sorted.  The combo box lets the user select a primary diagnosis (there can be multiple diagnoses).  I want to put the primary diagnosis at the top of the otherwise sorted list.
The problem I see with using an integer field in the table, if the primary diagnosis is changed at some point, that field would have to be  changed and the new diagnosis assigned a rank to allow it to float to the top.  Thought I might be able to just let the normal sort happen with a requery and then move the primary diagnosis up the list to the top.
Rey Obrero (Capricorn1)Commented:
can you post sample data.. we need it to determine how the sorting should be done..

what type of data is the IDNO field?
what type of data is the  Diagnosis field?
leachjAuthor Commented:
AID          IDNO                 Diagnosis                                ICD9      
6        36            Intestinal infection due to e coli      008.04      

AID is auto number field
IDNO is the patients ID number used to uniquely identify the record related to other tables
Diagnosis is text.
Dale FyeConnect With a Mentor Commented:
Why not add a IsPrimary (Yes/No) field to the diagnosis table?  When you insert that record into the table, you would first update all of the other diagnosis (don't know the appropriate plural) so that IsPrimary = False.  Then insert the new diagnosis and set it's IsPrimary field to true.  Then you could add the IsPrimary field as the first field in the ORDER BY clause.

If the Primary diagnosis changes later, you would set all of the other IsPrimary to false and assign the new diagnosis a -1 in the IsPrimary field.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.