Sorting data within a form I've created

  • Thread starter Thread starter Jackmac
  • Start date Start date
J

Jackmac

Hi all,

Hopefully an easy one for somebody...

I've created an input form for users to type in data about customer sales
etc. They also use it when the customer calls to ask a question. At the
moment the database (and therefore the form) shows the data as being sorted
on the autogenerated ID number which is the primary key. I want to put a
button on the form so they can sort it in order of say Name, Postcode etc.
Any ideas how?

TIA

Jack
 
Jackmac,
one way is to put a combo box containing a list with ways to sort the form.
When the combo drops down the user sees
Sort by Name
Sort by Postcode
Sort by etc . . .

When the user selects say, sort by name, your code creates a SQL string to
use as the recordsource of the form.
This sql string would have an ORDER BY clause for name.

You can set up the sql string in 2 parts.
The main part is the Select clause, the From clause and the Where clause.
This first part would be fixed and you could call it strStub.
The second part would be the tail = strTail

The whole sql clause would be strStub & strTail

On the after update event of the combo box you put code like this

Dim strSQL as String
Dim strStub as String
Dim strTail as String

strStub = "SELECT yadda, yadda " _
& "FROM tblOne " _
& "WHERE yadda, yadda "

Select case Me.cboSort
Case "Name"
strTail = "ORDER BY tblOne.LastName"

Case "Postcode"
strTail = "ORDER BY tblOne.LastName"
End Select

strSQL = strStub & strTail
Me.RecordSource = strSQL


With the above idea, after user makes a selection in the combo,
the form's recordsource changes to sort the form in the way wanted.


Jeanette Cunningham -- Melbourne Victoria Australia
 
Back
Top