Fitness 5
By Richard Rost
10 months ago
Search Box Filter, LIKE Keyword, Timer Color Reset
In this Microsoft Access tutorial I will show you how to add a search box to your fitness database form, allowing you to filter food items by description using wildcard searches and the LIKE keyword, troubleshoot common mistakes with event procedures, and answer viewer questions about Access features and form design. This is part 5.
Members
There 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Building a Fitness Database, search box, filter box, food group filter, FoodDescriptionFilter, LIKE keyword, wildcard searches, After Update event, Before Update event, timer event, form timer interval, status box backcolor, RGB gray, ingredients database, Chef's Kitchen Helper, AI Chef's Helper, tracking macronutrients, log protein carbs fat, calorie deficit, maintain muscle mass
Subscribe to Fitness 5
Get notifications when this page is updated
Intro In this video, we continue with part five of the Building a Fitness Database series in Microsoft Access, where I'll show you how to add a search box to your form for easy filtering of food records using partial text and the LIKE keyword. You'll see how to copy and unbind a text box, write VBA code in the After Update event, use wildcard searches, work with double double quotes, and unlock controls for user input. We'll also talk about troubleshooting common design mistakes and discuss tips for nutrition tracking. This is part 5.Transcript Today is part five of my Building a Fitness Database series. And like I have always said in every one of the other parts, whether or not you care about fitness, I am covering lots of cool Access stuff today.
What we are going to do is we are going to add a little search box here. We have got a filter box, so we can drop this down and filter by the group, the food group - fruits or dairy or whatever. Now we are going to add a search box here. So if you are looking for everything that has got the word, let's say, cheese in it, type in the CHE, press Tab, and you are going to see all that showed on there too. All kinds of cool stuff today.
So whether or not you are building a fitness database or just a regular old whatever database, all the stuff I am showing in this series is going to be useful for you.
All right, here we go.
Here I am in my fitness database. If you have not been following along, go watch parts one through four, and then you will know where we are at.
Here we have got our filter box. You can drop this down and pick nuts. I do not have any nuts, do I? Pick protein. There is some protein in there - chicken breast. Pick fruits.
But eventually, this list is going to get big. And you might not know what group you put something in. You might be thinking that avocado is a fruit, so you filter for fruits, and now you cannot find it. Oh, it is under fats.
So we might want to be able to just search AVO over here and boom, there it goes.
Let us go into design view. I am going to move this button out of the way for a minute. I am going to copy this food description text box, copy, Control + C, and paste it up here. Then we are going to slide it over right next to this guy, like that.
I like to leave a little bit of a break in there just so you can visually see they are different.
Then we will put this guy right there. We will repurpose this button in a minute to clear both of those boxes.
Now we are going to use this as a filter box. Bring up its properties, go to All.
Now we are going to unbind it from Control Source here. We are going to just cut that out, paste it over the name, and add Filter on the end. So this is now the FoodDescriptionFilter, and it is unbound.
Save that. Let us go into its After Update. Dot, dot, dot. There we go. Let me bring this down here and resize it.
What we are going to do in here is basically the same thing we are going to do in there. So we are going to copy all this and paste it into here.
Same stuff, but we are going to check a different field. So if IsNull, we are looking at FoodDescriptionFilter this time. If that is the case, turn the filter off. If not, if there is something in there, then I am going to say the FoodDescription field has to contain what is inside the FoodDescriptionFilter.
We cannot use equal here. What do we use if we are looking for something that is a string inside of another string? We use our LIKE keyword. If you are not familiar with the LIKE keyword and wildcard searches, go watch this video.
So it is going to be LIKE. Now in here, we have to put inside of quotes an asterisk, and then whatever is inside that FoodGroupFilter. So it is going to be double, double quotes asterisk, close that string, and then at the end, it is going to be quote asterisk, double, double quotes, close that string.
I know that is confusing, but think of it like this. Here is what is going to happen. Here is what it is going to look like. It is going to come out there here. It is going to be LIKE and then, Avow for avocado, like that.
But we need the stars in here for LIKE to work, so it finds anything before the avo and anything after the avo.
Since these double quotes are inside of another string, they have to be double, double quotes - that one there and that one there, or like that. Then this whole thing goes inside of this whole thing. I know the double, double quotes are confusing. I do not like using single quotes though. You can also use single quotes, but they can cause problems, especially with names that have apostrophes in them - just ask my wife.
If you need more help understanding those double quotes and double, double quotes, go watch my concatenation video. Also, I have a whole separate video on why I prefer double, double quotes over a single quote, so go watch this too.
So FoodDescription LIKE a quote star. Then this has to also be FoodDescriptionFilter, not the FoodGroupFilter. So copy that and paste it there. So whatever is in the FoodDescriptionFilter, like avo, goes in there. Then we get FoodDescription LIKE blah.
Let us try it and see if it works. Save it. Debug Compile once in a while. Come back out here. Close it. Open it. Let us type something in here.
Wait, I cannot type. Why not? Why cannot I type?
Remember what we did last class. We locked all this stuff. We had to make the form allow edits, but we locked all of these so the user cannot change this stuff. So this guy, since we copied it from here, is still locked. We just have to unlock it.
There. Now save it. Close it. Open it. If I come over here and type in CH... and oh, what happened?
The function of it before. Oh, I see what I did. Let me hit Debug.
All right, so it is saying there is a problem with this line. That looks fine to me.
Let us hit Stop. Take a real quick look and see if anybody can figure out what my mistake was. Real easy mistake to make. I have done this before. Pause the video and see if you can figure it out.
My problem is I am accidentally in the Before Update event. Remember I said I was going to leave my mistakes in the video because if I make this mistake - and I have made this mistake a dozen times before - I guarantee you will as well. So what we all have to do is move this to the After Update.
You cannot change stuff like this in the Before Update because the data has not been committed to the table. So we are going to switch to the After Update event and just move all of this stuff up into here. Then just get rid of the Before Update event. Before Update is good for validating data, making sure that stuff is correct. But you cannot use it for things like this.
Let us try it again.
I am going to come over here, just put in AV, and boom. There is our avocado. Or CH for some cheese. Or where is my provolone? PR-OV? There is provolone.
Now that is all fine and dandy, but it does not work in conjunction with this guy. This guy will just overwrite it. And so will this guy. If I go back to PR, it will overwrite it again. So they are not working together. They are working independently. So we have to make them work together.
We will cover that in my next class. So tune in tomorrow, same bat time, same bat channel.
Members, you can watch it right now because I am going to keep recording tonight.
But do not leave yet because we are going to go over some of the questions that you have posted in the forums on my website and comments from YouTube. I am going to start doing this at the end of each class as long as we have got some questions from the previous couple classes. I just did Quick Queries yesterday and we got a bunch of them. So I am going to do them at the end of these videos.
If you care about the Q and A, stick around. If not, class is over. I will see you tomorrow.
First up, we have Renee. He said another great episode. It is part of keeping things tidy. I modified the food button code to open or close the associated form from the main menu. That is a great idea.
So what he is basically saying is in his food button click, he is checking to see if the food button .caption equals Food List, and if so, he opens the Food List and he sets the food button caption to Close Form. Otherwise, if it says Close Form, he then closes the form and sets it back to Food List.
That would work too. Just make sure that you disable their ability to close that form. Remove the X button - the close box on the form itself. Because if they close the form, then your code might generate an error message. You could use an IsFormLoaded type function to make sure it is open or just put in an error handler in here like On Error Resume Next in case the form is already closed. Then that will ignore that.
But that is a good idea.
Next up, Mitchell says always enjoy the videos. But for fitness tracking, you really just need your belt. If it is too loose or fits, you are doing fine. If it is too tight, you need to exercise more or give up and buy a bigger belt.
There is actually some truth to this because a lot of people just look at the scale and take that to mean whether or not they are actually losing weight. You might be losing weight or not losing weight, but your belt or basically how well your clothes fit is a much better indication of your overall health and fitness status than just looking at the scale. You could be losing fat and building muscle and then your clothes are going to fit better, because fat is less dense than muscle and so it will take up more space in your body. So you could lose five pounds of fat and gain five pounds of muscle. The scale does not move, but you are in a lot better shape. So yes, I kind of agree with that.
Next up, Edwin says, is it possible to set the backcolor of the status box back to gray after an amount of time? When I specify color in the status code, the backcolor will stay on that color. But I want it to return to gray after let us say 10 seconds. If so, what do I need to change in the code to make this happen? Good question.
Second, what if I would change the voice to another language? I cannot help you there. I do not know other languages, so I do not know. I know you can switch the voice - the speech synthesis - to a different language. I have never done it. So you might have to Google that one. If you do, post in the comments and maybe you can help someone else out.
As far as that backcolor thing, I have actually had it on my TechHelp video list for a while. I have had a TechHelp video list for a while now to include that - changing the color of the status box as a regular TechHelp video. Someone else asked me that exact same question in an email a few months ago. So I was going to put this in a video, but just for you guys who stuck around, I am going to show you how to do it right now. It is not that hard.
Now here, as you can see, when this one starts up, it loads the API key and it stays green. So you might want it to reset back to gray after a few seconds. What we are going to do first is in here, we are going to set up for the form an event - a timer event. But leave the Timer Interval set to zero so it is not always running, because you do not want to have too many timer events running in different forms. It can cause confusing and weird things to happen in your database. We are going to turn it on when we make a status change. Set the Timer Interval to whatever you want - three seconds, five seconds, whatever.And then when it is done, it will set that back to zero or back to gray and then make the timer interval zero again. So it basically shuts off.
So in the On Timer event, we are going to say in here: StatusBox.BackColor equals RGB and then whatever you want, like gray would be 200, 200, 200. All right, so like a light gray. And as soon as it does that, turn the timer off. So Me.TimerInterval equals zero. No sense to have it running because we are back to gray.
Now, in the actual Status function itself, which is in the global module right here, when all this is said and done, I am going to say if it is not being set to its default color, which I think those C's are basically the same as the 200's. If the back color is not the default color, so CCCCCC, that is gray, then we are going to set it back to that when we are done by invoking that timer interval. So we are going to say Forms!MainMenuF.TimerInterval equals however many seconds you want. Let's say five seconds. So 5000 milliseconds, five seconds.
So what is going to happen is if status gets called and it is anything other than the default gray, it is going to do its stuff and it is going to come down here and set the timer interval to five seconds on the main menu. That is going to activate, then five seconds later the main menu is going to run its event, set its color back to light gray, and then turn the timer interval off.
Save it. Debug, compile.
Now when I open up the main menu, it goes to green, give it five seconds, and it should turn right back to gray. That should work fine for pretty much anything, and you do not have a timer that is constantly running over here. The user should be able to do stuff even during that five seconds because it is a timer interval. Click. I can still click. It is not disabling anything at all from the rest of the database, and then that should just turn back off to gray. See how it worked.
That is pretty cool. And yeah, that was going to be a whole video. So you guys who just stuck around got some free bonus. T Coffee has been looking for the next step to help him grow so large you cannot fit through doorways. Beefcake. And if you know what that is from, then yeah, beefcake.
Cyberman says, very nice series you are making. Have a question. Could you make a connection between this system and the database you made with the list of all the ingredients you have and the recipes you can make with those? Sorry, but I cannot remember the name of that database. I think it would make a great addition to this database.
Well, I have got a couple of different videos. This first one is called The Chef's Kitchen Helper, where you put the ingredients in and then it will determine what meals are possible based on your available ingredients. This is all just done within the database.
And this one, flash forward a couple of years, this one is the AI Chef's Helper, which uses the same AI code that we just used in an earlier lesson here for the members to get the food macros, the protein, and the calories and all that. This one does the same thing, but it sends to the AI your list of what is in your pantry, and the AI will generate for you some recipes based on what you have got.
I do not think I am going to mix this into the fitness database because it is two different things. This is just, "Here is my raw ingredients, what can I make?" I mean, I suppose we could, but the fitness database really is not tracking what is in your pantry. So maybe I will leave it as a solid maybe.
But just go watch this one and build it, put them together. All the Legos are there, just build them yourself.
And finally, one of my viewers, Carrie, shared her own experience. She said she used to follow the old school diet advice, avoiding certain foods and just counting calories. But once she learned about tracking macronutrients - protein, carbs, fat - her results improved. Now she focuses on getting the right balance of macros instead of labeling foods as good or bad. She is excited that this database tracks not just calories, but macros too, which is exactly what I want to do.
I like to focus on tracking total calories and macronutrients, especially protein, because like you said, calories matter, but macros are key for staying healthy while losing fat. So in this database project, we will be logging foods with their grams of protein, carbs, fats, not just calories. That way you can see what your daily totals are and make sure you are hitting your macro goals.
So glad to have you following along. Personally, I like to be - right now, I am trying to lose fat - so I like to be in a calorie deficit. My BMR is about 2500 calories a day. That is how many calories I need just to survive. So I am trying to, most days, bring it down to about 2000, which is enough for me to not be totally hungry, but it is enough that I can lose weight over time.
It is also very important to me that my secondary goal is I do not want to lose a lot of muscle mass. So I am trying to get 200 grams of protein every day, and so that means a lot of tuna fish, a lot of chicken, lifting weights. It is difficult to lose weight and maintain that muscle. You do not want to lose your muscle mass. That is important.
I personally do not care as much about tracking fat and carbohydrates, but I do care about overall calories and protein. This database can be set up so you can track whatever you want. If you want to track cholesterol, or if you have high blood pressure and you want to track your sodium, that is great. Track whatever you want to track. I am just showing you how to put the Legos together, but I do appreciate your comment. Thank you very much.
So that is going to do it for part five, folks. If you have questions, post them either in the forums on my website or on my YouTube channel. I usually only go through the YouTube questions once a week before I am getting ready to do a Quick Queries Friday, but if I see them, I will grab them.
That is going to do it for part five. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part six, and we are going to get those two boxes to work together. Take care.
TOPICS:
Adding a search box to filter records in a form
Copying and repurposing a text box in design view
Unbinding a text box for use as a filter
Setting up a filter text box with a new control name
Writing VBA code in the After Update event
Using the LIKE keyword for wildcard string searches
Concatenating strings for filter criteria in VBA
Understanding and applying double double quotes in VBA
Unlocking form controls for user input
Difference between Before Update and After Update events
Troubleshooting event handler mistakes in Access forms
Testing and verifying filter functionality in Access forms
COMMERCIAL:
In today's video, we're continuing with part five of the Building a Fitness Database series, and you'll learn how to add a search box to your Access form so you can find foods by typing in part of their name, using the LIKE keyword and wildcards to make searching easy. I'll show you step by step how to set up the filter box, update your form design, and fix common mistakes like locking controls or using the wrong event. We also discuss how to use a timer event to change a status box color back after a few seconds, answer some viewer questions, and go over tips for tracking nutrition. 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 primary new feature added to the fitness database in this video?
A. A filter dropdown for food groups
B. An automated report generator
C. A search box for filtering food descriptions
D. A new table for exercise routines
Q2. Why was it necessary to create a search box in addition to the existing filter dropdown?
A. So users could find foods without knowing their food group
B. To allow users to add new foods directly from the form
C. Because the filter dropdown was broken
D. To track the history of searches in the database
Q3. In Microsoft Access, what property must be set to 'unbound' so that the new search box can function as a filter rather than display data directly from a table?
A. Format
B. Control Source
C. Default Value
D. Row Source
Q4. When constructing a filter to find records containing a substring in Access, which keyword is used in the criteria expression?
A. BETWEEN
B. EQUALS
C. LIKE
D. HAVING
Q5. Why is the asterisk (*) used with the LIKE keyword in the filter string?
A. It matches all records with empty fields
B. It allows partial matches before and after the search term
C. It sorts the results alphabetically
D. It indicates case-sensitive searching
Q6. Why are double, double quotes sometimes needed in Access VBA when building filter strings?
A. To handle fields with spaces in their names
B. To concatenate numeric values
C. To escape quote characters inside string literals
D. To force uppercase letters in search terms
Q7. What was the mistake made when testing the search functionality, and how was it fixed?
A. Control Source was not set; fixed by adding a control source
B. Code was placed in Before Update; fixed by moving it to After Update
C. The filter box was hidden; fixed by making it visible
D. Incorrect table was filtered; fixed by updating the table reference
Q8. Why can filters and updates that depend on the current form values not be reliably placed in the Before Update event?
A. The form data is not yet saved to the table
B. The Before Update event is not supported in Access
C. It runs too slowly for filters
D. It only validates numeric fields
Q9. If you want two filter/search boxes to combine their criteria to filter records together, what must you do?
A. Nothing, they will automatically work together
B. Implement code to combine both filter criteria into a single filter string
C. Place each box on a separate form
D. Add both boxes to the same tab control
Q10. In the Q and A, what did the viewer suggest to manage opening and closing a form from a button on the main menu?
A. Create a separate close form button
B. Change the button's caption and behavior depending on form state
C. Use macros only, not VBA
D. Only allow closing from the window X button
Q11. What should you do if you take over the closing of a form programmatically, to avoid errors if the user closes it in an unexpected way?
A. Disable the form's close box and use an IsFormLoaded function or error handler
B. Only use modal forms
C. Convert the form to a subform
D. Lock all fields in the form
Q12. Why is tracking macros like protein, carbs, and fat in addition to calories important for fitness databases?
A. Macros are required for database indexing
B. Only calories affect weight loss
C. Macro balance is key for health and body composition
D. Tracking macros disables other features
Q13. In the bonus section, how can you reset a status box's color to gray after a few seconds?
A. Use a hidden checkbox to track time
B. Create a timer event on the form that changes the color after an interval
C. Manually refresh the whole form
D. Reboot the database
Q14. What is the recommended way to trigger the timer event so it does not run continuously in Access?
A. Always keep the timer interval set to 1000
B. Only set Timer Interval to a nonzero value when you want the timer event to trigger, then set it back to 0
C. Use the On Load event to start the timer
D. Create a hidden form to manage all timers
Q15. According to the video, when users are dieting, why might the scale not reflect their progress even if clothes fit better?
A. Fat is heavier than muscle
B. The database is tracking data incorrectly
C. They are not entering their calorie data
D. Muscle is denser than fat, so exchanging fat for muscle may not change weight but changes body composition
Answers: 1-C; 2-A; 3-B; 4-C; 5-B; 6-C; 7-B; 8-A; 9-B; 10-B; 11-A; 12-C; 13-B; 14-B; 15-D
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 with part five of the Building a Fitness Database series. As I have mentioned in earlier parts, everything we're doing here is valuable not just for those interested in fitness. These Access techniques can be used in any database project.
In this lesson, I'm going to show you how to add a search box to your database form. Up until now, we already have a filter box that lets you choose a food group such as fruits, dairy, or protein and display just the items from that group. Now, we're taking things further by adding a search box where you can type in a few letters, like "CHE" for cheese, press Tab, and instantly see all matching records, regardless of group. This will be really handy when your list grows and you're not always sure which category an item falls under.
If you haven't completed the first four parts, I'd suggest reviewing those videos so you're up to speed.
Currently, with the filter box, you can drop it down and choose a group like protein to see items such as chicken breast, or fruits to see apples and so on. But what happens when the list gets long or you don't remember which group something is in? For example, you might think avocado is a fruit, so you filter for fruits, but it's actually under fats. That's where a search box comes in handy. You could type "AVO" and the form will show "avocado" no matter which group it's in.
To add this feature, I started by opening the form in design view. I moved the button that clears the filters out of the way temporarily and duplicated the existing FoodDescription text box, which holds the food names. After copying and pasting it near the filter box, I left a small space between the controls for visual clarity.
Next, I unbound the newly added text box by removing its Control Source property. I renamed it to FoodDescriptionFilter to make its purpose clear. This control is no longer tied directly to any data field.
The real filtering happens when you use the After Update event of this text box. I pointed this event to a new VBA routine. The code inside this event checks whether the FoodDescriptionFilter box is empty. If so, it turns the filter off and shows all records. If there's a value in the filter, such as "AVO" for avocado, it sets the form's filter so that the FoodDescription field contains whatever is in the search box.
To do this, it's important to use the LIKE keyword in your filter expression along with wildcard asterisks. This way, searching for "AVO" finds any food with those letters anywhere in its name. In Access, you need to be careful with quote marks and asterisks when building this filter string. I prefer using double quotes for concatenation, even though it can get a bit confusing. If you're unsure about concatenation or wildcard searches, I have separate tutorials that explain those topics in detail.
Once the filter string is set up and the logic is copied into the After Update event, it's time to test it. Sometimes you might hit a snag, and that's precisely what happened here. I couldn't type into the new search box at first. After a bit of troubleshooting, I remembered that earlier I set the form controls to locked so users couldn't edit the main data by accident. Since I duplicated another text box, the new one inherited this locked property. Simply unlocking it solved the problem.
Then, while testing the code, I ran into another common issue: having the filter code under the Before Update event instead of After Update. Filtering must happen after the user's entry is committed, so it's important to use the After Update event for this purpose. Once fixed, typing in a few letters like "AV" quickly filtered the list to show avocado. Typing "CH" found cheese, and so on.
One limitation right now is that the group filter and the new search box do not work together yet. Each will clear the other's filter when used separately. Making them work together will be the focus of the next lesson.
Before wrapping up, I spent some time responding to student questions from my website forums and YouTube comments, something I now do at the end of each tutorial if there are questions from earlier videos.
For example, one student explained how he modified the food button to open or close the associated form and toggle the button caption accordingly. That's a smart approach. Just remember to remove the close button on the form itself to avoid errors, or use an error handler to catch cases where the form might already be closed.
Another viewer suggested that the best way to track fitness is simply by how your clothes fit, not just by the scale. There's plenty of truth there, since losing fat and gaining muscle can keep your weight steady, even as your health improves.
One question asked about changing a status box's background color back to gray after a delay. The solution involves using a timer event on the form. Set the Timer Interval only when you want the color to reset, and turn it off again after it fires, so it's not constantly running in the background. That way, the color change is temporary without affecting form performance.
Another question related to integrating this system with a recipes database like the Chef's Kitchen Helper or AI Chef's Helper. While it's possible, I haven't mixed the two since the fitness database isn't designed to track pantry inventory. However, the techniques from my other databases can be combined if that's something you're interested in building.
Finally, one viewer shared her experience with tracking macronutrients instead of just calories, and how it improved her results. The system we're building here also focuses on logging protein, carbs, fat, and calories, so you can monitor daily totals and hit specific macro goals. Personally, my current aim is to stay in a calorie deficit while keeping protein intake high to preserve muscle mass. This database can help you track whatever nutritional stats matter to you, whether that's protein, fat, carbohydrates, sodium, or cholesterol.
That wraps up part five of our Building a Fitness Database series. If you have questions or feedback, you can reach me through the forums on my website or via my YouTube channel.
As always, you can find a complete video tutorial with step-by-step instructions on everything I covered here on my website at the link below.
Live long and prosper, my friends.Topic List Adding a search box to filter records in a form
Copying and repurposing a text box in design view
Unbinding a text box for use as a filter
Setting up a filter text box with a new control name
Writing VBA code in the After Update event
Using the LIKE keyword for wildcard string searches
Concatenating strings for filter criteria in VBA
Understanding and applying double double quotes in VBA
Unlocking form controls for user input
Difference between Before Update and After Update events
Troubleshooting event handler mistakes in Access forms
Testing and verifying filter functionality in Access forms |
