Multiple sort options in single combo box?

[rkeesecker]

No problem creating 'sort by' buttons in a form with RunMenuCommand and SortOrderBy. However, it looks odd to have a bunch of buttons, one for each of the desired sorts. I've looked everywhere but can't find how to create a combo box with drop-down selections which each offer a sort option based on different fields in the form.

[June7]

What do you mean by 'sort option'? Do you want to give user a list of fields to choose to apply sort to?

Combobox has a Field List option in the Row Source Type property.

[Micron]

yes, your post doesn't give enough info. In addition to June7's question, does your current setup provide a pre-determined selection of sort options that are singular in scope, or are you providing a compilation of sorting fields for each option? And what is the form design? If a continuous form, in the first case you can use the header labels to affect a sort on that field by using the label double click event. If you've got a bunch of pre-determined sorts in mind, an option frame may be the way to go.

[CJ_London]

alternatively if your options are to simply sort on one field or another, educate the users to right click on a field and select sort

[rkeesecker]

Thanks for the prompt responses. I have been learning Access from the ground up over the past year. While I have gained some degree of functional knowledge, I have a lot to learn to make navigating more of the possibilities in Access second nature. My learning is primarily by trial and error, with help the other obligatory sources like books, online searches, Lynda, etc.

Yes, the form I wish to sort on is a continuous form. At least I know there is nothing to sort in a single form. (Like I said, I'm learning bit by bit lol).

@June7, yes, I wish to give end user "...a list of fields to choose to apply sort to?"
@Micron, thanks ... the header labels w/event assigned works great. I was too thick to think of it. Although, as @Ajax says, I will have to "educate the users" since there likely is no way to have an up or down arrow with that header ... ? This is to say place a tip in this regard on the form and/or in the help menu. This is why I would like the more intuitive option of simply selecting from the drop-down list and selecting the field for a sort. To use a dumbed-down analogy, I'm looking for the proverbial Sort By combo box we all find on shopping websites, i.e. Price Low to High, Price High to Low, Most Popular. You get the idea.

Option Group isn't visually what I want because it takes up real estate that is unnecessary, and I know there must be a better way. Hence my wish to have a drop-down list in a combo box with values such as 'Sort by Last Name', 'Sort by First Name', Sort by Date', etc. In creating the CB, I selected the option "I will type in the values that I want." Then I using the Expression Builder on the CB to assign a sort/order by value to each, but that isn't offered. I tried using the field list via the Row Source Type in the combo box, relating it to a table with the sort options, but couldn't refine it beyond that to sort functionality.

And, with the header scenario, is there a way to set it up with double-clicks to alternate ascending order and descending order?

As Rosanne Rosanadanna used to say, "Mr. rkeesecker, you sure have a lot of questions" or words to that effect. Thanks for your patience with me.

[June7]

Only way I know uses VBA. User selects field from combobox and VBA constructs sort parameter and applies to the form OrderBy property. User would have to select Ascending or Descending options in another combobox or a checkbox or a toggle button.

[Micron]

Yes, you can alter the sort order by doubling clicking. Set the sort order on form open, pass the sort (ASC or DESC) to a variable. On double click, get the value and reverse it
(If strSort = ASC then...set to DESC; If strSort = DESC then...set to ASC), reset the variable & refresh the form. As for being intuitive, colour the labels and put a small label nearby with caption "Double click green header to change sort) or something like that, as well as sending an email to users so they can ignore it . The arrow idea is not bad - cycle an image as well.
The option group, while maybe not the best, does not have to be on this form - you can open it and return the chosen value.
So if you can/have figured out how to sort on label clicking, you should be able to do the same with your combo box. Create one with a value list and write a Select Case statement to handle the sorting? This method would allow you to provide sorting on multiple fields.

[rkeesecker]

Only way I know uses VBA. User selects field from combobox and VBA constructs sort parameter and applies to the form OrderBy property. User would have to select Ascending or Descending options in another combobox or a checkbox or a toggle button.

OK, thanks. I've recently started delving into VBA, so this will give me a specific task in that regard. Also, it's good to know the standard macro and expression builder isn't up to this task, so that gets me just a bit off the hook for being a complete ignoramus. I'll work on this and post the results. Thanks again.

[rkeesecker]

Yes, you can alter the sort order by doubling clicking. Set the sort order on form open, pass the sort (ASC or DESC) to a variable. On double click, get the value and reverse it
(If strSort = ASC then...set to DESC; If strSort = DESC then...set to ASC), reset the variable & refresh the form. As for being intuitive, colour the labels and put a small label nearby with caption "Double click green header to change sort) or something like that, as well as sending an email to users so they can ignore it . The arrow idea is not bad - cycle an image as well.
The option group, while maybe not the best, does not have to be on this form - you can open it and return the chosen value.
So if you can/have figured out how to sort on label clicking, you should be able to do the same with your combo box. Create one with a value list and write a Select Case statement to handle the sorting? This method would allow you to provide sorting on multiple fields.

Thanks. Some of this is still another language to me, but I love learning it. Appreciate the suggested If expression. Will have to learn what a "Select Case" statement is, but that's OK, love doing it. I'll study and pursue your suggestions. Thanks very much.

[June7]

I tested label control DblClick event and it works but doubt this will be any more intuitive to users than Right Click. Every label control will need event code.

[rkeesecker]

I tested label control DblClick event and it works but doubt this will be any more intuitive to users than Right Click. Every label control will need event code.

Thanks. A number of new things to try. A year ago I knew virtually nothing about Access. I could open the program. That's about it. I really appreciate this help. If I presented any challenge to you folks with this - you who know what they are doing with Access - that's just icing on the cake.