|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Turn Off Filter By Richard Rost 
4 years ago
Turn Off Filter & Stay On Current Record In today's video, I'm going to show you how to turn off a form filter, but remain on the current record. This is handy if you want to be able to browse through all of the records, but not lose your place when you turn off the filter. Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Turn Off Filter but stay on current record, filter, filteron, Remove Filter and Stay at current Record, Remove filter from form but stay on current record, Microsoft Access Turn off Filter and stay on record, GoToControl, FindRecord, find record, go to control
IntroIn this video, I will show you how to temporarily turn off a filter on a form in Microsoft Access while staying on the current record, instead of jumping back to the first record in the set. We will cover how to use a little bit of VBA code to store the current record's ID, turn the filter off, and then automatically return to that same record. This technique is useful if you want to quickly remove a filter but remain focused on the record you were viewing.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, I'm going to show you how to turn off the filter on a form, but stay on that record. I have been asked this question a couple of times over the years by a few different people. Let's say you have a list form and you open up one customer from that list. It opens up with a filter and a where condition. You can see you have one of one here. If you want to turn off that filter but stay on the current record, it turns the filter off. It goes back to the beginning of the record set, which is record one, which is me. I open up Jean-Luc and I turn off the filter, and it goes back to me again. What if you want a button here that turns the filter off so you can still scroll through people but stay on the current record? That is handy. For example, let's say you want to come over here and manually put a filter on. Say I want a filter for the people in Florida. Right click, equals Florida. Now I have me and there is Deanna. I am going to turn the filter off and still stay on that record. Same problem. I am back to one of six now. We can do this with a couple of lines of VBA code. If you have never done any VBA programming before, go watch this video first. I will put a link down below in the links section in the description below the video. I have a nice little intro to VBA video that will teach you the basics. VBA is not scary; anyone can learn it and you will really take Microsoft Access to the next level if you learn just a little bit of VBA. So watch this first and come back. How do we do it? Let's open up the customer form here and go to design view. I am going to put a button right about there. I am going to copy one of these existing buttons, copy and paste. There we go. Control-C, Control-V. I will put a button right there and we will call this "Turn Off Filter, Stay." Now let's right click and go to build event right there. The key here is that we are going to turn off the filter and the command for that is Me.FilterOn = False. That turns the filter off. The FilterOn property tells you whether the filter is on or off and you set that to True or False. But before we do that, we have to remember what customer we are on. So we are going to take the CustomerID and store that in a variable. So Dim ID As Long--any ID, whatever field you want. If it is orders, use OrderID. If it is products, you can use ProductID. We are going to say ID = CustomerID. CustomerID is a field on the customer form. So we are going to say our ID value is equal to that CustomerID field on the form. Now we have turned the filter off, so we have all the records back in the form. Now we just have to find that record, but to find something, you have to be on that field. So DoCmd.GoToControl "CustomerID". Now the focus is sitting on the CustomerID. That is it. Like if we took our mouse and clicked on CustomerID or we tabbed over to it. So the blinking cursor is sitting on CustomerID. Now we just say DoCmd.FindRecord ID. We do not need all the rest of those options as they are optional. We do not need any of them. That is all we need. Clear the variable, set the ID equal to whatever the current CustomerID is, turn the filter off, go to the CustomerID field, and do the search. Save it and close it. I am going to close this form and let's open up Malcolm Reynolds and then turn the filter off. As you can see, it happened real fast there. Turn the filter off. We went back to record one, but then we issued the find command. Find will stay on that same set of records but just move through the records until it finds that ID. Try it again: close this, go to Deanna, turn the filter off, and jump right back to it. This database, by the way, is my TechHelp free template. It is a free download off my website, so go grab a copy if you want to. If you liked this lesson and you enjoy programming in VBA, check out my full Microsoft Access Developer Level 1 course. There is a link right there. I will also put it down below in the link section below the video. I cover this and lots more stuff. I have a whole series of developer lessons. I have dozens--I think I am up to 35 now. So there is lots and lots of developer stuff on my website if you want to learn more programming in Microsoft Access. Thanks for watching. This Fast Tip was brought to you by AccessLearningZone.com. Again, my name is Richard Rost, and we'll see you again next time. QuizQ1. What problem is the video addressing regarding turning off filters on a Microsoft Access form?A. It resets the current record to the first record in the set B. It deletes the filtered record C. It closes the form automatically D. It hides all records in the form Q2. When you turn off a filter in Access, what typically happens by default? A. You stay on the same record B. The form closes C. You are taken back to the first record D. All records are deleted Q3. What language is used to write the solution for staying on the current record after removing a filter? A. Python B. VBA C. JavaScript D. SQL Q4. Before turning off the filter, what does the tutorial recommend you do? A. Close the form B. Store the current record's ID in a variable C. Run a query D. Refresh the page Q5. Which property is used to turn the filter off in VBA? A. Me.FilterOff B. Me.RecordActive C. Me.FilterOn D. Me.FilterShow Q6. What VBA command is used to move the focus to the appropriate field before searching for the record? A. DoCmd.SetField B. Me.Focus C. DoCmd.GoToControl D. Me.FieldFocus Q7. What command is used in VBA to find and go to the saved record? A. DoCmd.SearchRecord B. Find.Record C. DoCmd.FindRecord D. Me.Find Q8. Which field's value should you save in the variable before removing the filter if you want to return to the same record? A. The form's Title B. The primary key (like CustomerID) C. The database name D. The filter condition Q9. After turning the filter off and finding the record, what should you do with the variable? A. Clear it or let it go out of scope B. Set it to zero C. Multiply by two D. Save it to a table Q10. Where can you download the TechHelp free template database mentioned in the video? A. From AccessLearningZone.com B. From the Microsoft Store C. From Google Drive D. From GitHub Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-C; 7-C; 8-B; 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. SummaryToday's video from Access Learning Zone covers how to turn off the filter on a form in Microsoft Access while remaining on the same record. Over the years, several students have asked me how to accomplish this without having Access automatically move to the first record in the recordset.Picture this scenario: you have a form that lists customers. When you open a specific customer from the list, Access opens the record using a filter and a Where condition, so only that customer appears on the form. The navigation bar shows "1 of 1." However, if you decide to remove the filter, Access reverts to the first record in the recordset, which may not be the one you were working with. Suppose you open Jean-Luc's record and then turn off the filter; Access takes you back to the first record instead of keeping you on Jean-Luc. Ideally, you might want a button that will remove the filter but ensures you remain on whichever customer you are currently viewing, allowing you to scroll through all the records from that position. This can also be helpful when you're using manual filters, such as filtering for all customers in Florida. After turning off the filter, you will want to stay on the same record you had selected. To accomplish this, you can use a few lines of VBA code. For anyone new to VBA programming, I recommend watching my introductory video first, which you can find in the links section beneath the video. VBA is quite approachable and mastering even the basics will allow you to get much more out of Microsoft Access. Let me explain how to implement this feature. Open your customer form in design view. Next, add a command button to your form. You can position this button wherever you like, and you might find it easiest to duplicate an existing button and rename it to something descriptive like "Turn Off Filter, Stay." For the button's event, you'll write a short sequence of actions. The main task is to turn the filter off by setting the FilterOn property to False. But, before doing that, you need to note which customer you are currently viewing. To do this, store the value of the CustomerID (or whichever unique field is relevant for your data) in a variable. This way, you have a reference to the same record after removing the filter. Once the filter is off, all records return to the form. Next, you instruct Access to move the focus to the CustomerID control. That sets up the next step where you ask Access to find the record matching the previously stored CustomerID value. This is equivalent to placing your cursor in the CustomerID box and searching for that specific value with a Find command. After saving and closing your changes, test the feature. Open the form, navigate to a particular customer such as Malcolm Reynolds, and use your new button to turn off the filter. Access may briefly snap back to the first record, but the Find operation will immediately bring you back to the original customer. Try it with another customer and see that it works the same way. If you would like to try out the database used in this lesson, you can download my free TechHelp template from my website. Those who are interested in learning more about VBA programming in Access should take a look at my full Microsoft Access Developer Level 1 course. You will find the link on my website, along with many other courses for mastering Access development. For a complete step-by-step video tutorial covering everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListTurning off a form filter while staying on the current recordAdding a button to a form in design view Copying and modifying an existing button Storing the current record's ID in a variable using VBA Turning off the filter with VBA (Me.FilterOn = False) Navigating to a specific control using DoCmd.GoToControl Finding a specific record with DoCmd.FindRecord Implementing the complete VBA code for the process |
||||||||||||||||||||
|
| |||
| Keywords: FastTips Access Turn Off Filter but stay on current record, filter, filteron, Remove Filter and Stay at current Record, Remove filter from form but stay on current record, Microsoft Access Turn off Filter and stay on record, GoToControl, FindRecord, find PermaLink Turn Off Filter in Microsoft Access |
