Fitness 15
By Richard Rost
10 months ago
Minor Redesign - Merging Food List and Food Form
In this Microsoft Access tutorial, I will show you how to redesign a form in the Fitness Database, moving from a two-form setup to a single, integrated form for improved usability. We will go over best practices for backing up your database before making major changes, updating form layouts, modifying queries for efficiency, handling control naming, editing tab order, and simplifying code such as add and delete functions. I will also share tips on handling real-world workflow improvements and answer viewer questions about filtering and sorting records in Access forms. This is part 15.
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
Recommended Courses
Up Next
Keywords
TechHelp Access, Fitness Database, form redesign, integrating forms, continuous forms, footer controls, conditional formatting, tab order, updateable recordset, backup strategies, control naming, deleting records, recordset requery, error messages, VBA code migration, SQL record source, sorting and filtering, user experience improvements, automation, data entry validation
Subscribe to Fitness 15
Get notifications when this page is updated
Intro In this video, we continue with part 15 of the Microsoft Access Fitness Database series. I will show you how to redesign your forms by integrating two separate forms into one, move fields to the footer, update queries for simplicity and performance, handle default values, and transfer VBA code after making design changes. We'll cover reorganizing tab order, adjusting conditional formatting, cleaning up control names, and creating more user-friendly error messages. Along the way, I'll share tips for backing up your database, discuss best practices for sorting and filtering, and answer viewer questions. This is part 15.Transcript We are back with the Fitness Database. This is part 15 of my Fitness Database series.
As a quick reminder, whether or not you care about tracking fitness, food, exercise, or anything, that does not matter because the techniques that I am showing you in this database series are the same for any database you are going to build. We are building a real-world database that people are going to use, and all the tips that I am showing you with the forms, the queries, the VBA, and the SQL all apply to any Access database, whether it is customers, inventory, orders, or whatever. It is all the same skills. It is all Legos. You can put them together however you want. This is just the example that I am using for this database.
Now, if you are a member, you know it has been about two weeks since I posted part 14. If you are not a member, it has only been a couple of days because I scheduled these to go out in the future. As I posted about in my captain's log, I have had some dental problems over the past couple of weeks and it has kept me from being able to move my jaw, so I have not been recording any videos. Now that I am actually able to properly work again, we are going to continue with this series.
Over the past couple of weeks, even though I have been mostly limited to eating yogurt and soft foods, I have still been using the database on my own. I have realized that the way we have been building it is not exactly what I want. And this will happen. I realize there are places that the database is a little clunky. I thought at first it would be really cool to have this two-form setup and there we go. That is why that did not open all the way. However, the more that I use it, the more I really do not like it. The more I think I want to integrate these as one form.
We are going to take this up here and put it down here in the footer of this form and just use one form. I think that will work well for the meals too. Now, the meals you might leave like this; I am not sure yet, but the food, I definitely want to integrate it.
This is actually a teaching moment because I have personally spent weeks with clients developing what we thought would be the perfect interface for their business, for their database. We would flesh it all out on the whiteboard. I would make sketches. I would put a mock-up in Access for them or maybe even in PowerPoint and show what it is going to look like. They sign off on it. This looks fantastic.
Then I will spend however much time building the actual database and then once the people start working with it, they say it seems good in theory, but in reality, this does not flow right. We need to make changes. This will happen in any real-world situation. We start working with the database and, especially in my case, the people building the database, me, are not the ones using the database on a daily basis. So it was one idea to build it a certain way, thinking it would be cool.
We learned some cool stuff, like synchronizing two forms together. That is good stuff, good tricks to know, good tools to have in your box. But as I use this myself, I am just feeling that it is clunky, and I want to kind of redesign it a little bit. This is something that is going to happen in your life as a developer. Your job as a developer is to adapt and improve, kind of like the Borg. You have to assimilate whatever the client wants and make it happen.
Do not consider it going backwards or starting over. We are not going to do that. We have got everything that is cool. Our infrastructure is good under the hood. The tables are fine, the queries are fine. Just a little bit of a form redesign.
Now, the first step, obviously, is to back up what you have got now because I have also done that too. I have built databases and then the client was like, now we need to make some changes. And then after they work with the changes, they are like, now we liked it better the first way we had it. So that can happen too. Do not lose anything. Keep all the versions of your database. In fact, I just made a copy of it now. So I have got a copy here.
And I have got some other weight loss stuff that I wrote years and years ago. I am going to update these for today and post these; I will put them on my website. Copy this, put a copy in your backup folder, date it, save it. That is one of the reasons I like Google Drive because it versions everything nicely for you. You can have multiple copies of the same thing up there.
Oh wait, there. I have got my obligatory backup warning slide that I have to put up on that. Back up your data, back up your database before you make major changes to it.
As soon as you realize it is not exactly working for you, or what you want, or what the client wants, at that point, start considering redesigning. It is better to redesign now when we have only got about 25 percent of the database built than it is to finish the whole thing and then have to go back and tear things apart.
If you have got all the polish on here, do not polish a turd. If you are not 100 percent happy with it, fix it before you realize it is a major problem.
One of the first things I want to do is in the food table, we have got our is active down here. I want to set this to yes. I did this off camera before because it was defaulted to no before, and as you add new foods, you want them to be assumed active. So make that change first.
Sometimes moving forward, I might make some little notes for things that I change off camera. I will be sure to mention them in the video, though, because I do that a lot. I will be working with it. It is Thursday night at 9 p.m. and I am like, I am going to make a little change. I am not going to sit there and make a video about it too. But I will make notes.
So let us open these guys up side by side. What I am going to do is I am going to move all of this stuff into the footer of this form. That will work because with a continuous form like this, you can have it still show you a single record on the bottom in the footer or in the header. All those fields will still be linked to the current record.
Let us design this guy. When you go into design view here, it is going to close that form because we have got the on close event here. So we will have to open up the food form separately.
Design view. There we go. I am going to make this nice and big. We are going to keep these buttons. I am just going to slide them down here out of the way for now. Then come over here. We are going to select all of these fields. Copy. Click over here in the form footer and paste them in.
Slide them over just a little tiny bit so they fit nicely. Looks good.
Now, we are going to see some green little dots over here because not every field in here is in this guy's record source. Do not do anything with this food form. Keep it for now. Like I said, we will hang on to that until we are sure we do not need it anymore.
Now we have got this stuff in the footer of this form. Let us take a moment and make this stuff white so we can actually see it. Let me select these guys and this, this, and this. Let us go to format. Make it white. That is actually readable now.
Let us save this and let us go to the query that this guy is based on, which is food with group q. You can click the little button here to get to it, but I do not like doing that. Let us close this, close this, and find it over here.
Design view. I like to close my objects. I do not like having all those layered objects open. The point of this query before was just to get that food group name, which we call food group here. Then I have got a bunch of other individual things in here.
Let us get rid of these individual ones that we do not need. For example, we do not need that. We will get food group ID from this table. We do not need it from that table. We do not need this one. Let us leave this here for our sort, but let us hide it, so we do not see two of them. Actually, that is food description. Let us leave that visible so we still see it because it is named differently. We have got two descriptions in here, food description and food group description.
All right, calories and protein we can get rid of because we are going to bring the star in. I mean, get rid of is active. Now just bring in the food T dot star and we will get all those fields back. If you run that, now we have got food group, we have got food description, and then our calculated field protein percent, and then we have got all of the fields from the food table.
I like to do that because we can pull any of these fields into our form now, and we do not have duplicates. I do not like having food T dot description or food group T dot description. I like to alias those and rename them. It just makes it easier in the long run.
Now, can we still edit these things? Yes, if you run this, you can see down here, as long as you can go to a new record and as long as you can edit in here, then this is still an updateable record set. If you make your query too complicated, you will not always have an updateable record set. I have whole videos on this subject.
We still do want to lock some things though because this is also updateable. If someone comes in here and edits that, it is going to update that table. We do not want that. We are going to lock the food group field on that form. We do want to be able to update all this stuff.
Let us save that. Close this. Let us go back to our food list.
The first thing I am going to do is break this connection so that this guy does not open this guy. Let us go back to design view here. That is going to be that show food F event. We can get rid of it here. I believe it is also in all these click events. Yes, we put it in a bunch of spots. So here, here, there, there, and there, it is in all of those. I will select all of that. I will select this on click over here, hit delete. That should change all of them.
If you want to get rid of that code, you can. That is up to you. If you think you might use it in the future, that is fine. I am eventually going to delete that food F because it is duplicated with what is right here. Let us find it. There is some pretty cool stuff in here, though. But again, I do not like having duplicated code in my database that I do not need. So let us excise that. It is in the past videos if you want to go find it.
Save that. Now, since we are going to go about editing this stuff again, let us go back in here and turn back on the record selectors. Yes. Let us turn allow additions back to yes. I am still going to leave allowed deletions as no, because I still want to use my delete button. I do not like giving users the ability to delete important stuff. Some forms I will leave it on, but most of the time I want to use my own delete button.
Now that also means that the add new button can be simplified. So let us right-click and go to build event here. We do not need a lot of this. All we need in here is to go to a new record. DoCmd.GoToRecord , , acNewRecord. That is all we need. We are going to do more of this later. We are going to do some more in here.
All right, save that.Now, if we look at these form fields, we have Food Group, Food Description, Calories, Protein, and Protein Percent. Those are fine. That's description. Calories here is now Text76, or 78, sorry, because it's duplicated from this one. Same thing with protein.
All the other fields that aren't up there kept their own names. But because there is already a field called calories and there is already a field called protein, these got renamed. For now, let's allow everybody to pretty much keep the names they've got. But I don't like Text78 and 79. So let's call this one Protein2. We'll call this one Calories2. I'm just clicking here, copy, click here, paste, and then put a 2 in there.
That's not super important because they're bound to the same table or the same field in the table. It's just the control names themselves that are different, and that can make a difference later on. But again, more on that one later.
Now, the Save button - we don't really need the Save button either because the purpose of the Save button was to save the record and update the list for this guy. But since it's all one form now, we really don't need this unless you want to keep that there for noobs who don't know how Access works. When you leave a record, it saves the data. A lot of people don't know that.
In fact, I've made whole videos in my TechHelp series showing how to make a Save button for people who are used to Word, Mix, Excel, where you must manually save it. I'm going to get rid of it, though. We don't really need it.
So, where's my Save button? Oh, I don't have - oh, oh, that code is in the other form. So, this button doesn't have any code behind it. As I went into the code builder, nothing happened. The Save button code is in the other form. So, we can just delete that button there, which means all of this code - yep, these other buttons - we're going to have to grab the code out of the other form. We'll get that in just a minute.
That's one thing to always remember when you copy objects from one form to another: their code doesn't come with them. So, hang on to that other module or other form until you get all the code out of it.
Speaking of which, let's do the Delete button. Let's go back into here, Design View. Here, right-click, Build Event. Let's grab all of this - copy it. Now, here is a trick. If you want to go back to the other form, just close this form module window right there, and then it puts you back on the food list.
Where's that Delete button? We just made an empty thing. Let me show you the members-only content. Let's come in here and there it is - Delete_Button_Click. Let's paste it in there. If you're paying attention, you just got a quick peek at some of the form filter stuff.
Now, most of this I'm going to keep. If it is an old FoodID, are you sure? Me.Dirty = False, that saves the record, deletes it from the underlying table. And now, this is the part here that says if the Food List is open, which it should be, then requery it and then close this form, which we can get rid of now and just requery the current form we're on.
But I don't want to requery the whole thing and have focus move back up to the first record. So, I'm going to say Me.Recordset.Requery. That requires it in place. And yes, it beeps every time you hit that period there. I have a whole separate video on Me.Recordset.Requery. I think I already told you guys.
I'm getting to the point now where I don't remember what I've covered in parts 1 through 14. So I might repeat myself, just deal with it. I'm old.
Let's save it, and let's give a Debug > Compile. We might see some errors. No? Okay. Let's close this. Close us. Let's take a peek at what we got. Save changes to Food List? Okay.
Looking pretty good. I can scroll down. For some reason, my mouse always beeps when I scroll down. I'm just using my scroll wheel right now and it's beeping.
I also missed that Save Records in some of these fields down here. So make sure you delete it out of Description, Calories - let's see, Protein's got it. That'll fix that problem. Yep, there it is there too, because we were saving it. Remember, when we made the changes over here in the little form, we would save it in the big form too.
Save that because those don't come up in a debug compile. If you've got those function properties in here, the compiler doesn't see those because those are properties in the form controls.
Let's save it and let's try adding an item. Let's see if our delete code works here too. Let's go to Add New. That's good.
I would kind of like the focus to start here in the Description field. While we're at it, let's add that conditional formatting to these guys too so they show up as yellow to the user. I like that up here, so I'm going to select all of these.
Let's make sure we have the same color, though. Let's see, Conditional Formatting. It's that dark yellow. We're going to click on all of these guys, I'm holding down the Shift key. We can do the Notes too. Format, Conditional Formatting, New Rule, Has Focus, and we'll just give it that yellow background. I like that. It works well for this database.
Save it. Close it. Open it. Let's have it so when we hit Add New, the focus goes here. Design View, my Add New button, Build Event. Right here, we're going to say - I think that one is just Description. Description.SetFocus. The top one's Food Description, this one's just Description.
Let's see, Add New. I like that better. We're going to do something else with this up a little bit later on. I just want to test to make sure my delete code is still working.
Let's put something in here. And now if I try to leave that record, look what happens. You can't add or change a record because you need a Food Group T. We're going to deal with that problem too. For now, you have to have it in the Food Group.
Let's leave it. Good. Come back to it. Good. Let's delete it. Delete it. Are you sure? Yep, and it works. Good. Everything's working so far.
I just noticed our tab order is off. Tab, tab, tab, tab. This is fine up here. Down here, I want to go tab, tab. It skipped protein. Tab, tab, tab, tab, tab. So we're a little wacky down here.
How do we want this to go? I'm going to say let's do this and then go down this column and then over here and then down those. That's how I think I'd like it to be.
To do that, we have to click on the form footer, then go into the Tab Order, and now we can arrange these guys.
So, Description. The buttons - I don't like having buttons in my tab order. That's just me personally. I'm going to take them all out as tabs and put them down at the bottom.
Description, Calories2, Protein2, Fat, Carbs, Fiber, Total Sugar, Added Sugar. Then we need the Food Group combo after the sugars, then the URL, then Active/IsActive, then Notes, and then all the buttons, which I'm going to take out of the order.
Click OK. I'm going to click on all of the buttons - that's just a me thing. I don't like having buttons in tab orders. If you want to be able to get to them with the keyboard, put little Alt key tricks in them. Other Tab Stop: No. Since they're different, you have to actually type in No. Do the same with that guy. Tab Stop: No.
Save it. If you want the little Alt tricks in here, do the little Alt dots: Alt+Delete, Alt+G for Get Macros, Alt+A to Add New, and then Alt+R for Requery. If you want that, then do that.
While we're at it, let's slide all these up here. Make them all part of the same family. Add New. Do we have room? Let's see if we've got room. Oh, we might not have ever. Oh, yeah, that's OK. We'll make them all see.
Again, I like to keep these all the same size. Oops, I'm moving them. There we go. I'm sure we'll be adding stuff and moving stuff around, but for now, make it a little more compact.
Save it. Close it. Open it. Oh, it's looking good. They're looking so much better. Now, down here, tab, tab, tab, tab, tab, tab, tab, tab. Beautiful. Perfect.
So that's pretty much it, that covers the redesign of the form that I wanted to do. I got rid of the second form. It's all part of the primary form now. It all works pretty much the same.
I can't figure out why my mouse beeps when I scroll. This should still work. Let's see, B-A-N, banana. There we go. That should still work fine.
Members, you have to move the code from Get Macros and the Bot button from the other form and put those in these buttons. You should know how to do that. That's pretty simple.
In fact, I just did it off camera. It took me five seconds. Get Macros is the only code you need. The bots are part of the global module.
So we're good. It took us 20 minutes, and now I'm much, much happier with this form the way it is right now. I like it a lot better. I think it's a lot easier to work with.
So, new material coming up next. Tomorrow is going to be August 1, so tomorrow will be a Quick Queries Friday. Monday, we'll get back into some new material. The first thing I want to do is when you go to add a new item - blah, blah, blah - and if you try to leave that item, you get this.
We saw this earlier: you cannot add because a related record is required. Now, this is fine. I don't like this. We're going to get our own friendly message so it tells people what's happening. This doesn't say anything and it doesn't really explain what's going on.
So, what we're going to do is make a friendly error message for it. When that happens, we'll drop this box down and say, pick one of these. Make it much, much friendlier for the user. There's a trick you're going to have to do, and it's not what you think. So, stick around.
As I said before, after some of these videos, I'm going to start doing some Q and A - kind of quick query style about this series, from people who posted.
Just a comment from Nick, he says, "A nice teaser showing that there's already 15 parts." Oh, there's going to be a lot more than 15, Nick. A lot more than 15.
Never been on a diet. M.O.A.D. is constant and that's great. Eat healthy, don't overdo it. Three games of squash a week do help. Of course they will, and that's fantastic.
But for a lot of people, myself included, I'm the kind of person where if I don't pay attention to my weight, I will just slowly gain weight. That's what happened to me over the last ten years.
As I said in previous videos, I went through a weight loss phase because I was 340 pounds when I was in my early 30s, and I had to work for a year or two, work hard, to drop all that weight off. But then, when I stopped that fitness regimen, the weight just slowly came back on to where I'm back up to 300 pounds without thinking about it.So for some people like me, I have to work at it. I have to be mindful of what I'm eating and get some exercise. If I don't, then it just comes back on.
It's different for everybody. Some of the other comments I've seen from people say that they hate tracking everything they eat. That is true, and it's not something that you have to do for the rest of your life. It's just something that you have to do until you become aware of what's in what you're eating.
When you become aware of how many calories you're actually eating, which I think a lot of people don't really realize, you find that even healthy things like a teaspoon of peanut butter are loaded with calories. They're good calories, but there's a lot in there. Walnuts, dried fruits, that kind of stuff, have a lot of calories as well. It's good food for you, but you just have to be mindful of what you're eating.
Is tracking that stuff something you have to do for the rest of your life? No, but it's good to do for a while until you educate yourself and get used to what you're eating.
Now I just know every day my breakfast is about 550 calories. I eat the same thing for breakfast pretty much every day. That helps form a routine.
As far as everything else, some people need to track it. I do. That's just me. I'm very database oriented, as you could probably tell.
Another question from the website: Jeffrey says, typically when I do this sort of sorting or filtering, I use VBA to rewrite the SQL for the form's record source. Instead of using Me.Filter or Me.OrderBy, is there an advantage to doing it one way or the other?
As Sammy and Raymond have pointed out, however you want to do it is fine. In fact, I do a lot of this by rewriting the SQL for the form's record source in a lot of my videos. It's usually my preferred way to do that unless I want the user to have control over that filter and order by.
If you just rewrite the record source property, then the user is stuck with just that record or records, whatever you give them. But if you want them to be able to turn the filter off or change it or change the sort on the fly, that kind of stuff, then accessing these properties works just as well, if not better, in that case.
It's just a different tool to have in your box, both are fine.
All right. So that's going to do it for today, folks. That is part 15. Again, tune in on Monday, August 4, 2025, for part 16. We'll cover even more stuff and do some new stuff.
But that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Integrating two separate forms into one main form
Moving form fields to the form footer
Backing up the database before redesign
Setting default value for IsActive in the food table
Editing the query to use table star for field inclusion
Making queries updateable by simplifying structure
Locking fields to prevent accidental editing
Breaking the code connection between main form and subform
Removing unnecessary event procedures after redesign
Updating command button code after copying controls
Adjusting add new record button to use DoCmd.GoToRecord
Renaming duplicated control names after merging fields
Removing the save button and its related code
Transferring custom delete button code between forms
Using Me.Recordset.Requery to refresh data in place
Removing residual function calls from form controls
Applying conditional formatting for focus highlighting
Setting initial focus when adding new records
Correcting tab order in the redesigned form
Removing command buttons from the tab order
Assigning Alt key shortcuts to buttons
Rearranging and resizing command buttons for layout
Testing and troubleshooting the revised form design
Adding user-friendly error messages for required relationships
COMMERCIAL:
In today's video, we are continuing with part 15 of the Fitness Database series. I will show you how to redesign your Access database forms by integrating features from two forms into one, making your database easier to use. We will discuss important developer tips like backing up your work before making changes, updating form and query layouts, moving VBA code when copying controls, and adjusting things like tab order and conditional formatting. I will also talk about handling data entry errors more gracefully and answer questions about filtering and sorting with VBA versus using form properties. 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 does the instructor emphasize that the techniques shown in the Fitness Database series are relevant for any database type?
A. Because they only apply to fitness tracking
B. Because all Access database concepts are fundamentally the same and transferable
C. Because only VBA code is different for each database
D. Because SQL queries only work for fitness databases
Q2. What is a common real-world realization about database form design, as discussed by the instructor?
A. The first design is always ideal if planned thoroughly
B. Users never ask for changes after initial approval
C. What looks good in theory may not work well in practice, and redesign is often necessary
D. Mockups are unnecessary and rarely used
Q3. What should you do before making major changes to a database, according to the instructor?
A. Delete old versions to avoid confusion
B. Back up your data and database
C. Change all form designs first
D. Only save important tables
Q4. What is the benefit of using Google Drive or a similar service for database work?
A. It has built-in SQL tools
B. It allows for easy shared editing of queries
C. It provides automatic versioning and backup
D. It can run VBA code in the cloud
Q5. Why is it preferable to redesign a database before it is fully built and polished?
A. Because partial redesigns are never possible
B. Because it is easier and less disruptive to fix issues early
C. Because tables cannot be changed after creation
D. Because Access databases cannot be modified later
Q6. Why was the 'IsActive' field in the food table set to default to Yes?
A. Because most new foods are inactive by default
B. To ensure new food entries are assumed to be active
C. Because 'No' was causing calculation errors
D. Because the instructor forgot the original default
Q7. When moving controls from one form to another in Access, what does NOT get transferred automatically?
A. The control names
B. The data bindings
C. The underlying VBA event code
D. The formatting properties
Q8. What is the main advantage of moving from a dual-form setup to a single integrated form in the case study?
A. It reduces the number of queries needed
B. It makes the interface less clunky and easier to use
C. It prevents table redesign
D. It increases security
Q9. How does the instructor recommend handling delete actions in user forms?
A. Allow users to delete records using the built-in keyboard shortcuts only
B. Allow deletions by default on all forms
C. Use a custom Delete button for control and confirmation
D. Disallow all deletions to prevent data loss
Q10. Why might an Access query become non-updateable, and why does the instructor check for this after modifying a query?
A. Non-updateable queries cannot display data
B. Too many fields make a query read-only
C. Complex queries or certain joins can prevent record updates in forms
D. Updateable queries cannot have calculated fields
Q11. What approach does the instructor take regarding the 'Save' button in Access forms?
A. Always require users to click Save to store data
B. Remove the Save button, as Access saves data on record change unless for user familiarity
C. Use Save buttons for every field
D. Hide the Save button but keep its code
Q12. How does Access handle records when users move to a new record or close a form?
A. It prompts the user to save
B. It automatically saves changes to the current record
C. It prevents saving until the form is closed
D. It deletes unsaved data automatically
Q13. Why is it important to rename copied controls like 'Text78' and 'Text79' to something more meaningful?
A. It affects the field names in the table directly
B. Meaningful names make future form and code maintenance easier
C. It determines the sorting order in the query
D. It affects the data type
Q14. What is the role of conditional formatting for fields like Description, Calories, etc. in the redesigned form?
A. To hide unused fields
B. To visually indicate the field in focus, improving user experience
C. To apply data validation rules
D. To prevent editing of those fields
Q15. What benefit does setting Tab Stop to No on command buttons achieve?
A. It makes buttons invisible
B. It improves keyboard navigation for users who enter data primarily with the keyboard
C. It prevents button clicks with the mouse
D. It disables the button's code
Q16. In the Q and A section, why does the instructor sometimes prefer rewriting the form's record source (SQL) with VBA instead of using Me.Filter or Me.OrderBy?
A. Because record source SQL is faster than filters
B. Because it allows the developer to limit user options for sorting and filtering
C. Because filters do not work in Access forms
D. Filters and order by cannot be changed by users
Q17. According to the instructor, is tracking all your food intake something you have to do for the rest of your life?
A. Yes, to maintain a healthy lifestyle forever
B. Only if you are trying to lose weight
C. No, you only need to do it until you become familiar with your eating habits
D. Only for people on special diets
Q18. Why does the instructor mention the importance of being able to recognize when a database design is "not working"?
A. Because stubbornly sticking to an original design can lead to inefficient workflows and user frustration
B. Because it is required by Microsoft Access guidelines
C. Because all databases should be re-built every month
D. Because forms cannot be edited after creation
Q19. When deleting a record in the integrated form, why does the instructor use Me.Recordset.Requery instead of requerying the whole form?
A. To prevent data loss
B. To avoid moving focus back to the first record after requery
C. Because it is the only way to refresh data in Access
D. To delete multiple records at once
Q20. What is the main message about the development process that the instructor wants students to understand from this video?
A. Database development is a rigid, linear process
B. Iteration, adaptation, and willingness to redesign are key to building useful databases
C. User feedback should only be considered at the end
D. Redesign indicates failure and should be avoided
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-C; 8-B; 9-C; 10-C; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-C; 18-A; 19-B; 20-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 part 15 of the ongoing Fitness Database series.
Before I begin, let me remind you that even if you have no interest in tracking fitness, food, or exercise data, the techniques I demonstrate in this series are universal for any Microsoft Access database you might build. Whether you're designing a system for customers, inventory, orders, or other business needs, the tips I present on forms, queries, VBA, and SQL are all interchangeable. It's just like putting together Legos—what matters is learning how to use the pieces, regardless of the specific example.
For those who are members, you may have noticed a longer gap than usual since the last video. I encountered some dental issues that made recording very difficult for about two weeks. Now that I'm able to work again, we're ready to continue improving the database.
Over the last couple of weeks, despite being limited to soft foods, I kept using and testing the database. This real-world usage made me realize that some of the design choices we started with did not work as well as I'd hoped. Originally, for example, I thought a two-form setup would be ideal for the food data entry, but in practice, it feels cumbersome and inefficient. This is an important lesson—when building a database, you may not notice shortcomings until you actually use it day-to-day. Things often look great in theory or in mockups, but only hands-on experience tells you what really works.
Through actual use, it became clear to me that integrating the current two forms into a single streamlined form would make the process easier, especially for entering and managing food items. This is a normal part of any development process. You spend time brainstorming, sketching wireframes, and getting client signoff. When the users begin working with the live system, the feedback often leads to necessary changes. As a developer, your job is to adapt and improve to meet practical needs. You should not see this as going backward or starting over—instead, take comfort that the underlying tables and queries remain solid. The main changes today involve redesigning the form interface.
Before making major design adjustments, it's always vital to back up your database. Over the years, I've encountered plenty of situations where clients decided to revert to earlier designs. I recommend saving frequent backup copies with descriptive names or dates. Services like Google Drive make this easy with built-in version control.
With that in mind, our first technical change is in the food table. I updated the default value of the 'IsActive' field to Yes so that new food entries are assumed to be active by default. It used to default to No, but switching this will align better with practical use.
Sometimes I make small changes off camera, often when working late at night or testing a feature. I keep notes so I can mention any modifications you might not see directly on screen.
The plan for the form redesign is straightforward: I will move the single-record details section from a separate form into the footer of the main food list form. This will allow the current food record's details to be displayed and edited directly within the same form. Continuous forms in Access allow you to have an editable single record section in the footer, which works well for this setup.
To begin, I open the food listing form in design view and move the necessary controls from the second form into the footer. Any buttons are put aside temporarily. After arranging the controls neatly, I adjust their formatting for legibility, like changing backgrounds to white.
The next step is to review the query upon which this form is based. The query consolidates the fields from the food and food group tables. To keep things organized and prevent duplicate fields, I remove any unnecessary columns and bring in all the necessary fields from the food table using a wildcard. Aliasing fields makes referencing them in the forms and code much easier later.
After verifying that the query remains updateable, I lock certain fields—such as the food group name—so users do not accidentally make changes where they should not. Maintaining an updateable query is important for editing records directly in the form.
Now it's time to remove any event logic that previously linked the two forms, since the details are integrated within the single form. This reduces duplicated code and avoids confusion. When copying form controls from one form to another, keep in mind that the associated VBA code does not come along, so you need to carefully migrate any button event logic as needed.
The Add New button is streamlined to simply move to a new record, since additional logic from the two-form setup is now unnecessary. Likewise, the Save button can be eliminated because Access automatically saves an edited record when the user moves elsewhere. Many users, especially those accustomed to explicit saving in Word or Excel, do not realize that saving is automatic in Access. However, if you prefer, you can keep a Save button for those new to Access.
For delete operations, I bring over the relevant VBA code from the original form. The main change is to requery the form's recordset in place, so focus is preserved and the updates are reflected instantly.
After this, I tidy up the tab order of controls in the new section. This ensures that pressing Tab follows a logical and user-friendly progression through the fields, skipping action buttons unless you specifically want them included. I also recommend setting Tab Stop to No for most buttons and using keyboard shortcuts with Alt keys if you want them to be quickly accessible.
To improve user feedback, I add conditional formatting so that fields that have focus are highlighted with a yellow background. These small touches help guide user interaction and make the form feel more responsive.
I also add VBA so that when a new record is started, the focus automatically jumps to the Description field, which is usually the first field users will fill in when adding a new food item.
Testing the redesigned form confirms that adding, editing, and deleting records works well. However, I notice that if a required related record (like a food group) is missing, Access gives a generic error message. In a future lesson, I will show how to intercept and replace this unhelpful error with a custom, user-friendly message to guide users through the correct process.
A few viewer comments are worth mentioning. Some people enjoy a consistent fitness routine and don't need to track their food. Others, myself included, find that tracking is helpful for increased awareness and to avoid unintentional weight gain. Tracking intake is not a lifetime requirement—it's primarily an educational process to learn the caloric and nutritional content of foods during the transition to new habits.
Another student asked about whether it's better to filter and sort forms using the Me.Filter and Me.OrderBy properties versus rewriting the form's SQL record source in VBA. My answer is that both approaches are fine, and which you use depends on your needs and how much control you want to give your users.
That concludes part 15 of this Fitness Database series. Next, I'll address customizing error messages and continue improving both usability and logic. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Integrating two separate forms into one main form
Moving form fields to the form footer
Backing up the database before redesign
Setting default value for IsActive in the food table
Editing the query to use table star for field inclusion
Making queries updateable by simplifying structure
Locking fields to prevent accidental editing
Breaking the code connection between main form and subform
Removing unnecessary event procedures after redesign
Updating command button code after copying controls
Adjusting add new record button to use DoCmd.GoToRecord
Renaming duplicated control names after merging fields
Removing the save button and its related code
Transferring custom delete button code between forms
Using Me.Recordset.Requery to refresh data in place
Removing residual function calls from form controls
Applying conditional formatting for focus highlighting
Setting initial focus when adding new records
Correcting tab order in the redesigned form
Removing command buttons from the tab order
Assigning Alt key shortcuts to buttons
Rearranging and resizing command buttons for layout
Testing and troubleshooting the revised form design
Adding user-friendly error messages for required relationships |
