Fitness 35
By Richard Rost
5 months ago
Add Daily Totals to Form Footer, Fix Combo Box Defaults
In this tutorial I will show you how to improve the usability of our ongoing fitness database project by fixing several everyday annoyances I have discovered while using it, including adding daily totals for calories and protein, addressing issues with default values, improving navigation between input fields, and making sure new food entries are recorded under the correct date. This is part 35.
Members
In the extended cut, we will add sort and filter labels, create a filter box over the food item so you can easily filter and sort your data, discuss the option to display multiple dates in the food log, and I will show you my new improved bounce procedure for handling new records in the food list without screen flashing: Bounce.
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, fixing annoyances, calorie totals, protein totals, form footer totals, calculated values in query, continuous form totals, conditional formatting goals, system table goals, combo box input, Got Focus event, setfocus method, debugging VBA, add food item to log, meal planning date, filter labels, sort labels, bounce procedure
Subscribe to Fitness 35
Get notifications when this page is updated
Intro In this video, we continue building our Microsoft Access fitness database by tackling several common usability issues. We'll talk about adding daily calorie and protein totals to the food log form, setting up calculated fields in queries for proper summing, and displaying these totals in the form footer. I'll show you how to fix combo box default value problems, improve data entry by automatically moving focus to the right controls, and update your VBA code so new entries use the correct date, whether you're logging food for today or planning ahead. Debugging strategies for VBA are also discussed. This is part 35.Transcript Today is part 35 of my up to now 35-part series about fitness. Like I always say in every video, this is not just about fitness. These are cool tips, techniques, tricks, and stuff that you can do.
Oops, someone's beaming in.
In any database. So it does not matter if you care about fitness, weights, or nutrition. That does not matter. This is about building a cool database.
So, if you have not watched parts 1 through 34 yet, what are you waiting for? Go watch those and come on back.
Today's video is going to be about fixing a bunch of annoyances - things that I have noticed while using this database myself for a couple of weeks since we started building it. I have been using it. Not religiously; I have not been putting every day in it, but I have been using it most days. And I have come up with a short list of things that have annoyed me. So, let's fix some of those things first.
One thing I really need is the totals down here for the calories and protein every day, because I want to know how many calories I have left. I have had breakfast, I have had my lunch, and I got my snack that I have every night. It is the same thing: some apple or fruit and some yogurt. So how much do I have left for dinner? And how many grams of protein do I still need? That is one of the things we are going to fix today.
I have got a couple of things. There is some stuff I want to fix. But first, before we do that, there is something I need to add that I have been wanting to add for a while. I am using this myself. And yes, you can see I had breakfast today, and I went to Mellow Mushroom for lunch. They have a really good Cobb salad. My wife always gets the veggie pizza, so I always get a slice of it. So that is not bad. That is 770 calories for lunch. That is not too bad. I just adjust the rest of the day.
But what I am saying is I need totals down here. I was going to plan on doing this a little bit later, but I need to throw it in now because it is driving me nuts every day when I have been putting my food in here. And every day, I have to add this up in my head. So, time for some totals. We are going to go a little bit out of order and do that right now.
Yeah, there is other stuff we need to fix. This thing is stuck on canned salmon. We will get to that. I know. Relax.
Design view. Now, these are values that are calculated values. They come from total calories. Where does total calories come from? It comes from a query. I made sure that in the food log query, that is where we do the calculation.
This is something that throws beginners sometimes. I will admit, when I was a beginner this threw me too. If you want to have calculated values in the rows and the records of your continuous form, put the calculation in a query. Because if you do that calculation here, then you cannot total it up at the bottom of the form. You want to make sure you have this in the query because now that it exists in the query, the form can see all of them and it can add them up and so on.
All right, so we are going to slide all this stuff down just a bit. I think I talk about that in my form footer totals video. I will put a link to that down below.
I am just going to copy these two guys, copy, click, paste, and it worked. Yay. I am always happy when a copy-paste works. We will put it here. We are going to just open this up. In here, all we need to do now is call this, the name is going to be sum total calories. This is how I would do it: Calories. Now the control source is equals the sum of total calories. See how I do that?
The other one is going to be the same thing. Open this up. Total protein is going to be equals the sum of total protein. This is going to be sum total protein. Put whatever other things you want in here too - if you are tracking carbs, if you are tracking sodium, whatever you are tracking, put them in there.
If you want to make these different, I am going to eventually put conditional formatting on these. I want to make sure I am under my calories and over my protein or at least close to it. So we are going to have goals. We are going to make a system table where you can set what your goals are - your daily goals. Then we will have those change color based on that.
For now, I am just going to make this a simple green. Okay. Save it. Close it. Open it.
And oh, that is so much better. So much better. I thought about maybe putting them at the top, but no, totals go in footers. That is normal. That is the way. So now I can see yesterday, I was right on - those are my goals: 2000 calories, about 180 grams of protein. So today, now, I know what I have.
Now I can go over here and I know I do my fruit bowl and yogurt almost every night, same thing. It is 292. We are going to eventually have it down here in the list. So I could type in a dairy and put the stuff in. We will get to that. But my fruit bowl and yogurt is a staple. That will be a meal. So we will go over here. I know I could put this in here for later. And I forgot the numbers already.
One thing I thought about also is you cannot type in here, which is a little annoying, but most of this stuff is going to be fed in from the combo box. However, you still have the option to come down here and type in 290. And I forgot the number. 290 and 22. 290 and - ah.
All right. So this tells me now I have got about 500 calories for dinner, and I still need 80 grams of protein - 70 grams of protein. So I figured that one out. I will probably do a salad with some fish or something. That is something else.
But yesterday - by the way, that is what dinner was yesterday. I did three cans of tuna and then lettuce and some olives. By the way, do not put canned mushrooms in a salad. Fresh mushrooms, yes. Yesterday I tried to put canned mushrooms in. No, that was bad. Do not do that. Canned mushrooms are fine in cooked dishes, but gross in a salad.
OK.
Later on, this meal space is going to be - when we put a meal up here, it is going to feed that up here. So this will say breakfast, then this will say lunch, and then this will be night snack, whatever you put it in as. That gives me some room in here.
Now, you can enter stuff here later. My nighttime snack is usually around, say, 10 p.m. That way, I could put dinner in here. Say I am going to eat dinner at, let's say, 7 p.m. We will add some sort buttons later. If you just leave it and come back to it, it sorts. You have that nice big hole here, and you know how many calories and protein you have left. Maybe we will do that: put that down here too - your goal and then what is left - so you know, OK, I have got 510 calories left for dinner.
All right, now let us talk about this guy. Why is this stuck at fish - canned salmon, chicken of the sea? What happened here? This can happen if your VB code changes that control, like we are doing, and then you go to design view and then go back to form view, maybe, and test it. Then, if you save the form, your VBA code changed that control. Guess what? You make a design change and save it. That gets saved in your design.
So, come in here and take a look and go to Data. Then, this guy - Data and then control. Let us zoom in. Look at that. See, it is saved in there. The way to fix this is to set this every time you load the form, because these design changes will happen. You want to set that default when you load the form up.
Now, we already have code that sets this guy in the On Change event right here. It is update food combo control, and it sets it. If we do not send in anything, it just sets it back to default. So that is what we are going to do when the form loads.
All right, so drop this down. We are going to go to Form. Not before insert; we want either Load or Open. We already have a Load event. We will use that. Let us see if we have an Open event too. Nope, just Load event. Either one is fine for this.
All right, order by that, update the date filter, and then update the food combo control. That should set it back to blank when the thing opens. Ready, click, and then we are fixed.
It is those little things you do not discover sometimes until you start using the database. I have been using this every day. That is also kind of why I like spacing this out a bit where I am not doing four of these a week - I am doing maybe two or three fitness videos a week because that gives me a little time in the middle to use this. I have been using it over the weekend and have noticed a bunch of little things.
That is one of the problems I had when I was a consultant working for other companies. I would build their database, but I was not the guy using it every day. So I would sometimes hang around afterwards. I would obviously train the employees how to use it, but then I would sometimes sit back and just watch them for a few days, seeing what works, seeing what does not work. That is when you can really make the adjustments to make things better. And of course, I tell them always to take notes on what kind of features they like, what they do not like, and what they want to see streamlined. A database is a living, growing, breathing creature. It needs love. So it is constantly evolving.
While we are doing fixes today, the meal list - if you try to click up here and edit something, it properly jumps you down here. The food list does not do that. That is another one of my pet peeves. This guy does not do it. So, if I click here and I try to type, I am just annoyed because I cannot touch it. I want that to do the same thing - drop it down here and put the cursor at the end of it, just like we did with the meal list.
This one will be a little easier because we just have to, when any of these are clicked on, move the focus down here. Or, if this is clicked on, move the focus here; if this is clicked on, move the focus here, for example. The other one was in a subform, so we had to do some work with the bookmarks and find it and all that. So this is going to be much, much easier.
I would probably use the Got Focus event for all these. So, food group - we are going to go to food group combo. On Got Focus - where are you? On Got Focus. Food group combo.setfocus. You could drop it down if you want to. I do not think I am going to - nah, I am not going to drop it down.
Food description is going to go to description. So again, on focus, we are going to say description.setfocus. Here is one of those ones where I am going to say if description is not blank and not null, then we are going to say description. I cannot type description today. That cell start equals description, length and description. I will put this at the end of the word, basically.
Let us give that a quick test. We just show you what I mean here. Food list, click here; it puts you there at the end of the list.Put you at the end because I hate when you click on it and then it puts the focus down here and then you go, oops. I like to have it so when I click on something, it puts me at the end. Testing it for a new one, and that works fine. Because that's why you don't want to have to, if it's null, it could throw an error. And that's the zero main error.
Where is the series protein? Let's see here. Click, and then we'll have it go down to calories. I don't want to add a button with the whole thing because usually that's just something you type in again. If I'm going to change this, I'm not going to sit here and just pick that and type in three. I'm going to click on it and type in 253 again. I'm just building it the way that I work with small numbers. I tend to just type them back.
Calories 2.set focus and protein. Let me guess. I'm so smart, protein 2 got focus, protein 2.set focus. That is a calculation of your protein percent that should be in the query underneath. Yeah, so that should be great.
Save it, close it, open it, and that looks much better. If I click here, let's be down there. I click over here, let's be there. That's perfect. That's exactly what I want. Annoyance number two fixed.
Next annoyance is my annoyances video. In the food or in the food log, I'm going to go to tomorrow since I haven't put anything. Drop this down, pick something, add it. What happened? It went here because it's just adding it at the current time.
What we're going to do is if, let's say you're trying to plan tomorrow, you want to put it on tomorrow's date. Let me get rid of that. I didn't need that. If you want to plan tomorrow's meal, we're going to look at this up here. If the date up here is not today's date, then we're going to just put it in on this date at, let's say, midnight.
Let's go into our add code. We've got add food item to log definition. Now this is right here. Let me move this over so we can see the whole thing. Now right here, we're just setting it equal to now. But let's take a look and say in here, we'll say if the date value of right now is equal to the date value of that field on the form, we'll get it in a second. I forgot the name of it. If it's the same, if it's today, then do that. Otherwise, we're going to say set it equal to whatever x is. Let me get that field. I forget that field name.
This guy is log date. We'll just say in here log date. We're on the same form that's fine. We can just call it log date. Save it. Debug, compile once in a while. Close it. Close it. Close it. Open it.
If we're on today and I add a banana, there's the banana at 7:00 PM. That's perfect. Let's delete it. Now if I go to tomorrow's date and I'm prepping tomorrow, I can come in here and put in blueberries and hit add. And there they go. Oh, wait. Hold on. It went on today. It went to 7:00 PM on this date. Oh, wait a minute. This might have the default. Does this have an hour date? Let me see. I should not put a time in there. Let me see.
I had to step through it to figure out the problem, and I see it's a really easy problem. Let me show you what I did. When you get in trouble like that, when you can't figure out why it's doing that. It is putting tomorrow's date in there, otherwise it wouldn't be showing up on this form on this page. But it's got 7:10 in there still, which isn't too bad. But what's happening is it's actually keeping the format of the text for the time here.
Let me show you how I discovered what it's doing. We're going to walk through it. The button is what adds it. I put a breakpoint here on this spot. I haven't covered debugging too much in the TechHelp videos. I do spend a lot of time in my full developer course covering debugging.
What we're going to do now is this breakpoint will stop us right here. Go back here and run it. Go to tomorrow. I'm going to add that banana again. Now when I go, it's going to stop right there. You see how it stopped right here. You can hover over this stuff and make sure things look good. Food combo is three, that's fine.
Let's step into it. What step into means is it's going to go into this function or subroutine. Now I'm in that one, and now I can step through step down. Each one of these, it'll step to the next command. That one's good, that one's good, that one's good. I get down here to the date values.
Now is okay. That is now. What's the log date? The log date is tomorrow. That's fine. Step, step, that's good. Food date time has gotten log date and has got tomorrow's date in it. There it is. Food time text is format food log time of now. So I'm seeing the wrong value. It's putting the time in there still, but that's not what we need.
Now we know what the problem is. We're going to take this now and put that up in here. This will remain the same, but this will have to be put in here differently. I'll just do log date in here. It should throw a midnight in there. Tomorrow at midnight.
Save it, and we can get rid of that breakpoint. Now, where's the breakpoint? Go back in here. Sometimes you have to scroll back into your code to find all your breakpoints. There it is. Just click right there, turn it off. There's step into, step over, and step out of. Step out of says you're in a procedure. I finished this procedure. A sub or function is called a procedure collectively. Step out of says, we're done with this. Maybe it's like a status function or something, works fine. Step out means go back up a level to whatever called it and then go to the next line. Step into says step into this guy. Otherwise, it'll step over it. That's what step over is, the one I usually use.
Save it, debug, compile, already done that. Close it, close it. Now it should hopefully work.
Let's go to tomorrow and put in, here is some cereal. Perfect. That's coming in at midnight. That's exactly what I wanted to see. Those were my big monster annoyances so far.
I've got one more annoyance, but we'll talk about it in the next class. I also got some feedback, some stuff you posted on YouTube and on the website that we'll talk about in the next class as well.
If you want to learn more about debugging, I cover it in Access Developer 15 a lot more. Sorry, this is an older one. I don't have a lot of pretty pictures on it, but I do multi-select list boxes, which is another very popular topic, and debugging level two. There's a lot of good stuff in it, so check it out.
I'm going to delete this stuff since I haven't eaten it yet. We are going to eventually utilize this stuff because I've eaten this, I've eaten this, I've eaten this, but I haven't eaten this. We'll have these, what you've actually eaten, down here. We'll total that stuff up too.
Members, today we're going to add sort and filter labels, and we'll do a filter box over the food item. Right now it's just one day, but we might later on make it so you can display multiple dates in here. That's my goal eventually. You might want to be able to say, show me all the times I had a fruit bowl or whatever. Mostly for sorting purposes, you might want to sort your meal out, like we just did a minute ago where we were ready to go back and then forward again.
I'm going to share with you a new bounce procedure. What's that? Remember, in our food list, if we go to add a new item, we have it go up so many records and back down again in code so that it's not sitting there looking like that when you add a new record. I wrote a better procedure to handle that, and there's no flashing and you don't have to use echo and all that stuff. We'll talk about that in the extended cut too.
Silver members and up get access to all of my extended cut videos. We've got a couple hundred of them now. There is so much to watch. Gold members can download this database and all the stuff in the code vault, like my new bounce sub is going to be in the code vault and all kinds of cool stuff.
That's going to do it for part 35 folks. Tomorrow we've got quick queries, and have a good weekend after that. I will see you back on Monday with another fitness database lesson, lesson 36.
That's going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you on Monday. Well, I'll see you tomorrow for quick queries, and then if you're just following this, I'll see you on Monday.
Bye.
TOPICS:
Adding daily calorie and protein totals to the food log form
Creating calculated fields in a query for totals
Displaying sum of calories and protein in the form footer
Copying and modifying controls to show totals
Using the Sum function in control sources
Explaining placement of totals in form footers
Planning for conditional formatting based on nutrition goals
Explaining why calculations must be in the query not the form
Fixing a combo box default value stuck on a specific item
Using VBA to reset control defaults upon form load
Updating the food combo control in the form's Load event
Improving data entry usability with control focus changes
Using Got Focus events to move cursor to appropriate controls
Automatically placing the cursor at the end of the text in a field
Implementing navigation improvements in the food list form
Correcting date assignment for planned future food log entries
Updating VBA to use selected log date instead of current date
Debugging VBA code to identify and fix incorrect date/time assignment
Using breakpoints and step-through debugging in VBA
Deleting and managing planned food items in the log form
COMMERCIAL:
In today's video, we're continuing our fitness database series with part 35. We'll learn how to fix some of those everyday annoyances that pop up when actually using your database, like adding totals for calories and protein so you can easily track what you have left for the day. I'll show you why calculated fields should go in your query instead of the form for better footer totals, and we'll tidy up how combo boxes and textboxes behave so they're less frustrating. You'll also learn how to fix issues with stuck default values and how to make your food log entries line up with the right date and time, even when planning for tomorrow. Plus, I'll explain a bit about debugging in Access VBA and mention what is coming up for members in the extended cut, including new procedures for smoother record navigation and some cool label sorting and filtering options. 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. Why is it important to put calculation fields, such as total calories, in a query rather than directly in a form in Access?
A. The query allows Excel compatibility
B. Calculation in the query makes it possible to total values in the form footer
C. Queries are always faster than forms for calculations
D. Forms cannot display calculated values
Q2. What is the benefit of adding total calories and protein fields at the bottom of the daily entry form?
A. It shows what items were entered first
B. It allows tracking of inventory changes
C. It provides a quick summary of remaining nutrient goals for the day
D. It automatically generates meal plans
Q3. Why is it recommended to use the form's Load or Open event to set default values for controls like combo boxes?
A. Default values can only be set in the query
B. Changing defaults during design view does not affect how the form works
C. The Load/Open event ensures default values are consistently applied when the form is opened
D. These events are only triggered when saving records
Q4. When entering new food items into the log, how does the improved code decide which date and time to assign to the entry?
A. It always uses the current system time regardless of the form date
B. It uses today's date at 7 PM for all entries
C. It checks the displayed date and uses midnight if planning for a future day
D. It assigns the date based on the first entry of the day
Q5. What purpose does debugging (using breakpoints and stepping through code) serve in Access development?
A. It improves table relationships
B. It helps identify and correct logic errors in code execution
C. It increases the speed of database queries
D. It prevents users from editing records
Q6. What is suggested for handling focus (where the cursor is placed) when clicking on textboxes or combos in the food and meal lists?
A. Always set focus to the first text box
B. Use the Got Focus event to move the cursor to the desired position or field
C. Only allow editing in datasheet view
D. Disable focus changes for better performance
Q7. According to the video, why is it important to actually use the database yourself, or closely observe others using it?
A. To ensure documentation is complete
B. To find and fix usability issues that only become apparent during regular use
C. To create more tables and queries
D. To keep the database file size small
Q8. What does the creator suggest about the placement of total summary fields on forms?
A. They should always be at the top for visibility
B. Placement does not matter as long as they exist
C. It is standard practice to place totals in the form footer
D. They are best placed in a separate summary form
Q9. Why is it helpful to implement conditional formatting for summary fields like calorie and protein totals?
A. It decorates the form
B. It automatically updates food descriptions
C. It gives visual feedback on progress toward goals
D. It prevents data entry errors
Q10. What is a "bounce" procedure in the context described at the end of the video?
A. A way to remove duplicates from the meal list
B. A coding technique to refresh the record display cleanly after adding a new item
C. A method to copy meals between users
D. A special calculation for finding calorie deficits
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-C; 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 covers the ongoing process of refining and improving a fitness tracking database in Microsoft Access. While the series uses a fitness database as its foundation, the tips, tricks, and techniques I share here can be applied to virtually any kind of Access database. So even if you have no particular interest in fitness, I encourage you to stick around and focus on the database-building concepts.
If you have not already seen parts 1 through 34 of this series, I highly recommend going through those lessons first. Each one builds upon the previous examples, and the context will help you understand the improvements we are making today.
This session is all about resolving some of the annoyances and inconveniences I noticed while personally using the database over the past few weeks. Nothing compares to spending time as an actual end user for discovering where a database can fall short in productivity or ease of use. Even though I have not entered every meal every day, my practical use helped uncover several frustrating points that need to be adjusted.
The first major improvement I need is the introduction of totals for calories and protein at the bottom of the daily logs. As I enter my breakfast, lunch, and typical evening snack (usually fruit and yogurt), I want an ongoing view of how many calories and grams of protein I have remaining for dinner. Without these running totals, I have found myself doing manual calculations each day, which quickly becomes tedious.
Originally I planned to add these totals later, but I decided to move them up in priority because of how often I found myself missing them during daily use.
We already calculate individual calories and protein for each food entry, and those values are generated by a query, not directly on the form. This is an important concept for anyone building Access databases: if you want your continuous forms to be able to sum or otherwise work with calculated values, those calculations should occur in your underlying queries. Placing them at the query level makes it easy to total data in the form's footer.
I then make space for the new totals by shifting existing controls down on the form. It's a simple process to copy and paste the calorie and protein fields from above, adjust their control sources to be the sums of the relevant values, and rename them appropriately. If you are tracking additional nutritional data (like carbohydrates or sodium), you can use this same idea to sum those values as well.
Down the road, I plan to add conditional formatting to visually indicate whether I am meeting my daily goals for calories and protein. This will eventually tie into a system table where you can set your own targets, and the totals can change color based on whether you are under, over, or right at your thresholds. For now, I simply highlight the totals with a basic color to distinguish them.
With the form adjusted and the new totals in place, daily meal planning becomes much more transparent. My preferred placement for totals is in the footer, which is typical for this kind of summary data.
During this process, I touch on another minor irritation—an issue where one form field becomes stuck displaying a particular food, such as canned salmon. This problem can arise when VBA code changes the value of a control, and then the form is saved in design view with that modified value present. The solution here is to ensure that control defaults are explicitly set whenever the form loads. That way, if code happens to leave a control in an undesired state, it gets reset each time you open the form.
I also mention how these sorts of practical annoyances often only become apparent through regular usage. When I used to build databases for other companies, I would often spend time sitting with users or observing them to see where hiccups arose and which features caused confusion or slowdowns. This is why a database should always be seen as a growing, evolving project, responsive to feedback and real-world use.
Next, I turn my attention to another usability point. Currently, the meal list is set up so that clicking an item jumps you neatly to the relevant detail area. The food list, on the other hand, does not behave this way, and it annoyed me to have to manually adjust focus while navigating. Setting the proper focus automatically can easily be handled using the Got Focus event on the appropriate controls, making the user experience much smoother and more intuitive.
Another issue I address involves how the database handles adding food log entries for future dates. Previously, no matter which date you were planning for, new entries were always assigned the current time, which could be confusing. My goal is to ensure that if the user is planning a meal for a date other than today, the database should automatically assign a default time of midnight for those entries. I discuss how to alter the relevant VBA logic to check whether the selected date is today, and if not, use the selected date's midnight as the entry time. During troubleshooting, I also show how using breakpoints and stepping through code can be invaluable for resolving logic errors—a practice I discuss in greater depth in my full Access Developer courses.
Lastly, I tease a few more topics that are coming up soon, including further feedback from users and an improved procedure for managing record movement in lists, which avoids unwanted screen flashing and unnecessary complexity. Members will also get access to lessons about adding sorting and filtering capabilities, and displaying or filtering food items by multiple dates, among other enhancements.
Silver members and above have access to all my extended cut videos, which now number in the hundreds. Gold members additionally can download the complete database files and sample code. As always, new procedures like the improved "bounce" routine will be added to the code vault for convenient reference.
That wraps up part 35 of this ongoing fitness database series. I will return for the next lesson with even more fixes and new features.
For a complete video tutorial, including step-by-step instructions and demonstrations of everything I covered today, visit my website at the link below.
Live long and prosper, my friends.Topic List Adding daily calorie and protein totals to the food log form
Creating calculated fields in a query for totals
Displaying sum of calories and protein in the form footer
Copying and modifying controls to show totals
Using the Sum function in control sources
Explaining placement of totals in form footers
Planning for conditional formatting based on nutrition goals
Explaining why calculations must be in the query not the form
Fixing a combo box default value stuck on a specific item
Using VBA to reset control defaults upon form load
Updating the food combo control in the form's Load event
Improving data entry usability with control focus changes
Using Got Focus events to move cursor to appropriate controls
Automatically placing the cursor at the end of the text in a field
Implementing navigation improvements in the food list form
Correcting date assignment for planned future food log entries
Updating VBA to use selected log date instead of current date
Debugging VBA code to identify and fix incorrect date/time assignment
Using breakpoints and step-through debugging in VBA
Deleting and managing planned food items in the log formArticle In this tutorial, I will walk you through several practical improvements for a fitness or food tracking database built in Microsoft Access. These lessons are not just for those interested in fitness tracking; the techniques apply to any kind of database where you want to streamline data entry, display useful calculations, and troubleshoot everyday annoyances that appear as you use your system regularly.
One of the first improvements is adding daily totals for calories and protein right on your food log form. As you log your meals—breakfast, lunch, dinner, and snacks—it becomes very helpful to see at a glance how many calories and grams of protein you have consumed and how much you have left for the day according to your goals. Unfortunately, if you try to just add a calculation control directly into your form, Access does not handle this well if your controls are based on calculated values. The correct approach is to handle these calculations in your underlying query.
For example, if your form displays records from a "FoodLogQuery," make sure the query itself includes calculated fields like TotalCalories and TotalProtein. You might do something like this in SQL for your query:
SELECT ..., ([Calories_Per_Serving] * [Number_of_Servings]) AS TotalCalories, ([Protein_Per_Serving] * [Number_of_Servings]) AS TotalProtein, ...
FROM FoodLog;
Once these calculated fields exist in your query, you can display their sums in the form footer. Switch your form to Design View, drag the footer down to create space, and copy and paste an existing text box control to make room for the new total controls. For the control source of your calorie total, use:
=Sum([TotalCalories])
Give this control a clear name, like SumTotalCalories, and label it as "Calories." Do the same for protein totals, setting the control source to:
=Sum([TotalProtein])
and naming it SumTotalProtein, with the label "Protein."
Totals should be placed in the form footer. This is conventional and makes it easy for users to find them where they're expected. This approach not only makes your data entry screen more informative but saves you from needing to do mental math every time you add food entries. Now, as you add foods throughout the day, you instantly see updated calorie and protein totals below the form.
As you continue to improve your form, you might eventually want to color-code these totals. Conditional formatting can make the totals appear green when under your daily goal or warn you when you go over. You can even create a system table to store user-specific daily goals, and then have your forms use that information for feedback.
Another annoyance you may run into is a combo box—say, one that lists food items—getting stuck on a specific value (such as "canned salmon") every time the form opens. This often happens if VBA code has set the control's value, and you then save the form in that state in design view. To fix this, always reset your combo box in the form's Load event, which runs every time you open the form for data entry.
Suppose you have a procedure that manages your food combo box's value, like UpdateFoodComboControl. You should call this procedure from the form's On Load event:
Private Sub Form_Load()
' Other initialization code...
UpdateFoodComboControl
End Sub
This ensures that the combo box's value is set to the default (usually blank) every time you open the form, preventing it from getting stuck on an old selection.
Data entry can also be improved by controlling which controls get focus at the right time. For example, if you want to make it easier for users to jump directly into the description field when they click on the food description column, use the "Got Focus" event of each control. For the food group combo box, set its Got Focus event to move focus to the corresponding entry box:
Private Sub FoodGroupCombo_GotFocus()
FoodGroupCombo.SetFocus
End Sub
Similarly, with the description text box, you may not only want to place the focus there but also put the cursor at the end of any existing text so the user can keep typing. In the Got Focus event:
Private Sub Description_GotFocus()
If Not IsNull(Me.Description) Then
Me.Description.SelStart = Len(Me.Description)
End If
End Sub
This small detail greatly improves usability, especially for repetitive data entry.
Another common issue is making sure that new food log entries are assigned to the correct date and time, especially when planning meals for future dates. By default, if you simply assign Now as the date-time for each new entry, everything you add—even when looking at tomorrow's log—will be marked as today's date and time. To fix this, adjust your VBA procedure that adds food items to the log to check which day the user is currently viewing.
For example, in your add new food log item code:
If DateValue(Now) = DateValue(Me.LogDate) Then
FoodLogDateTime = Now
Else
FoodLogDateTime = Me.LogDate
End If
This way, if the user is planning for tomorrow, the log entry uses tomorrow's date (at midnight by default) rather than incorrectly assigning it to today. This ensures planned meals are logged on the correct day and prevents confusion.
To help identify and fix logic errors like the one above, become familiar with debugging in VBA. You can use breakpoints (click in the left margin next to a line of code) and the F8 key to step through code execution line by line. When a breakpoint is hit, you can hover over variables to see their current values and watch how your code behaves. Use this information to spot and repair bugs, such as wrong date assignments.
Finally, remember to test your improved navigation and data entry logic. For example, after making focus changes or correcting assignment of dates, close and reopen your forms, add test entries for today and other dates, and verify that everything works as expected. If not, revisit your code and debug as needed.
Through these simple but effective refinements—adding totals in form footers, controlling focus for faster data entry, resetting combo boxes with VBA, and assigning correct dates in code—you make your Access database more reliable and enjoyable to use in daily life. These principles apply to all kinds of tracking systems, not just fitness databases, and will help you build forms that stay easy and frustration-free as your needs evolve. |
