Fitness 19
By Richard Rost
3 months ago
Open Related Record with Double Click Event In this Microsoft Access tutorial, I will show you how to enhance your fitness database by creating a double-click event to quickly open and focus on a related food record, handle filters effectively, and avoid duplicating code by utilizing a public sub to reset filter controls. You will also learn the difference between using Recordset.FindFirst and the RecordsetClone method for navigation between forms. This is part 19. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, fitness database, double-click event, open form to specific record, FoodListF, Recordset FindFirst, filter reset, SetFocus, IsLoaded function, filter off button, TurnOffFilters public sub, RecordsetClone Bookmark, referential integrity
Subscribe to Fitness 19
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today is part 19 of my fitness database series. If you haven't watched parts one through eighteen, go watch those first. As a reminder, if you don't care about fitness, that's fine because these techniques, tips, and tricks work in every database you could possibly build whether you're building a database for customers, orders, inventory, you name it.
All right, let's get back to it.
Like I said before, the more I use this database myself, the more I want to be able to say, "Hey, I want to add this feature." I just kind of come up with ideas off the top of my head. One thing I want to do is, while I'm working on my meals here, I might want to say, "Oh, I got to update this. I need a quick way to go to this mixed vegetables, one can." So I like to make it so you can double-click on a field and then it'll open it up over here, open the food list up. I don't want to have to come over here and have to find it, right? Mixed vegetables, oh, there it is. I want to just quickly be able to go "bam," and there it is.
I'm running out of room here, so I have to kind of do this a little bit. There is some other stuff I'm working on - don't pay any attention to the man behind the curtain. You didn't see that. I've got some other shortcuts for some other databases I'm working on.
We're going to make a little double-click event on this that'll open up this form and go right to that record. Now, I'm going to show you something similar to the RecordSet Clone/Bookmark thing we did before, but it's kind of a shortcut. It's a shortcut because it doesn't always work, but it should work fine in this situation.
Design view: now, this guy down here is my FoodID. Whenever I make something with a double-click event, for those of you who haven't seen this in my previous lessons, I like to make it so that if this field does something when you double-click on it, I change the color. I like to make them blue, so I pick this light blue right there. It just gives the user a little visual hint that, "Hey, if I double-click on this, it'll do something."
All right, what's that something going to be? The event: double-click event. Where are you? On double-click, right there. Open up my editor. Shrink my editor down so you can work with it with you guys. When I'm doing coding on my own, I make it really big.
So in here I want to open up the FoodListF, right? So DoCmd.OpenForm "FoodListF." We're going to assume it's not open first. We'll deal with it if it's open in just a minute.
Normally, I had to go through the whole setting up a recordset clone, setting the bookmark, find, file, that stuff. You can try this shortcut - it works most of the time, but it doesn't always work. That's why I showed you the proper way first. I don't like showing shortcuts first, even in my full course. I like to show, "This is the right, proper way to do it." Then I'll show you the shortcut, just keep in mind the shortcut doesn't always work.
All right, so here's the shortcut. You've got that form open; now we're going to go Forms!FoodListF.Recordset.FindFirst "FoodID = " & FoodCombo. I say this doesn't always work because the proper way is to set up a recordset, set the bookmark, all that stuff. This might not work sometimes, especially if you're doing it within the form itself. Then it almost always has problems, like if you're trying to do this search technique inside itself. But if you're doing it with a different form, especially one that you just opened, you're cool.
Now, if the user has already got the form open and they've applied a filter and they've applied a sort and the record can't be found, you might run into errors. But in this particular case, I know that I just opened the form, so I should be able to get away with this.
This also might not work in situations where you're trying to run this as part of an Open event - like a Form_Open event, OnCurrent event - when the recordset hasn't fully loaded yet, you might run into problems. But in this case, at this point here, you know the form is open, it's clean, it's fresh. This should work.
All right, let's test it. Save it, debug, compile. Come back over here, close it, open it, and double-click. Oh, yeah. Find the oil. See, it's working because the form's open. Let's say someone had filtered it. Let's say they filtered it on dairy, or you can use the little box up here. I built an extended cut. If I now try to find this mixed vegetables, nothing will happen because there's a filter on it. So we'll deal with that too.
First thing I want to do is let me close this and reopen it again. First thing I want to do is let's put the focus here because I'm looking at mixed vegetables here. I don't want to be seeing just dairy or whatever over here; I want it to be right on that item. That's a real easy fix to do. We've got it open, or assuming we found it, now I'm going to say Forms!FoodListF!FoodDescription. Remember, FoodDescription is the one up top, Description is the one down below. .SetFocus, OK? Let's see if that looks a little better. Let's go to the fish. There you go. See, now it looks exactly where we're at. OK, beautiful.
Now, again, the problem is if they've got it filtered already, now I try to find something and it isn't going to work. What I'm going to do now is - you could go through all kinds of extra programming, you could turn the filter off, get rid of the sort. What I'm going to do is, and the user won't even see this happen, I'm just going to see if the form is open. If it is, close it and reopen it. That'll just reset it to its default state. It's super simple to do and works nice and easy.
So I'm just going to come over here. I'm going to say if IsLoaded - IsLoaded is one of my functions, I'm pretty sure we covered it earlier - the problem is now we're in part 19. I don't remember what I've taught you. In my Developer course, I remember well.
Anyways, if that form is open - "FoodListF" - then we're just going to close it: DoCmd.Close acForm, "FoodListF", acSaveYes.
OK, save it, save it, close it, close it, open it, blah blah. All right. It's already open and it's already filtered. Let's go for "Naked Collagen Creamer" - boom, see, closed it, opened it, right there, cleared all the filters, put you right where you want it to be.
If that's too much of a - what's the word I'm looking for - if that's too much of a flash for you, you can do that echo trick I taught you before. But again, be very careful because if this at all locks up or causes any problems, you're going to lock your database up.
Or if you want, you can manually turn the filter off. That might be good enough for you too. Honestly, I think in this particular case, let me think about it, yeah, we could probably get away with that. Let's see if we do .NotActive, that's still a filter. Yeah, we could try just turning this - I'm going to do it, I'm going to leave this code in here for you. Let's see if we can just turn the filter off. So we'll say .FilterOn = False. That might work just as well, but keep in mind in the future if you add more stuff to this form.
OK, that works and causes less of a flashing. And if there is no filter, that works - it works perfectly.
All right, but if you add more stuff to this in the future that involves more than just a filter that might cause a problem. Also, for the members, we're not resetting these boxes up top too. So, members, what you could try doing is you've got: FoodGroupFilter, FoodDescriptionFilter, and IsActiveFilter. You could set those to their default values.
For example, IsActiveFilter. I'll just show you right here. We'll have to do this just so they don't look like they're weird. In this case, now that I'm going to be using Forms!FoodListF a lot, I'm going to say With Forms!FoodListF and then I'm going to put all of this stuff inside that With. I don't usually pull out the With unless I'm going to be using it a lot. End With.
Now, in here we can just say .FilterOn = False, .Recordset etc. It makes it a little harder to read sometimes, but it saves you a lot of typing. In here we can say, and I forgot the names of those fields already, we can say IsActiveFilter, right, IsActiveFilter = IsActiveFilter.DefaultValue. There it is. Yeah, we could do DefaultValue. Sometimes you can, sometimes you can't. Let's see, let's try it. Sometimes it only works with bound fields. Let's see, yeah.
You cannot find the reference Form_FoodList. What? Oh, anybody see the problem? It's not open yet. I tried to put the With outside the IsLoaded and the Open. It's not open yet.
All right, so for this we'd have to do this and then put it in here, then tab over on this and then End With, and then we'd need another one of them down here. Do the same thing. This is where you could also - sometimes what I'll do is I'll just define it as F and say Set F = Forms!FoodListF, and then you just say F.RecordSet, F.FoodDescription, that kind of stuff.
All right, try it again. New. OK, see, this is why you debug, compile, and this is why I should have run through all this before.
All right, open her up and there we go. Now, if I put this on to both and I run this again, I put it back. Oh, let's see. I remember that problem. This is why I love stuff like this that comes up that I haven't covered. The actual default value of this box, alright, is "Active" in quotes. Where is it? Default Value. See "Active" in quotes, which is really not what you're looking for.
So you can use it if you want to, probably better off just setting it instead of relying on this stuff up here. So I'll just change it here to "Active" like that and then just set the other ones manually. So what is this, FoodDescriptionFilter? Copy, and then we'll go like this, = Null. The other one is GroupFilter, and that's a combo box getting your data from that, so we'll make it = 0. What's your default? You don't have one? OK, 0 should work. Actually, we'll make it Null too, like that one. All right, so that will get rid of any filter that's on there, and then we'll also turn the filter off.
OK, save it, close it. We'll throw in a debug compile once in a while. Close that, close it, close it. All right, here we go. Rice. Oil.
All right, we're going to put this back on. When you make a design change, it'll save your settings too. Let's set this equal to fruits and we'll set this equal to not active, and then I'll try to find mixed vegetables - boom, there we go.
All right, so we turned off the filter, we reset these to their default values, members, and that's a Members feature. Sorry, members, you're going to have to once in a while watch a little bit of stuff for the members, but you see what I did there, right? These are the filter boxes.
Oh, you know what we could do? We can utilize this button. Yeah, all right, so this button, if you right-click on it, build it, and that turns the filters off - that does basically what we just did. So we can just call this filter-off-button stuff, but I don't want to have to call it. You could make this public, but I don't like doing that.So let's just make it turn off filters. And now we'll make this a public sub called TurnOffFilters like that. Now, since this is public, we can call it from outside the form: TurnOffFilters.
Let's go to where that was; we can close this. They've changed this. Yes, we can go back into here, design view, and back into the On Double Click event.
On Double Click, we don't need any of this really, because that button will turn the filters off. So we can just say right here: Forms!FoodListF.TurnOffFilters. So we're opening the form, turning off the filters. Perfect.
Debug, file, and that should be good. Let's test it.
Ready meal, let's go to rice one cup. Let's go to the specials. Let's go to fish. Let's turn these on. Let's turn this on. Try to find olive oil. Oh, that's so much better.
I had forgotten about that button there. You don't want to duplicate the code. You don't want this doing the same thing that this already does, and in the future if you add another filter thing in here - let's say you want to filter by calories or protein or whatever - you remember to add it to this one. You forget about that code that's in there. That's why we don't duplicate code.
So that's a pretty cool trick. That's a good little shortcut too. Just Recordset.FindFirst - generally, I only use that if I'm dealing with a different form that's already loaded. It loads it here.
Now that I'm looking at this, since we're just turning off the filters and not closing it, here we can make this an Else. Else Open it, and then set the Recordset, and then set the focus.
Let's see here. Yep.
But keep in mind this doesn't always work. Sometimes you have to use that RecordsetClone trick that I showed you last time. That one for sure works well, 99 percent of the time. This one works most of the time; most being more than half.
Now we're back to why I don't mess with the width most of the time. Let's say I've got a whole bunch, because now I'm back to not needing it. I've got just two of them down here, got one up here and two down there, and the width - I think the width actually makes it harder to read sometimes.
I think this is simpler to read. You can see what's going on here. If I have six, seven, eight items, then yeah, I'll pull that out, but now I can clearly see what's here. I don't know which is better.
That's it. Save it, close it. Close it.
Last time, I think I mentioned that we're going to deal with the problem of being able to add an item when there's no meal. We will definitely do that.
Today's Thursday the seventh. So we'll deal with that problem on Monday. Tomorrow's going to be Quick Queries Friday. Monday we'll get to part 20 and we'll start with this problem here. Promise. It has to deal with a referential integrity thing.
I don't want to call it a bug. I think it's a bug, but you have to know how to deal with this issue. We'll get to it on Monday.
That's it. That's a quick TechHelp video for today. I hope you learned something. Live long and prosper, my friends. Give me a like, follow, subscribe, all that stuff. I'll see you next time.
TOPICS:
Creating a double-click event to open a related form
Using DoCmd.OpenForm to open FoodListF
Implementing Recordset.FindFirst to locate a record
Changing field appearance for double-click hint
Setting focus to a control on the opened form
Handling issues with filters on the destination form
Programmatically removing filters using FilterOn
Resetting filter combo boxes to default values
Utilizing and calling a public sub to clear filters
Refactoring code to avoid duplication with TurnOffFilters
Managing code structure with With blocks in VBA
Conditionally closing and reopening a form if open
Validating field default values programmatically
COMMERCIAL:
In today's video, we're continuing with part 19 of our fitness database series. I will show you how to make it so you can double-click on a field to quickly open another form and go right to a specific record using a simple shortcut with Recordset.FindFirst, and we will discuss ways to handle possible issues with filters and sorts already set on the form. You will learn about giving users visual hints, updating focus after jumping to a record, and how to avoid duplicating code by calling filter-reset routines. If you work with forms that open records in Access, you won't want to miss this. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main feature added to the database in this lesson?
A. The ability to export food data to Excel
B. The ability to double-click a field to open the related record in another form
C. The option to delete records from the food list
D. The ability to email database reports directly
Q2. Why does Richard change the color of fields that have a double-click event?
A. To make the form more colorful
B. To provide a visual hint to users that double-clicking has a special action
C. To match the color scheme of the fitness database
D. To make the field easier to read in design view
Q3. What shortcut method is shown to move to a specific record in another open form?
A. Using DLookup to find the record
B. Using Me.GoToRecord in the current form
C. Using Forms!FormName.Recordset.FindFirst with criteria
D. Refreshing all data in the form with Requery
Q4. Why does the FindFirst shortcut sometimes fail?
A. Because it always requires the form to be closed
B. Because it works only with numeric fields
C. Because if there is an active filter or sort, the record might not be found
D. Because it cannot be used with subforms
Q5. What simple solution does Richard suggest if the FoodListF form is already open and filtered?
A. Manually change filter settings
B. Close and reopen the form to reset its state
C. Restart Access
D. Remove all records from the table
Q6. What does the .SetFocus method do in this context?
A. Sets the background color of the form
B. Moves the cursor to a specified control within the form
C. Saves the current record
D. Runs a specific query
Q7. What is the purpose of making filter-reset code into a public sub like TurnOffFilters?
A. To allow it to be reused from elsewhere without duplicating code
B. To improve the visual layout of the form
C. To automatically back up the database
D. To keep the code hidden from other users
Q8. What advantage does using a 'With' block provide in the context of manipulating the FoodListF form?
A. It makes the code easier to read no matter how many actions are performed
B. It saves typing when using multiple properties or methods on the same object
C. It allows private variables to be accessed outside the procedure
D. It automatically prevents errors when the form is not open
Q9. Why does Richard recommend against duplicating code for turning filters off in multiple places?
A. It increases the size of the database file excessively
B. It leads to inconsistency and forgetting to update all copies if something changes
C. It makes queries run slower
D. It is required by Access standards
Q10. In what scenario does Richard say the RecordsetClone/bookmark method is more reliable than the FindFirst shortcut?
A. When exporting records to a spreadsheet
B. When working within the same form or dealing with complex filtering
C. When displaying totals in a report
D. When updating table relationships
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-A; 8-B; 9-B; 10-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 TechHelp tutorial from Access Learning Zone continues my fitness database series with part 19. If you have not yet watched the previous videos in the series, it is a good idea to start at the beginning. Remember, even if fitness tracking is not your area of interest, the tips and techniques in this lesson apply to virtually any Microsoft Access database, whether you are working with customers, orders, inventory, or another type of data.
In my own use of this fitness database, I often come up with new ideas or features that would make data entry and management easier. One enhancement I wanted to add involves streamlining how I navigate from a meal entry to the corresponding food item in my Food List form. Instead of manually searching for the food record, I wanted a faster way to jump directly to it, perhaps by double-clicking on a field.
To enable this, I decided to set up a double-click event on the relevant field so that double-clicking would automatically open the Food List form at the correct record. This eliminates the need to manually browse or filter for the item each time.
Design-wise, I always try to give users a visual cue that a field offers this double-click feature. I change the field's background to a light blue color, which tells users that some action can be taken if they double-click.
Setting up the double-click behavior involves programming the event to open the Food List form and locate the record based on its FoodID. There is a quick way to do this using the FindFirst method on the form's recordset. While this shortcut works reliably when operating between different forms, it is not always dependable when searching within the same form or when filters are already applied. For these scenarios, I usually teach the more robust method that involves setting up a recordset clone and bookmarks, but in this particular case the shortcut is suitable.
Potential issues can occur if the Food List form is already open and filtered, in which case the record may not be found. To address this, my solution is to check whether the form is already open. If so, I close it and reopen it to reset all filters and sorts. This ensures the form is in its default state and ready for a direct search.
There is an alternative to closing and reopening the form. You can simply turn off the filter property. This avoids the visual flash that occurs when closing and reopening the form. Turning off the filter typically resolves the issue and is less disruptive, but you must remember to reset any filter controls or combo boxes on your form as well.
For Member-level users, additional refinements are possible, such as resetting specific filter controls like FoodGroupFilter or IsActiveFilter to their default values. To keep code organized and avoid duplication, especially if there are multiple ways a filter can be applied, you can centralize your filter-reset logic within a public procedure. Then, you can call this procedure from any place in your application that needs to clear filters, including the double-click event.
A key programming principle here is not to duplicate code. If you have a filter-clearing button, the logic behind it should be accessible from other procedures rather than rewriting the same code in multiple places. This not only keeps your code cleaner and easier to maintain, but ensures any new filter feature you add in the future only needs to be managed from one place.
It is important to note that using the shortcut search method generally works when jumping from one form to a freshly opened form. In other, more complex situations, you may still need to rely on the more robust recordset clone technique for reliability.
Looking ahead, in the next part of this series we will address a common issue that sometimes comes up in databases involving referential integrity -- specifically, how to handle situations where you want to add a related item when a necessary parent record does not exist. Be sure to join me for that lesson.
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 a double-click event to open a related form
Using DoCmd.OpenForm to open FoodListF
Implementing Recordset.FindFirst to locate a record
Changing field appearance for double-click hint
Setting focus to a control on the opened form
Handling issues with filters on the destination form
Programmatically removing filters using FilterOn
Resetting filter combo boxes to default values
Utilizing and calling a public sub to clear filters
Refactoring code to avoid duplication with TurnOffFilters
Managing code structure with With blocks in VBA
Conditionally closing and reopening a form if open
Validating field default values programmatically
|
