Can I have Sort Column Headers in a Combo Box?

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have a 'Find Record' combo box that works just fine; but would like to be
able to sort its columns by clicking on the Column Headers. Cab this be done?
 
Just set the OrderBy property of the form (remembering to turn on OrderByOn
as well.)

This kind of thing:

Function SortForm(frm As Form, ByVal sOrderBy As String)
On Error GoTo Err_SortForm
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")

If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.OrderBy = sOrderBy
frm.OrderByOn = True
End Function
 
You can fake this behaviour by writing code for the ComboBox's
MouseUp event.

I have a working example.
Post back if you're interested.

I've put most of the code in a class module, which you can import
into your project. Then, with a few lines of code in the MouseUp
event, you can instantiate the class and call its sort method,
which'll to do the trick. You can use the class with any number
of ComboBoxes.

Geoff




message
news:[email protected]...
 
Allen,

I'm slightly confused because your answer makes a lot of references to a
form. Is your code for a continous form or for a Combo Box; because I am
using a combo box; very similar to the one that the 'find record' wizard
generates, except I am using your superb, quicker code that I found on your
website.

Also, if this is for a combo box, where do I put the code?
 
Geoff,

some of this stuff goes beyond my abilities, particulary class module type
stuff. I don't think I'm too keen on adding additional class modules from a
distribution point of view. I was rather hoping that the solution would be
more 'built-in' to Access 2007.
 
Class modules probably sound more difficult than they are in
practice. It's mostly a copy-and-paste job, but I understand you
feel more comfortable with a built-in feature.

The Access ComboBox doesn't provide the built-in sorting feature
you need (not in my version of Access and not, by the sounds of
it, in yours either).

I checked out to see if you could make an API call but no luck
there.

There is a special call-back function you can use to fill a
ComboBox, but it's quite a challenge and wouldn't help you (no
sorting feature).

All in all, I think you're better off with a SQL statement as
the RowSource.

Given all of the above, I suggest you create two (or more)
Find-Record ComboBoxes, each sorted in the way you prefer. A bit
glitchy perhaps, but a straightforward alternative.

Geoff




message
 
Presumably you have an unbound combo that contains the names of the fields
you may wish to sort by?

If so, the combo's AfterUpdate event procedure would call the code like
this:
If Me.dirty Then Me.Dirty = False 'save any edits
With Me.[cboSortBy] 'Use your combo name here
If IsNull(.Value) Then
Me.OrderByOn = False
Else
Call SortForm(Me, .Value)
End if
End With
 
Allen,

It's not the form that needs to be sorted; it's the ComboBox -
preferably in response to a mouse click on a column heading in
the ComboBox.

Geoff





Allen Browne said:
Presumably you have an unbound combo that contains the names of
the fields you may wish to sort by?

If so, the combo's AfterUpdate event procedure would call the
code like this:
If Me.dirty Then Me.Dirty = False 'save any edits
With Me.[cboSortBy] 'Use your combo name here
If IsNull(.Value) Then
Me.OrderByOn = False
Else
Call SortForm(Me, .Value)
End if
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Allen,

I'm slightly confused because your answer makes a lot of
references to a
form. Is your code for a continous form or for a Combo Box;
because I am
using a combo box; very similar to the one that the 'find
record' wizard
generates, except I am using your superb, quicker code that I
found on your
website.

Also, if this is for a combo box, where do I put the code?
Click to expand...
Click to expand...
 
I've thought of another alternative.

You could place a command button next to the Find-Record
ComboBox. When the user clicks the button, it changes the
ComboBox's RowSource property to a different SQL (SELECT)
statement, thereby changing the sort order. (This isn't as
intuitive as the user clicking a column heading.)

BTW, the idea of programming with objects (which are created from
classes) is that someone else does the hard work for you. The
standard textbooks are full of classes you can use. You don't
necessarily need to understand how they work; in fact, that's the
point! You just need to know what a class does and how to use it.

Geoff
 
Back
Top