Click to Sort
By Richard Rost
4 years ago
Click to Sort by Column Header in Microsoft Access
In this video, I'm going to teach you how to create a one-click method to sort by a specific field (column) in your Microsoft Access continuous forms.
Bernie form St. John's, Newfoundland (a Platinum Member) asks: I know you can sort the records in a form by right-clicking on the field and selecting a sort option, but that's a pain. Is there an easier, faster way to just, say, click on the column header and sort by that column? My accounting software works this way and I would love it if Access did, too.
Members
Members will take this up a notch. We will make one click to sort ascending (A to Z) and a second click will sort descending. We will also change the color of the column header label to blue if it's sorted ascending, and red if descending.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Suggested Course
Links
Keywords
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, click to sort, How to sort in a form in Access, orderby, orderbyon, Click field header to sort Access, order by, order by on, sort by column, click on column header, on click event
Subscribe to Click to Sort
Get notifications when this page is updated
Intro In this video, I will show you how to add a "click to sort" feature to your Microsoft Access continuous forms, allowing users to sort data by simply clicking on column headers like First Name, Last Name, or State. We'll cover naming label controls, using the OnClick event, and writing a couple of simple VBA lines to change the Order By property so your form instantly sorts by the selected column. This tutorial is perfect if you're looking for a faster, easier way to sort your records without relying on right-click menus.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about click to sort. I'm going to show you how to make a click to sort column header in your Microsoft Access Continuous Forms. Do you want to sort by that column, like first name or last name? You just click on the column header.
Today's question comes from Bernie in St. John's, Newfoundland, one of my platinum members. Bernie says, I know you can sort the records in a form by right clicking on the field and selecting a sort option. But that's a pain. Is there an easier, faster way to just click on the column header and sort by that column? My accounting software works this way and I would love it if Access did too.
Well of course, Bernie, let me show you how to do it. But first, a couple of prerequisites. I always say if you learn just a tiny bit of VBA, you can make your database a lot more powerful. This is one of those situations. So if you have not yet watched my Intro to VBA video, go watch it. It's totally free, it's 20 minutes long, and it teaches you everything you need to know to get started programming in VBA. Do not be scared of it. All you need is a little tiny bit of VBA knowledge. In fact, this will work with, I think, two lines of code. But you have to know where to put those two lines of code, which is what I'm going to show you in this video.
But first, go watch Intro to VBA if you've never done any VBA programming before. And then after that, go watch my OnClick video. We're going to use something called the OnClick event, which means when you click on something, like the column header, something else happens. And that's called the OnClick event. So go watch that video too. That will give you a little bit of background before we get started today.
So go watch these two videos and then come on back. I'll wait for you. Go do it.
Okay. So here I am in my TechHelp free template. This is a free database. You can go grab a copy from my website if you want. You'll find a link down below in the description below the video, which is also where you'll find links to those videos that I just wanted you to watch.
In here, we have a continuous form, my customer list. Now I can sort by any of these fields. Remember, in Access, we call them fields. In Excel, they're called columns. You can still call them columns here if you really want to. But if I want to sort, let's say, by last name, it's at least a two-click process. You can click on this column and then right click and pick sort A to Z. Or you can click on a field like first name and hit the A to Z or Z to A up here, ascending or descending.
Ascending has nothing to do with the back of a donkey. Yes, I know bad joke. For those of you who watched my Access Beginner Level 1 class, my free four-hour class, I recycled that joke for everyone who did so. Ha-ha.
Anyways, what Bernie is saying is, would not it be nice? Would not it be easy if I could just click on this column header once and it just sorted that column? That would be pretty fancy, huh? Well, we can do that by putting an OnClick event in these labels that are back here, right? So let's go into design view.
Now, labels can have events in them as long as they're not bound to a field. If you look at a different form like this customer form here, these labels are actually bound to these text boxes. So if you come into design view and you click on one of these like first name, see how it's bound to the first name field? There are no events there. So you can't put events in a label that's bound to a text box.
But if you have an independent, free standing label like all of these are, they can have events. If you open up their properties by double clicking, you'll see the event tab. The first thing we're going to do is give our labels names. I normally don't bother naming labels because I almost never refer to them. But if you want to do something with or to that label, like change its color or have an event tied to it, then you want to give it a good name. You don't want to just call it label1.
So go to the All tab over here, pick Name, and make it, I like to make the name of the label the same as the name of the field that it's bound to. Well, or that it represents. These aren't bound. So, CustomerIDLabel, okay. This will be the FirstNameLabel, FirstNameLabel, and so on. LastNameLabel, and StateLabel. We'll just chop these off.
I'll just teach you how to do it with these four. That's plenty. I don't want you to have to sit here and watch me do the same thing for 10 different labels. So save that, close it, open it back up again.
Now, nothing appears to have happened. I just named the labels. Let's go ahead and put an OnClick event in those labels. Right click, design view.
Now, before we do that, I want to teach you about a certain property. Click on the form properties right there where the two ruler bars meet. You'll have the form properties over here.
If you go to Data, there's a field right here called Order By. There's some stuff in there right now. If you look, the order by, the default order by is CustomerT.FirstName, CustomerT.LastName. So the default sort for this form is first name, last name. Then there's Order By On Load, which means do you want to load that order by, that sorting? You want to load that when the form loads. Normally that's set to Yes. So whatever you save that sort as will get loaded the next time you load the form.
What we're going to do with our VB code is we're going to change this Order By property. So let's go to the CustomerIDLabel. Go to Event. Here's the OnClick event. There's also On Double Click, and don't worry about these. These are a little more specific, like when the mouse button goes down, the mouse button goes up, or the mouse moves over the label. There are all kinds of events in here. There are tons more for text boxes, but labels have fewer.
So we'll click on the CustomerIDLabel, and then over here hit the dot dot dot button. That will open up your Visual Basic window. Okay. I'm in the CustomerIDLabel_Click. If you get the little popup message saying what builder do you want, pick the code builder. I explained that in the Intro to VBA video that you should have watched.
Right in here, CustomerIDLabel_Click, this is the code that runs when you click on the Customer ID. Now we already have a CustomerID_DoubleClick event. I created this in the blank template video. If you want to watch the video where I actually build this database, that's my blank template video. I'll put a link to that down below. You can watch that too. In there I make this event where we open up the customer form of the currently selected customer. But if you just click it, this is the CustomerID field by the way, CustomerID, this is CustomerIDLabel. So, different things.
In here, this is where we're going to set that Order By property. I'm going to say Me.OrderBy. What is Me? Me is the current object you're working with, object being form, report, that kind of thing. Me represents the form that you're on.
Normally you don't need Me unless you're modifying a property of the form like its caption, its Order By, its filter, that kind of thing. You don't need to refer to Me if you're just talking about the fields on the form. You don't have to say Me.CustomerID. A lot of people do, but you don't need to.
Me.OrderBy, that's its Order By property, equals what field? CustomerID, that's the field we're on. Generally, that will work just fine. But there's one other line of code that I'd like you to put in here just to be safe. It's Me.OrderByOn = True. We're basically saying turn on the sorting. That setting can get turned off, and if you just change the Order By field name without making sure that Order By is on, then it might not do anything. This just ensures that sorting is on.
Let's do another one. Come back over here. Let's pick on the FirstNameLabel. Go to OnClick. Now notice we're in the FirstNameLabel_Click event. I'm just going to copy this code from up here. Copy (Ctrl+C) and paste it down here and just change the name to first name.
See how easy this is? It's not hard. Paste for LastName. LastName. And then state is the last. Click state, dot dot dot, and then state.
Save it. I'm going to close my form and then reopen it.
You ready? Here we go. First name. I think it's already pretty much sorted by first name. Last name, boom. Look at that. State, boom. ID, boom. There you go.
And that's how you sort. That's a one-click sort for each one of those columns. That's kind of cool. See, two lines of code is all you need, and really one usually just does it. That's most of what you need to learn when it comes to Access VBA. You don't have to become a super programmer developer hacker type guy. Just a couple lines of VBA code here and there can make your databases a whole lot more functional and a whole lot more powerful.
Do you want to learn more programming like this? I recommend my Access Developer Level 1 class. I start you from the ground up teaching all the basics of VBA, a little bit more detailed than the free one, but it's about an hour long. We go into all the different simple commands you need to learn to make Access powerful.
If you want to learn more in the extended cut for the members, we will make it so that you can sort ascending or descending. You click on the header. If it's currently sorted ascending, it'll sort descending and it will change the color. If it's ascending, it'll be blue. If it's descending, it'll be red. Here's the finished product. ID, first name, last name, back to first name. Click on first name again and now it's sorted descending. That's ascending, descending. State, ascending, descending. Pretty cool.
That is covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos. There are about 250 of them now, so you join and you watch as much as you want. There's tons and tons of extended cut videos to watch.
Gold members get access to download these databases that I build in the TechHelp videos, and you get my code vault of all kinds of cool, crazy VBA stuff in it for Access. So check it out.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
The Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the main feature being demonstrated in this video?
A. How to sort columns in a Microsoft Access report
B. How to create a click to sort column header in a Microsoft Access Continuous Form
C. How to add new fields to a table in Access
D. How to filter records using queries in Access
Q2. According to the video, what programming language knowledge is helpful for implementing the click to sort feature?
A. SQL
B. VBA
C. Python
D. JavaScript
Q3. What event is used to make the column header clickable for sorting?
A. OnDoubleClick event
B. OnChange event
C. OnClick event
D. OnLoad event
Q4. In Access Continuous Forms, what are column headers typically created with?
A. Text boxes bound to fields
B. Independent unbound labels
C. Combo boxes
D. Checkboxes
Q5. What must you do to a label before adding an event property to it for click to sort functionality?
A. Bind it to a text box
B. Delete the label and recreate it
C. Assign a unique name to the label
D. Set its default value
Q6. What property of the form is manipulated with VBA to change the sort order?
A. RecordSource
B. Filter
C. OrderBy
D. DataEntry
Q7. What does the 'Me' keyword represent in the VBA code discussed?
A. The current user logged in
B. The entire Access database
C. The current form or report object being worked on
D. The currently selected field
Q8. Why is it recommended to set both Me.OrderBy and Me.OrderByOn in the VBA code?
A. Me.OrderByOn is not required and can be omitted
B. To ensure that sorting is enabled and functions correctly
C. To filter out unwanted records
D. To reset the form completely
Q9. If a label is bound to a text box, can you add events to it like OnClick for sorting?
A. Yes, always
B. Only if the text box is visible
C. No, bound labels do not support events
D. Only if the form is in design view
Q10. In the extended cut version of the video, what additional features are added to the click to sort functionality?
A. Filtering based on field content
B. Ascending and descending sorting with color changes on the header
C. Printing the sorted data
D. Exporting data to Excel
Q11. What is one of the key advantages of learning a little bit of VBA according to the instructor?
A. It allows you to write SQL code directly in tables
B. It enables adding simple but powerful functionalities to your database
C. It prevents database corruption
D. It removes the need for queries in Access
Q12. What membership level is required to access all extended cut TechHelp videos?
A. Bronze
B. Silver
C. Guest
D. Viewer only
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-B; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone explains how you can create clickable column headers in your Microsoft Access Continuous Forms to make sorting easier and more intuitive. The goal is to allow users to simply click a column header, such as First Name or Last Name, to quickly sort the data by that column without needing to right click or use Access's built-in sort buttons.
The question for this lesson was inspired by Bernie, who wanted to streamline the sorting process to work more like typical accounting software, where sorting by a field is as simple as clicking its header.
To get started, it's important to know a bit about VBA (Visual Basic for Applications). If you have never done any programming in VBA, I recommend watching my free Intro to VBA video, which covers all the beginner essentials in about 20 minutes. You will also need to understand the OnClick event, which triggers some code to run when an object (like a label) is clicked. My OnClick video explains this in more detail, so be sure to check that out if you are new to these concepts.
For this demonstration, I'm using my free TechHelp template database, a straightforward example that anyone can download from my website. The continuous form we'll be working with shows my customer list. In Access, these are called fields, although many people also call them columns, as in Excel.
Normally, sorting records in a continuous form is a multi-step process. You might click in a field, right click to pick a sort option, or use the A to Z buttons on the ribbon. While these methods work, they are not as fast and user-friendly as simply clicking the header.
The solution involves adding an OnClick event to the labels in your column headers. First, ensure that you are working with standalone label controls, not labels that are attached to a text box. Only independent labels can have events attached to them. If your column headers are independent labels, you will see the Events tab in their properties.
To organize your work, it is a good practice to name the labels appropriately. For example, for the CustomerID column, name that label CustomerIDLabel. Repeat this for the other headers you want clickable, like FirstNameLabel, LastNameLabel, and so on. This makes it easier to identify them later if you want to add more functionality or change their appearance.
Once labeled, you can add the OnClick event. In the design view of your form, select a label and open the Events tab in its properties. In the OnClick event, you will add a very short VBA procedure. The key concept is to set the Order By property of the form to the field name you want to sort by, and then ensure that "Order By On" is set to true. These two lines of code are usually all you need to implement the sorting.
If you want to add sorting to other columns, just add similar click events to their labels and update the code with the proper field names.
After saving and reopening your form, you will see that clicking any labeled header now instantly sorts the data by that column. This method works for as many columns as you wish to enable with this feature.
With just a tiny bit of VBA, you can add significant functionality to your Access forms. You do not need to be a programming expert; sometimes two lines of VBA code are all it takes to move your database from basic to advanced.
If you are interested in learning more about programming in Access, I recommend taking my Access Developer Level 1 class, where you learn more about the power of VBA in an easy, practical way.
Also, in today's Extended Cut, we will take this a step further. I will show how to make the sorting toggle between ascending and descending order each time you click the column header. Additionally, the header color will change to indicate the current sorting direction - blue for ascending and red for descending. All of this is demonstrated with the final working version at the end of the extended lesson.
If you become a member at the Silver level or higher, you will get access to all of my extended cut TechHelp videos, which now number around 250. Gold members can download all the sample databases shown in these videos, and get access to my code vault, which is a collection of cool VBA code snippets and functions. You also receive priority support if you have questions of your own. Platinum members get even more, including access to all my beginner and developer courses for Access and other Microsoft Office topics, along with the highest TechHelp priority.
No matter your membership status, my free TechHelp videos will always be available as long as you keep enjoying them.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating click to sort column headers in Access continuous forms
Setting up OnClick events for label controls
Naming label controls to match field names
Understanding the Order By property in forms
Writing VBA to change the form's Order By property
Ensuring Order By On is enabled in VBA
Assigning unique OnClick handlers to each column header
Testing single-click sorting functionality in the form |
