Order By, Filter On Load
By Richard Rost
5 years ago
Order By On Load, Filter On Load Properties
In this video, I'm going to show you how to use the Filter On Load and Order By On Load properties in your Microsoft Access forms. This will allow you to make the filters and sorting preferences you set in your forms stay persistent (or not, if you prefer) when you close and reopen your forms.
Rashad from Dubai, UAE (a Platinum Member) asks: I've noticed that when I put a custom sort on my forms, it stays there the next time I open the form. However when I apply a filter, it goes away. Is there any way to have the filter stay ON so the next time I open the form I don't have to re-apply it?
Members
I'll show you how to use the Me.Filter, Me.FilterOn, Me.OrderBy, and Me.OrderByOn properties to control those settings in your VBA code so you can specify what you want them to be when the form loads. Then we'll make some custom sort and filter combo boxes so you can prepare some popular choices for your users. With a couple of clicks they can easily apply sorting and filtering such as "order by last name and filter where first name starts with the letter R."
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!
 Links
Like Keyword: https://599cd.com/Like
Double Double Quotes: https://599cd.com/DoubleDouble
Search Seminar: https://599cd.com/SearchSeminar
Intro to VBA: https://599cd.com/IntroVBA
After Update: https://599cd.com/AfterUpdate
Intro In this video, we will talk about the Order By On Load and Filter On Load properties in Microsoft Access forms. You will learn how these settings control whether your sorts and filters remain active each time you open a form, and how to make filters persistent just like sorts. I will show you where to find these properties, how they work, and how to adjust them so your forms behave the way you want when opening and closing.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are going to talk about the Order By On Load and the Filter On Load properties. These will control whether your filter or your sort (Order By) are active or not active every time you open up a form.
If you want the filter or the sorting that you apply to your forms to be persistent, or if you want them to come back every time you open the form, you can use these properties to turn that on or off. I will show you how in this video.
Today's question comes from Rashad from Dubai, one of my Platinum members. Rashad asks, I have noticed that when I put a custom sort on my forms, it stays there the next time I open the form. However, when I apply a filter, it goes away. Is there any way to have the filters stay on so the next time I open the form, I do not have to reapply it?
This is a very good question, Rashad. Yes, we can definitely set it up so your filter stays consistent and persistent. The next time you open up the form, the filter that you placed last time stays on, just like the sort does. I will show you how you can turn the sort off so it does not stay persistent.
Let us take a look.
Here we are in my TechHelp free template. This is a free download from my website. If you want to go grab it, I will put a link down below. Let us take a look at the customer list.
Here is my customer list form. For those of you that do not know, you can put a custom sort or filter on this data. Let us say, for example, you want to sort by last name. Just click in the last name field and go sort A to Z. You can do the same thing with the first name: right click, sort A to Z. That will sort by whatever field you want.
You can also filter this data. Let us say you want to see only names that begin with the letter R. Right click on that field and go with Text Filters and then Begins With, then put the letter R in there and hit OK. There is your custom filter. You can control the filter by turning it on and off with that button.
I cover sorting and filtering in great detail in my Access Level 1 course. It is absolutely free. It is three hours long. I will put a link down below. Go watch it now if you do not know how to sort and filter your data.
Now that we all know how to sort and filter, once you put a sort on - let us say I have got this sorted by first name right now - and notice the IDs are out of sync. That is fine. Close the form and come back to it. They are still sorted by first name. That is because the sort, or let us call it the Order By, is persistent. It is set to stay on.
Now if I change it to sort by last name and then close the form and come back to it, that is still on. Whereas the filtering goes away.
If I filter - let me change two of these names to the same. Let us say that Deanna is now Deanna Riker. I filter this by Riker now, right click - it is just easier because it is right here - Riker, OK. If I close the form and come back to it, the filter is off. Why is that?
Let us take a look at the form's properties. Design View. The form's properties are right here, double click. Here is the filter that we set and here is the Order By or the sort. The filter is CustomerT.LastName = 'Riker.' The Order By is actually LastName, FirstName. That is sort of both of those.
Now take a look at this property: Filter On Load and Order By On Load. Filter On Load is set to No and Order By On Load is set to Yes. That is because when you set a filter or a sort (Order By property), that stays there. You do not even have to save the form. That is how Access works. It saves that setting for you automatically.
The difference is, by default, forms will have the Order By On Load property set to Yes. So it will keep the sort from time to time. When you close and open the form, it is programmed to automatically reorder based on your last sort. Filtering is by default turned off.
If you want to change that, just change this setting to Yes - Filter On Load. Now if I close this form, save changes, and reopen it, my filter is still on. See that? Close it and open it back up again. Even if you turn the filter off, close it, and come back into it, it still reapplies the filter.
Turning this button off does not erase the filter - it just turns the filter off - but that filter is still in there. If you take a look at the properties (Design View), see, it is still there.
The only way to get rid of it is to either come into properties and delete it - like that, I just deleted the filter and the sort - or I will show you the other way. See, now it is not there. Or if you filter, you can get rid of it by right clicking and then Clear Filter From Last Name. That is the other way you can erase the filter.
That should answer your question, Rashad. Design View, Properties, and right here you have Filter On Load, Order By On Load.
Likewise, if you want to turn that Order By off, you can. If you do not want your form to resort every time you open it, just set both of these to Off. Then you do not have to worry about it anymore. You have to reapply the filter and the sort every time you open the form.
That is it. It is that simple.
If you want to learn more, I have got a lot more in the Extended Cut for members. I show you how to manipulate this stuff using VBA programming. It is not scary. It is only a couple of lines of code.
Everyone hears VBA programming and they get all crazy and stressed out. It is really easy. I will show you step by step. There is a Me.Filter and a FilterOn property. They do the same thing that the form properties do, as you saw just a minute ago. Same thing with an Order By and OrderByOn properties. You can manipulate those, so when the form opens up, you can set what those things are.
If you want to have a custom sort or a custom filter every time the form opens, or you can make sure they are always off.
Then I will show you how to make those little custom combo boxes on the bottom -- these little guys right down here. You can do a sort, whatever sorts you want to have, custom sorts - first name, last name, last name first name, state, whatever you have in there.
Whatever fields you want to put in there that you think your users are going to use on a regular basis. Same thing with filter - last name contains, and I put 'Rik' in there, and then hit Go. First name starts with J. State equals, and then a drop-down box for state - whatever you want to do.
I show you those right there - first name, last name, contains or begins with. You can easily add 'ends with' if you want to, and then a little box for them to type in whatever they want, or another combo box if you want to put that there. This is all covered in the Extended Cut.
Once again, my Silver members and up get access to all of my previous Extended Cut videos right now. There are over 100. We just passed 100. There are 100 TechHelp videos. I think we are at 90-something with the Extended Cuts because a couple of them do not have Extended Cuts, but there is tons of material for Silver members and up. Very inexpensive, it is a great investment.
If you have any questions, please feel free to post them down below. Check out my Access Beginner Level 1 class. It is three hours of free training, and one of the lessons covers sorting and filtering.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available.
Silver members and up will get access to all of the Extended Cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is the main purpose of the "Order By On Load" and "Filter On Load" properties in Microsoft Access forms?
A. To determine if sorting and filtering are applied when the form opens
B. To control the form's background color
C. To change the form's record source
D. To adjust the font size in the form
Q2. What does "Order By On Load" set to Yes mean in a form property?
A. The form will open with records sorted by the last applied sort
B. The form will filter out blank records
C. The form will alternate background colors for each row
D. The form will prevent sorting by the user
Q3. What happens by default when you apply a filter to a form and then close and reopen it?
A. The filter is cleared and not reapplied
B. The filter is always reapplied
C. The filter is permanently deleted from the form
D. The filter changes to a different field
Q4. How can you make an applied filter persistent so it stays active each time you open the form?
A. Set "Filter On Load" to Yes in the form's properties
B. Save the form as a new object
C. Use the Print Preview option before closing
D. Set "Order By On Load" to No
Q5. How do you permanently remove a filter already set in the form's properties?
A. Delete the filter from the properties sheet or clear the filter using right-click
B. Save the form to a different folder
C. Set font color to transparent
D. Export the form to Excel and re-import it
Q6. If both "Order By On Load" and "Filter On Load" are set to No, what happens when you open the form?
A. Any applied sort or filter is not automatically reapplied
B. The form is read-only
C. The form opens in Design View
D. Data entry is disabled
Q7. Which property allows you to automatically apply the last custom sort when the form opens?
A. Order By On Load
B. Filter On Load
C. Record Source
D. Tab Order
Q8. What is the quickest way to apply a text filter for names starting with a specific letter in the form?
A. Right-click the field, use Text Filters, then Begins With
B. Export the list to Word and filter there
C. Change the sort order to descending
D. Add a new field to the table
Q9. What happens if you turn the filter off using the filter button, but do not delete the filter from properties?
A. The filter remains stored and can be reactivated
B. The filter is lost forever
C. The database is locked
D. The form switches to Layout View
Q10. In the Extended Cut for members, what additional topic is covered related to form filters and sorts?
A. Using VBA code to manipulate Filter and Order By properties
B. Importing data from Excel
C. Adding password protection to forms
D. Printing forms in landscape mode
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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 focuses on two important properties you can set on your Microsoft Access forms: Order By On Load and Filter On Load. These properties allow you to control whether the sort order or filter you apply to your form will remain active each time you open it.
If you have ever wanted your custom sorts or filters to be saved and automatically re-applied when you open your form, these are the options you will want to adjust. Today I will show you exactly how this works.
This question was inspired by a viewer who noticed that any custom sorting applied to a form in Access remains in place the next time the form is opened, but any filter gets removed. The question, then, is whether there is a way to make filters persistent, so that when you open the form again, the same filter stays in effect without having to set it up again.
The good news is yes, you can definitely make your filters persist, just like sorts. Let me walk through how all of this works.
First, in Access, it is easy to apply a custom sort or filter from within a form. For example, you can sort records by last name or by first name. You can also filter so that only records beginning with a specific letter are shown. Sorting and filtering are covered extensively in my Access Level 1 class, which is free and available on my website if you need more detailed training.
Once you understand how to sort and filter, you will notice an important difference. When you apply a sort (for instance, by first name), then close and reopen your form, the sort remains. Access keeps the most recent sort persistent by default. If you change the sort (perhaps to last name) and repeat the process, the form opens already sorted that way. However, when you apply a filter and then close and reopen the form, the filter is removed and you are back to viewing all records.
To learn why this happens, open the form in Design View and look at its property sheet. You will find two key properties: Filter and Order By. The Filter property will display the last filter you applied, such as "CustomerT.LastName = 'Riker'." The Order By property might say something like "LastName, FirstName" based on your sort.
Now focus on the two properties Filter On Load and Order By On Load. Order By On Load is set to Yes by default, which is why your sort becomes persistent automatically. Filter On Load, on the other hand, is set to No by default, so filters are not persistent unless you specifically change this option.
If you want your filters to remain in effect when you reopen the form, simply change Filter On Load to Yes. Now, when you save, close, and reopen the form, the filter is still applied. Even if you temporarily turn the filter off and close the form, it will automatically reactivate when you open it again unless you delete the filter from the property sheet or use Access's Clear Filter menu option.
If you do not want either the sorting or the filtering to be persistent, just set both Order By On Load and Filter On Load to No. That way, each time you open the form, it will display records without any sorts or filters, and you can apply them as needed for that session.
For those interested in advanced features, in today's Extended Cut for members, I explore how to manipulate these settings through VBA programming. This is not as complicated as it might sound, and only requires a few lines of code. I demonstrate how to control the form's filter and order properties in code so you can enforce a particular filter or sort every time the form opens, or ensure they are always cleared out.
I also show how to build custom combo boxes on your form for quick sorting and filtering options. This includes setting up controls for sorting by different fields, or providing filter options like "last name contains" or "first name starts with." You can set up custom drop-down lists for filtering by state, or add other controls as needed for your users.
Remember, if you want access to the Extended Cut and over one hundred other member videos, consider joining at the Silver level or higher. Members get access to additional materials, live video and chat sessions, and a range of helpful downloads and tools.
If you are new to Access, don't forget to check out the three-hour Access Beginner Level 1 course offered for free. It covers everything you need to get started, including sorting and filtering your data. If you enjoy it, the Level 2 course is just a dollar or free with any channel membership.
If you have a question you want answered in a future video, submit it on my TechHelp page.
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 Order By On Load property explained
Filter On Load property explained
Making sort order persistent on forms
Making filters persistent on forms
Applying a custom sort to a form
Applying a custom filter to a form
Reviewing current filter and sort in form properties
Saving and removing filters from forms
Saving and removing sort orders from forms
Effect of Filter On Load and Order By On Load settings
Resetting filters and sorts in form Design View |
