strive4peace

If you like my Sort123 Code, please donate so I can keep on developing.

Sort123

Extra touches for "topnotch database designs that work but won't take a lifetime to create"

Access Basics

Video Tutorials

Learn VBA

ListFiles Database

Random Picker

Error Handling

Reference Database

Whistles & Bells

Help Examples

Crystal's Analyzer

Code Documenter

Sort by any column(s) in a Continuous Form by clicking the Column Header

Data Example: a couple hundred songs that have hit #1 on the charts from the 1890s to now.

Sort123

go to the code

Use to
Sort by any column(s)
in a Continuous Form
by clicking the Column Header

Toggles between
Ascending and Descending Order

Quickly find out where
information is missing
as blanks are lower than values.

Quick Jump To Sections

Data Structure of Example
Form RecordSource
- Combobox RowSource
- Combobox ControlSource
GenreID Combobox
Calculated Field in RecordSource
Sort123 Parameters
Sort123 Code
Standard (General) Module
Code Behind Form
Sort By Examples
Indicate Sorting with Italics
Download Database
Comments

Go To Top

Data Structure of Example

Firstly, I must preface this example by telling you that this data is not normalized. I want to use an example that will be interesting for you and has lots of columns with different types of data. Since I love music (doesn't everybody?), I used information from a personal database I am compiling to study popular songs. While I have thousands of songs in my database, I only extracted a couple hundred records. If you notice any errors in the data, please let me know.

The result is a non-normalized table of hit song information. I don't want you to assume this is a good way to design tables. From a structure point of view, this is a very bad example! ... but it is good for what I want to show you.

The Genre information is normalized. The Primary Key in the Genres table is an autonumber field called GenreID. Genre is a text field that descibes a genre such as Country, Rock, and Blues.

In the Hits table, GenreID is a Foreign Key to Genres and is stored as a Long Integer. By knowing this value, the text field to describe the genre can be looked up anytime. The text corresponding to the GenreID is displayed using a combobox — from a table not in the RecordSource of the form. The fact that the Genres table got added to the form RecordSource has nothing to do with the combobox text displaying.

Here are the fields from the Hits table that were used on the form:

HitID
GenreID
Songname
Artist
FirstDate
nDays
Decade
BPM
Dur
Album
WrittenBy
RecordedBy
TheLabel

This form turned out to be very wide. Therefore, the Song and Artist are repeated at the end of the row for easy reference.

Go To Top Go To Quick Jump

Form RecordSource

Here is the RecordSource for the form:

		SELECT Hits.*
		, Genres.Genre
		, Format([FirstDate],"mmm") & Chr(13) & Chr(10) & Format([FirstDate],"yyyy") AS FirstDateMoYr
		FROM Hits 
		LEFT JOIN Genres 
		ON Hits.GenreID = Genres.GenreID;

It is good practice to use asterisk ( * ) to pull fields from the main table for the form. By doing this, if you change, add, or delete fields, everything is still available.

in our example:

The Genres table must be in the form RecordSource since the form is sorted by Genre when the user clicks on the column header for GenreID. Genre is also specified as a secondary sort for other columns. A form can only be sorted by fields that are in the RecordSource.

The Genre field is not used in any ControlSource. There is a combobox that uses Genre in the RowSource.

Even though Genre is displayed to the user in a combox, no form can be sorted by a combobox column unless it is also the ControlSource, which Genre is not. The ControlSource is GenreID, but sorting by this number would have no meaning for the user.

Therefore, the reason for including the Genres tabe in the form RecordSource is to enable the user to include Genre in a meaningful sort.

Combobox RowSource

The RowSource for a combobox does not come from the RecordSource for the form (or report). Comboboxes are a great way to show data from related tables.

in our example:

The GenreID combobox stores GenreID in the Hits table but displays Genre from the Genres table.

In order to illustrate how Sort123 can be used on the column header label to sort by text stored in another table that correlates to the numeric foreign key stored in the main table of the form RecordSource, the related table (Genres) is added to the form RecordSource (based on Hits) using an outer join.
aah, an uncomfortably long sentence -- I welcome your editing suggestions for a better way to say this...

It is interesting to note that, even though the Genres table is used in the form RecordSource, no fields from it are used on the form.

Combobox ControlSource

The ControlSource for a combobox is often a field in the form RecordSource that is a numeric foreign key.

in our example:

The ControlSource is GenreID in the Hits table, which is specified in Hits.*

Other combobox properties that are set are listed in the GenreID Combobox section.
Go To Top Go To Quick Jump

LEFT JOIN

Notice that Hits is related to Genres with a LEFT JOIN ... this means that all records from the first table specified, Hits, will be displayed even if there is not a corresponding GenreID in the Genres table.

If the Join type is not specifically changed from the default INNER JOIN, then only records in Hits with a matching GenreID in Genres would be displayed. This would not suit our purposes at all — we would hardly see any records! The genre data will be filled out ... sometime... but meanwhile, it is important to see where it is missing as well as other information that is there.

GenreID Combobox

The Combobox to collect GenreID and display Genre has these properties:

		ControlSource —> GenreID
		
		RowSource —>

		SELECT Genres.GenreID
		, Genres.Genre 
		FROM Genres; 

		ColumnCount —> 2
		
		ColumnWidths —> 0;3 in
		
		ListWidth —> 3.2 in (sum of column widths + 0.2" for scroll bar)

Go To Top Go To Quick Jump Go To Combobox ControlSource

Calculated Field in RecordSource

You will notice there is a calculated field in the RecordSource called FirstDateMoYr

   Format([FirstDate],"mmm") & Chr(13) & Chr(10) & Format([FirstDate],"yyyy") AS FirstDateMoYr

This displays the First Date that a hit made number one on the charts as

Month Abbreviation
```
Format([FirstDate],"mmm")
```
then new line
```
Chr(13) & Chr(10)
```
then the 4-digit year
```
Format([FirstDate],"yyyy")
```

The Format property could not be used alone on FirstDate in this case because there is no way to specify a new line instruction (I thought there might be a way to specify ASCII codes — but didn't see it so if any of you know a way, please tell me!).

Chr(13) = Carriage Return
Chr(10) = Line Feed

Go To Top Go To Quick Jump

Sort123 Parameters

Declaration Statement

Look at the declaration statement for the Sort123 procedure:


		Function Sort123( _
		   pF As Form _
		   , pField1 As String _
		   , Optional pField2 = "" _
		   , Optional pField3 = "" _
		   ) As Byte

The first parameter is a form reference. pF

If you are in the code behind the form you want to sort, use Me
if you want to sort a subform contained on the form the code is behind, use Me.subform_controlname.form
if you want to sort another open form, use forms!Formname
for a subform on another open form, use forms!Formname.subform_controlname.form

The second parameter, pField1, is the (first) field name to sort by. If the field name is an empty string, the form sort is removed (OrderByOn = False). The field name, if specified, must be in the form RecordSource.

You may send up to 2 more field names for secondary and tertiary sorts. pField2 and pField3 are optional.

Line Continuation

The space underscore _ at the end of lines means that the statement is continued on the next line. Use Line Continuation to keep lines short and enhance readability of the code.

Return Value

The return value for the function is specified as Byte. This is done because no return value is ever used and byte is the smallest data type. This procedure could, therefore, be defined as a Sub instead of a Function. I initially wrote this as a function so it can be specified directly on the property sheet on the Event tab.

Now I normally limit my property sheet references to simple functions without parameters since the property sheet is not compiled (thanks, Gord, for hammering this point home <smile>)

In the future, I may change the return data type to boolean and send back True if anything was changed in the OrderBy or OrderByOn properties. Any dirty record should also be saved, which is not currently being done. If I update the code, it will be posted to this page.

Passed Field Names

Here is a diagram showing the column headers and which fields are passed to the Sort123 procedure.

first Diagram showing fieldname parameters

second Diagram showing fieldname parameters

Since there are no duplicate song names in this list, the Song column is just sorted by the Song Name.

If you sort by the number of days a hit held number 1 on the charts (nDays), there are multiple records for each value so more fields are specified in the sort: Number of Days, Genre, and Song name.

Go To Top Go To Quick Jump

Sort123 Code

Here is the Sort123 code that should be put into a general (standard) module:



'~~~~~~~~~~~~~~~~~~~~~~~~~~ Sort123 
Function Sort123( _
   pF As Form _
   , pField1 As String _
   , Optional pField2 = "" _
   , Optional pField3 = "" _
   ) As Byte 
'091203

   'written by Crystal
   'strive4peace2009 at yahoo dot com

   'sort form by specified field(s)
   'sending the same sort fields
   'toggles Ascending and Descending order

   ' --------------------------------------------------------
   ' PARAMETERS
   '  pF = form reference
   '       if in code behind a form, this is
   '                   Me
   '  pField1 -- name of field for first sort
   '  pField2 -- optional, name of field for second sort
   '  pField3 -- optional, name of field for third sort
   '
   ' --------------------------------------------------------
   ' NOTES
   '  you must specify FIELD names in the RecordSource
   '  control names do not matter
   ' --------------------------------------------------------
   '
   'USEAGE
   ' commonly called on
   '     CLICK event of column header label control
   '
   ' in code behind form to specify main and secondary sort fields
   '    Sort123 Me, "Fieldname1", "Fieldname2"
   
   'set up Error Handler 
   On Error GoTo Proc_Err
   
   'dimension sort string variables 
   ' for both ascending and descending cases 
   Dim mOrderBy As String _
	  , mOrderByZA As String   
  
   '  assign the first field to the OrderBy strings 
   If Len(Trim(pField1)) > 0 Then
	  mOrderBy = pField1
	  mOrderByZA = pField1 & " desc" 	

  
	  '  assign the second field to the OrderBy strings
	  '  if it is specified 
	  If Len(Trim(pField2)) > 0 Then
		  mOrderBy = (mOrderBy + ", ") & pField2
		  mOrderByZA = (mOrderByZA + ", ") & pField2
	  End If 
  
	  '  assign the third field to the OrderBy strings
	  '  if it is specified 
	  If Len(Trim(pField3)) > 0 Then
		 mOrderBy = (mOrderBy + ", ") & pField3
		 mOrderByZA = (mOrderByZA + ", ") & pField3
	  End If

   Else   
	  ' no sort string specified
	  ' remove OrderBy from the form 

	  pF.OrderByOn = False   
	  ' exit the procedure 
	  GoTo Proc_Exit
   End If   
   
   ' use WITH to minimize the number of times
   ' this code will access the form 
   
   With pF 
	  ' if the form is already sorted
	  ' by the ascending sort string,
	  ' then change order to be descending 

	  If .OrderBy = mOrderBy Then
		 .OrderBy = mOrderByZA
	  Else 
		 ' change the sort order to ascending
		 ' if form is not sorted this way 
		 If .OrderBy <> mOrderBy Then
			.OrderBy = mOrderBy
		 End If
	  End If 
	  ' make the form use the specified sort order 
	  .OrderByOn = True
   End With
   
Proc_Exit:
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
		"ERROR " & Err.Number _
		& "   Sort123"
 
   Resume Proc_Exit


   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement 
   Resume
   
End Function

Go To Top Go To Quick Jump

Standard (General) Module

To Create a Standard (General) Module:

When you are in Access, press ALT-F11 to go to a code window.
From the menu in a the Microsoft Visual Basic window, choose:
Insert —> Module This gives you a blank module sheet with any default compiler directives (Option statements) at the top.

Be sure to use the Option Explicit compiler directive at the top of each module so variables that are not declared or are misspelled will be caught when the code is compiled.
Option Explicit

Copy (CTRL-C) the code from this site and Paste (CTRL-V) into the module sheet below any compiler directives.
Once the code is in the module sheet, from the menu, choose —> Debug,Compile

If there are no syntax/reference errors, nothing will appear to happen — in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

Now that we have this code saved in a general module, it can be used anywhere in the database. On our form, we are going to call Sort123 in the Click [Event Procedure] for each column label.

Go To Top Go To Quick Jump

Code Behind Form

Here is the code behind the form to implement sorting on each column:

	Option Compare Database
	Option Explicit

	Private Sub Album_Label_Click()
	   Sort123 Me, "Album", "Artist"
	End Sub

	Private Sub Artist_echo_Label_Click()
	   Sort123 Me, "Artist", "Songname"
	End Sub

	Private Sub Artist_Label_Click()
	   Sort123 Me, "Artist", "Songname"
	End Sub

	Private Sub BPM_Label_Click()
	   Sort123 Me, "BPM", "Artist", "Songname"
	End Sub

	Private Sub Decade_Label_Click()
	   Sort123 Me, "Decade", "Songname"
	End Sub

	Private Sub Dur_Label_Click()
	   Sort123 Me, "Dur", "Songname"
	End Sub

	Private Sub FirstDate_Label_Click()
	   Sort123 Me, "FirstDate", "Songname"
	End Sub

	Private Sub GenreID_Label_Click()
	   Sort123 Me, "Genre", "BPM", "Songname"
	End Sub

	Private Sub HitID_Label_Click()
	   Sort123 Me, "HitID"
	End Sub

	Private Sub nDays_Label_Click()
	   Sort123 Me, "nDays", "Genre", "Songname"
	End Sub

	Private Sub RecordedBy_Label_Click()
	   Sort123 Me, "RecordedBy", "Artist"
	End Sub

	Private Sub Songname_echo_Label_Click()
	   Sort123 Me, "Songname"
	End Sub

	Private Sub Songname_Label_Click()
	   Sort123 Me, "Songname"
	End Sub

	Private Sub TheLabel_Label_Click()
	   Sort123 Me, "TheLabel", "Artist"
	End Sub

	Private Sub WrittenBy_Label_Click()
	   Sort123 Me, "WrittenBy", "Artist"
	End Sub

for more information on Code Behind a Form:
How to enter a live e-mail... -- Code Behind Form (CBF)
http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1438041&fpart=all#Post1438041

Go To Top Go To Quick Jump

Sort By Examples

...and now what you have all been waiting for ... to see how this works on our form.

click on the Song column header to sort by song name


	Private Sub Songname_Label_Click()
	   Sort123 Me, "Songname"
	End Sub

<<sort in ascending order by song name>>

and now click again to sort by song name in descending order:

<<sort in descending order by song name>>

Some of you probably noticed that more information is filled out for the songs at the top of the alphabet ... started at the top looking up other information I decided to track.

Go To Top Go To Quick Jump
click on the Artist column header to sort in ascending order by Artist and then Song

	Private Sub Artist_Label_Click()
	   Sort123 Me, "Artist", "Songname"
	End Sub

... and then again to sort in descending order:

<<sort in descending order by artist>>

Go To Top Go To Quick Jump
click on the First Date column header to sort in ascending order by First Date and then song


	Private Sub FirstDate_Label_Click()
	   Sort123 Me, "FirstDate", "Songname"
	End Sub

Missing Information: In this example, there is a song that never hit number 1 on the chart, so it does not have a FirstDate value. In this case, the information is not missing because it is not known. The information is missing because it is not relevant.

<<sort in ascending order by first date>>

<<sort in ascending order by first date>>

Go To Top Go To Quick Jump Click the FirstDate column label again to sort in descending order:

<<sort in descending order by first date>>

Since I have Beat Per Minute (BPM) information for more of the recent songs, the BPM column is included in the capture.

Go To Top Go To Quick Jump
click on the #Days column header to sort in ascending order by #Days, Genre, Song

	Private Sub nDays_Label_Click()
	   Sort123 Me, "nDays", "Genre", "Songname"
	End Sub

<<sort in ascending order by number of days at number one>>

... and then again to sort in descending order (these are the songs that stayed on the chart the longest in the number one spot):

<<sort in descending order by number of days at number one>>

<<sort in descending order by number of days at number one>>

According to the records, the songs around the turn of the 20th century appear to be the most popular of all time. This is actually a bit misleading as there were less songs being released that long ago. Well, there are a couple recent songs in this first screen of information, but lets look at the middle of this list:

<<sort in descending order by number of days at number one - middle of list>>

<<sort in descending order by number of days at number one - middle of list>>

Go To Top Go To Quick Jump
click on the Decade column header to sort in ascending order by Decade and then Song

	Private Sub Decade_Label_Click()
	   Sort123 Me, "Decade", "Songname"
	End Sub

... and then again to sort in descending order (these are the most recent songs):

<<sort in descending order by decade>>

... and a couple of shots from the middle of this sort:

<<sort in descending order by decade - mid 1>>

<<sort in descending order by decade - mid 2>>

Go To Top Go To Quick Jump
click on the BPM column header to sort in ascending order by BPM, Artist, Song

	Private Sub BPM_Label_Click()
	   Sort123 Me, "BPM", "Artist", "Songname"
	End Sub

As you can see, several of the records have this information missing. Values that need to be filled are at the top of the sort order. Right now, we are only interested in looking at records with values Filter for records where BPM is filled out. Right-click in the BPM column and choose —> Does Not Equal Blank (for Access 2007+ — in lower versions, use Not Null for filter criteria after right-clicking)

<<filter BPM for fields that have a value>>

<<filter BPM for fields that have a value>>

These are the slowest songs in the database that have beat information filled out:

<<sort in ascending order by filled BPM>>

and these are the fastest songs...

<<sort in descending order by decade>>

Go To Top Go To Quick Jump
click on the Genre column header to sort in ascending order by Genre, BPM, Song*

	Private Sub GenreID_Label_Click()
	   Sort123 Me, "Genre", "BPM", "Songname"
	End Sub

... and then again to sort in descending order

<<sort in descending order by Genre>

NOTE: Because we do not want to sort by GenreID, we pulled the Genres table into the RecordSource of this form so we could have access to the corresponding text value stored in Genre. The Genre field from Genres is not on the form anywhere but because Genre is in the Recordsource, we can use it to sort.

Go To Top Go To Quick Jump
click on the Duration column header to sort in ascending order by Duration and then Song — these are the shortest songs*


	Private Sub Dur_Label_Click()
	   Sort123 Me, "Dur", "Songname"
	End Sub

... and then again to sort in descending order — these are the longest songs:

<<sort in descending order by Duration>

Go To Top Go To Quick Jump
click on the Album column header to sort in ascending order by Album and then Artist*


	Private Sub Album_Label_Click()
	   Sort123 Me, "Album", "Artist"
	End Sub

Go To Top Go To Quick Jump
click on the Written By column header to sort in ascending order by WrittenBy and then Artist*

	Private Sub WrittenBy_Label_Click()
	   Sort123 Me, "WrittenBy", "Artist"
	End Sub

Go To Top Go To Quick Jump
click on the Recorded By column header to sort in ascending order by RecordedBy and then Artist*

	Private Sub RecordedBy_Label_Click()
	   Sort123 Me, "RecordedBy", "Artist"
	End Sub

Go To Top Go To Quick Jump
click on the Label column header to sort in ascending order by Record Label and then Artist*

	Private Sub TheLabel_Label_Click()
	   Sort123 Me, "TheLabel", "Artist"
	End Sub

The song and Artist columns are repeated since the record is so wide. The labels over these columns work just as the first 2 columns.

Go To Top Go To Quick Jump
click on the HitID column header to sort in ascending order by the Primary Key

	Private Sub HitID_Label_Click()
	   Sort123 Me, "HitID"
	End Sub

* only records with data in this field are shown Go To Top Go To Quick Jump

See how easy it is to add custom sorting to a continuous form? Once you get the code for Sort123 into your database, you can let it do most the work :)

Indicate Sorting with Italics

While it is true that Access gives the user ability to sort using the ribbon and shortcuts, some applications have these features disabled and many users do not know Access well enough to do this. When I design continuous forms, if the column can be sorted, then I put the label in italics. Note that the "Number 1 Hit" is not in talics — that is because nothing happens if you click on it. Putting labels that have code in italics is one way you can provide visual indicators to your users.

<<sort in ascending order by HitID>>

Download Database

DOWNLOAD a Sample Database for Sort123
MainMenu_Sort123_BoldMe_Crystal_091207__MDB.zip

contents:

MainMenu_Sort123_BoldMe_Crystal_091207.mdb
MDB File in Access XP (2000) format
The first form you see is a main menu. Choose to see the example form for Sort123 or BoldMe (another whistle).

strive4peace

Sort123

Sort by any column(s) in a Continuous Form by clicking the Column Header

Data Example: a couple hundred songs that have hit #1 on the charts from the 1890s to now.

Sort123

Quick Jump To Sections

Data Structure of Example

Form RecordSource

Combobox RowSource

Combobox ControlSource

LEFT JOIN

GenreID Combobox

Calculated Field in RecordSource

Sort123 Parameters

Declaration Statement

Line Continuation

Return Value

Passed Field Names

Sort123 Code

Standard (General) Module

Code Behind Form

Sort By Examples

Indicate Sorting with Italics

Download Database

Comments

Free Book on Access: Access Basics

Free Tutorials: Video Tutorials