Interactive Sorting tool

[Chrisopia]

I have a list box populated by a query, which in turn is populated by a text box - a custom made search tool basically.

I want to create a drop down, or maybe a series of buttons to enable someone to change the sorted column, and sort type.

Most tutorials on the internet tell me to edit the query directly, but there must be a way to choose the sorting method from outside of a query.

I have a feeling it has something to do with turning the query into a SQL, and amending the

ORDER BY tblCustomers.CustomerID DESC;

to add a variable, but how will I link it to a drop down box?? Or interchange between ACC and DESC??

 

[vbaInet]

To start you off, look into the OrderBy and the OrderByOn properties of the form. You can set those using VBA.

 

[Chrisopia]

Thankyou vbaInet,

I have found all I can about OrderBy but it seems to only sort the form, and not control objects (e.g. my List box) within the form.

I have tried the following combinations:

Private Sub SortListBy_AfterUpdate()
Dim SortString As String

SortString = CustomerID

If Me.SortListBy.Value = "Entry order" Then
SortString = "CustomerID"
ElseIf Me.SortListBy.Value = "Company Name" Then
SortString = "Company Name"
ElseIf Me.SortListBy.Value = "First Name" Then
SortString = "Firstname"
ElseIf Me.SortListBy.Value = "Surname" Then
SortString = "Surname"
End If

Me.SearchBox.OrderBy = SortString
Me.SearchBox.OrderByOn = True

End Sub

qrySearch.OrderBy = SortString
qrySearch.OrderbyOn = True

I cant quite figure it out

 

[John Big Booty]

This thread may be of interest to you.

 

[nanscombe]

Hi Chrisopia,

I've had a bit of a play and put together this attached example.

I believe it does what you are after.


It appears to be along similar lines to the thread that John Big Booty mentioned.

It just seems a pity to waste it.

 

[Chrisopia]

Thats perfect nanscombe.

I really appreciate the annotations too, to help me understand how to get around it, and I've learnt something about SQL along the way!