How to Sort Combo Box Items by Most Used - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

How to Sort Combo Box Items by Most Used

Dan North

41 days ago

I have a requirement to sort my combo box dropdowns by the most used items
I found a very basic link to do that.  See: https://www.bing.com/search?q=sort+combo+box+items+by+most+used+MS+Access

It works well enough, but it only increases the count (BTW I'm using the "HelperT" system that Mr. Rost explains very nicely).  Has saved me about 20 lookup tables and counting.
I have two questions (challenges?)
1st Can I convert this new code and the HelperT code to global so I only need to list and update it once?
Second How can I decrement and item count when the record is changed or deleted?
   Example, This particular field is for mailing lists the operator/customer is subscribed to.  When changed from say the "RCS Primary" list to the RCS Alternate list, I need I need to decrease the Primary list count and increase the Alternate list count.  I'm not sure how to capture the previous setting.  Would on Focus work?

Raymond Spornhauer @Reply

40 days ago

Can I convert this new code and the HelperT code to global so I only need to list and update it once?
- You will have to show us the code you are referring to in your specific application.

Second How can I decrement and item count when the record is changed or deleted?
Example, This particular field is for mailing lists the operator/customer is subscribed to. When changed from say the "RCS Primary" list to the RCS Alternate list, I need I need to decrease the Primary list count and increase the Alternate list count. I'm not sure how to capture the previous setting. Would on Focus work?

Have no idea what you're talking about... you'll need to be more specific.

-Raymond

	Thomas Gonder @Reply 40 days ago
I just love combo boxes that are constantly changing the order. Kinda like Microsoft changing how the time-honored right clicks appear in File Explorer. So much for muscle memory.

	Dan North OP @Reply 38 days ago
Thomas I pretty much agree sir, however this is what the boss asked for. The items should not change that much as some options are expensive and rarely used.

Dan North OP @Reply

38 days ago

Raymond This is one of the dropdowns.  Except for the helper ID they are identical, which is why I'm thinking Global.  I'm pretty sure Mr. Rost explained this in the HelperT video, I'm just not finding it.

Combo Box Row Source:
SELECT HelperT.HelperID, HelperT.HelperDropdown, HelperT.HelperDisplayOrderID FROM HelperT WHERE (((HelperT.HelperDisplay)=True) AND ((HelperT.HelperTypeID)=9)) ORDER BY HelperT.HelperDisplayOrderID DESC , HelperT.HelperDropdown;

Private Sub EmailTypeIDCombo_AfterUpdate()
    Dim sql As String

    sql = "UPDATE HelperT " & _
          "SET HelperDisplayOrderID = HelperDisplayOrderID + 1 " & _
          "WHERE HelperID = " & Me.EmailTypeIDCombo
    CurrentDb.Execute sql, dbFailOnError

    Me.EmailTypeIDCombo.Requery
    'HelperT.HelperTypeID=9
End Sub

	Raymond Spornhauer @Reply 38 days ago
Dan Can you show your Table in design view? -Raymond

Matt Hall @Reply

37 days ago

Dan , You have a couple of questions in here.  This might be what you are looking for here.

"When changed from say the "RCS Primary" list to the RCS Alternate list, I need I need to decrease the Primary list count and increase the Alternate list count.  I'm not sure how to capture the previous setting.  Would on Focus work? "

You can try something like this in the AfterUpdate Event :  EmailTypeIDCombo.OldValue

	Dan North OP @Reply 37 days ago
Raymond I'm not sure how to do that here. I tried a screen capture and a simple copy/paste as text, but nothing is coming through here.

Dan North OP @Reply

37 days ago

Matt That worked perfectly sir!  THANK YOU!  Here is the updated code.  Next step is to get it added as a global module.
    Dim sql As String

    '[20260324-DAN]add .oldvalue option to decrement the count of the old dropdown item as suggested by Mr. Matt Hall moderator at 599CD.com.  Works perfeftly!
    'Lower count of old dropdown value
    sql = "UPDATE HelperT " & _
          "SET HelperDisplayOrderID = HelperDisplayOrderID - 1 " & _
          "WHERE HelperID = " & Me.EmailTypeIDCombo.OldValue
    CurrentDb.Execute sql, dbFailOnError

    'increase count of new dropdown value
    sql = "UPDATE HelperT " & _
          "SET HelperDisplayOrderID = HelperDisplayOrderID + 1 " & _
          "WHERE HelperID = " & Me.EmailTypeIDCombo
    CurrentDb.Execute sql, dbFailOnError

    Me.EmailTypeIDCombo.Requery
    'HelperT.HelperTypeID=9

Matt Hall @Reply

37 days ago

You can do it in a global procedure or function. It will require some VBA. You will have to figure out how you want to pass the required information to the global module. The first question is how much of the form module do you want to move to the global module. To move all of it, you will have to pass all of the information needed for the global module to interact with the specific form and control that calls it. That would include, the old value, the new value, the form name, the control name, the HelperTypeID, and HelperDisplay. This is why Raymond was asking about images of the table and form in design view.

Before you click in the reply box, do you see an "Upload Image" box to the right of the "Reply" box? Also, I use WIN-SHIFT-S to snip the part of the screen that I want.

You might check out the Global Status Fn video to get an idea of what I am talking about.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View