Fitness 16
By Richard Rost
3 months ago
Validating Required Input with the Before Update Event In this Microsoft Access tutorial, I will show you how to handle referential integrity errors when adding new records involving required fields, such as ensuring a food group is selected before saving a food item. We will write code to provide user-friendly error messages, check for default values in combo boxes, and turn the error-checking logic into a reusable function. I will also cover how to properly handle deletions of new unsaved records to improve the overall user experience. This is part 16. MembersIn the extended cut, we will fix the issue where adding a new record causes the form to scroll in a way that puts existing records out of view. I will show you how to adjust the form's behavior so that when you add a new record, the view remains user-friendly and does not jump unnecessarily, making data entry smoother. 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, fitness database series, referential integrity, before update event, combo box validation, foreign key, food group table, error handling, cancel event, function HasFoodGroup, Me.Dirty, Me.Undo, form design view, event properties, data validation, meal builder, food items, nutrition API, get macros button, food group combo, extended cut, relationship properties, delete record, status box, error message
Subscribe to Fitness 16
Get notifications when this page is updated
Transcript
Today's part 16 of my fitness database series. If you haven't watched parts one through 15 yet, go watch those first and come on back.
As a reminder, whether or not you care about fitness, this is just how to build a database, so fitness is just a backdrop. Everything else applies to all the different kinds of databases I can think about. So yeah, it's good stuff.
Alright, let's get into it.
Alright, so last time in level 15, we talked about this. I go to add an item and I type in bread or whatever. Then I try to move to another item. We get this message that's not very friendly: "You can't add or change a record because a related record is required in table Food Group T." If you're smart, you understand what that means; you have to have a food group. I want a better message that my user can understand.
Alright, so let's escape and get away from that.
Now, first of all, what's causing that message? What's causing it is the relationship between our two tables. Remember when we set up our--I'm so used to going to database and then compact repair--if we go to our relationships, remember we have a relationship here between the food group table and food table. If we look at the properties of that relationship, we are enforcing referential integrity. That means you have to have a matching record here. You can't have a food item without a food group item.
Now, where do we go to trap that error? If they try to add something in here, whatever it is, then they try to leave the record, what we want to do is catch that before the form tries to save the data to the table. We do that in the form's Before Update event.
You can also try trapping the form error by error number, but that's a little more difficult. The Before Update event is usually good enough. If you're not familiar with the Before Update event, go watch this video for a little more information on that.
What I can do is I'm going to hit escape, get out of that, right click, design view, come into the form's properties under the Events tab, find the Before Update event. This runs before the data is saved to the table, so while it's still dirty. In here, we're going to check to make sure there's a value in that food group combo, but you have to be careful.
Watch what happens.
Most of the time you check and say if isnull(food group combo), then at this point, if we don't have one picked, we'll say food group combo.setfocus, then food group combo.dropdown, and then we'll status over here in the status box, or you can use a message box but I prefer status: "Select a food group." Then I'm going to put a color in here. Let's go with FFaa, that's red, and then speak it. Those are things I did with the extended cuts for the members. Then we're going to set cancel = true. That will cancel the update. I know it's an integer, but I always refer to it as a boolean. Cancel = true just makes more sense.
Alright. Save it. Debug, compile, once in a while. Close it. Open it. Let's try to add a new item. Add in some stuff, and then switch to another record or whatever. Oh, wait. We still get the same error message.
What's going on there? Why didn't that work? I got nothing in my combo box here.
What's the problem?
This one trips up a lot of people, including me. I often forget to check this. Yes, this is null on the surface, but what's underneath this field? The combo box doesn't have a value in it, but it's bound to Food Group ID. Food Group ID is a foreign key. That foreign key is in our food table, right there. It has a default value of zero.
So even though you don't see anything in the box and you think it's null, it's really zero. Make sure you check for that. If it's an unbound combo box, it'll be null if there's nothing in it. But if it's a bound foreign key box like that, it's going to have a zero in it. This used to throw me off. I don't know how many nights I lost sleep over this problem in the past. So you are benefiting from my sleeplessness of years gone by.
But anyway, in here, the easiest way to do it, if you're not sure, is just check for both. If isnull or if food group combo equals zero, not nine, zero, then do that.
I have literally thrown laptops across the room with this problem. Add new, whatever, and then we'll go to another record: "Select a food group." There you go. I have to select the food group. This is fruits.
Now we also have to check for this same issue if we hit the Add New button again. Sometimes you do this: you put something in there, do this, and hit the Add New button again. Hit the food group--yeah, see? There it goes. You can't go to the specified record because after the speech ran, it tried to do this. We could either turn that into a function or just check for it here--both ways work. We could put an "On Error Resume Next" here, but then it'll error out on that and then move the focus, which I don't want it to do. So let's turn it into a function. That makes more sense.
So this guy here we will turn into a function so we can check it in both places. We don't want duplicate code.
We'll say: If not HasFoodGroup, then cancel = true. Keep it simple in here and we'll make this Private Function HasFoodGroup. It doesn't take any values in because it's just going to work with the current form. You don't have to send in any parameters and it will return a boolean value. It should work false.
In here we'll say: If IsNull or this or that, then we'll do the dropdown, set the food group, and then we'll just exit with the value. Here we'll say HasFoodGroup = False. Otherwise, HasFoodGroup = True.
If you're wondering why this line doesn't appear here like it should, I have an End If there instead of an End Sub. This should be a function--function because it's returning a value. Nope. This is the function, that's the sub. So this needs to be End Sub right there. And this is our function.
The Before Update will check: does this even have a food group? If it gets in here and there is no food group, it'll return a false and then cancel the event.
Now, we have to check for it also in the Add button, which is up top here. Let's say if you click the Add New button and this record is dirty (in other words, I'm editing this record), we're going to check to see if it's got a food group. So, If Me.Dirty (which means I'm editing this record), then you could put an And there, but I like to have it check one thing at a time in this case.
If Not HasFoodGroup, then Exit Sub. Don't try to go to the new record then.
So if it's dirty, if we're editing the record, check to see if it's got a food group. If not, exit out because the function is going to generate the error message. This way we're using this guy and not having the same code in two places.
Save it. Even if you're working in here, if this guy is dirty, it's still going to fire off events. I'm going to hit escape a couple of times and let's delete this. Delete that. Yes.
Now, save it. We don't need the debug compiler; we just did. Close it. Open it. Add. Red. Add. "Select a food group." We're good. Hit escape. Move off of it. "Select a food group." Yes, I have my speech set to British. It just sounds more refined. Oh, someone's beaming in. Hold on. They don't have Klingon available.
Now there's one other situation that we have to take into consideration.
Let's say they add a new record, blah, blah, blah, whatever. At this point, they realize they don't want it. They want to delete the record. They try to move off of it. They're still going to get this error message. I don't want to have to pick a food group, and they don't know they can just hit escape.
Obviously, there's a training issue involved here. Training is important. But if they just hit delete at this point and say yes, they're going to get an error message. Why? Because you can't set Me.Dirty = False without that value in there.
So hit stop. What we're going to do at this point, after this point right here, we've verified that they do want to indeed delete this record. We're just going to check and see if they've picked the food group or not. If not, we're going to hit the Escape key for them and cancel the edit. That means it's a new record and they haven't picked a food group. Again, just say: If IsNull(food group combo) or food group combo = 0, then it's a new record; just undo it. Me.Undo. Exit Sub. That's all we have to do, because they're editing a new record, they obviously haven't picked a food group yet, so it's not saved. We can just undo their edits and exit out. No need to delete stuff in the table and all that stuff.
Let's try it again. Delete. Are you sure? Yes. Undo just cancels what they're doing. Nice and easy.
I just want to check to see if I had a video on Me.Undo, and I don't. I know I cover it in a couple of different videos. This is one of those features that I've never made a video on, but I've used a lot because it has a lot of value. Now I have to make a TechHelp video just for Me.Undo so I get the keyword.
Now, this actually kind of showcases why I sometimes don't like using referential integrity, because those relationships between the tables--yeah, they make some things easy, but they make other things more difficult. I would honestly rather handle a lot of that myself in code, so it's six of one, half dozen of the other. You sometimes have to work around referential integrity in your code with your other buttons.
Again, remember that referential integrity doesn't work if you've got tables that are in different backend files. That can be a problem too. But we'll work with it.
Alright, time for some Q and A--stuff posted on my website and on YouTube, questions about previous videos. Let's see what we got today.
thegirl2000, a member, said that he's wanting to add recipes and nutrition. I wrote a database that would love to have an API link to some nutritional database. I already built that for the members earlier where we click on the Get Macros button and it returns the calories and protein and all that stuff, so that's already in there.
As far as adding recipes, I probably won't build this into a recipe database. We have food items and then we're going to build meals. But as far as individual, like flour and eggs and such, I'm probably not going to go that far. We are going to have food items and then we can bundle those together into meals as we've already done.
Bowareinbetton7052 says, can you add the part to update the prices every time you buy a food to show your food cost? No, we're not going to get into that.I'm not going to keep track of pricing and all that stuff. I've got lots of other databases where I build product databases, and in my developer course, we do stuff like this, but we're not going to track food pricing in this one.
This is all about just how it affects you - the calories you eat, the protein, that kind of stuff. I do have other videos that show you how to do this on my website.
Renee B099 says, I have an issue with the are you sure function? The bug highlights it as undefined.
This is a function that I built in one of my previous TechHelp videos. I try to do my best to make sure that if there are prerequisites that you need to know before watching a video, I mention it. I might have forgotten to mention this because it's something that I use a lot.
If you are ever unsure about something like that, go to my website and search for it. Just come right up here in the search box and type in are you sure. You'll see it's right there in my search history, in fact, and you'll see right there, there's the are you sure function. It'll take you right to the video where I talk about it.
So if I ever forget in the future and there's something interesting or weird or different, or a function that I mention that doesn't come up, search for it on my website and you'll probably find it. I apologize if I didn't mention that in a previous video. It's not a members cut thing, it's just something I forgot to mention, that's all.
I get so used to using my own toolbox of functions that I forget that you guys don't have that toolbox unless you've seen the prerequisite videos. I don't always mention the prerequisite videos, so that's my fault. I apologize.
All right, in today's extended cut for the members, we're going to fix a pet peeve that I can't stand. When you go to a new record, it drops it all the way down, but it puts it at the top. Let me show you what I mean.
Let's say, save changes. Let's say you're in here and you hit add new. It puts you on a new record, but it scrolls way to the bottom and it puts all of these way up top past where you can see them. It doesn't happen if you're down low or if you're down here; it doesn't do that. But if you're up here somewhere and you hit add new, it does that, and I hate that.
I'm going to show you how to fix that in the extended cut for the members. Silver members and up get access to all of my extended cut videos - all of them. There are lots of them, hundreds of them, and Gold members can download these databases. Everybody gets free training. You get some free training, and you get some free training, and it's fun, so check it out.
That's going to do it for today. That's part 16, folks. We'll see you tomorrow for part 17. I hope you learned something. Live long and prosper, my friends. See you tomorrow.
TOPICS:
Identifying relationship-caused error messages in Access forms
Understanding referential integrity between tables
Using the Before Update event to validate form input
Checking bound combo boxes for null or zero values
Handling default values in foreign key combo boxes
Implementing custom user-friendly error messages
Converting repetitive validation code into a reusable function
Using a function to validate required combo box input
Integrating validation checks with Add New button code
Handling deletion of incomplete new records
Using Me.Undo to cancel edits in Access forms
Discussing when to enforce referential integrity in Access
Explaining limitations of referential integrity with split databases
COMMERCIAL:
In today's video, we're continuing with part 16 of the fitness database series. We'll learn how to give users a more helpful error message when a required food group is missing, instead of the confusing default one. I'll show you how to use the Form's Before Update event to check for missing values, why checking for both Null and zero is important, and how to move this check into a function so you do not have duplicate code. We'll also cover handling unsaved records and letting users cancel edits without trouble. Plus, in today's extended cut, I'll fix a scrolling annoyance that happens when adding new records in a continuous form. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main cause of the "You can't add or change a record because a related record is required in table Food Group T" error message?
A. The table's primary key is missing
B. The relationship enforces referential integrity between tables
C. The table is read-only and cannot be updated
D. The user is entering duplicate values
Q2. In the context of this database, what does enforcing referential integrity ensure?
A. Every food item must have a matching entry in the food group table
B. Food groups can exist without any food items
C. Food item names must be unique
D. Users can add records in any order they want
Q3. Where is it best to trap errors that occur when a user tries to save a record without choosing a required food group?
A. In the After Update event of the form
B. In the table's Default Value property
C. In the form's Before Update event
D. In the database's startup macro
Q4. Why might checking only IsNull on a bound combo box for a foreign key not work as expected?
A. Combo boxes automatically convert null values to text
B. The background color changes the combo box behavior
C. Bound combo boxes for foreign keys default to zero if nothing is selected
D. IsNull always returns False for combo boxes
Q5. What is a suitable way to check if a user has selected a food group in the form?
A. Check only if the combo box is empty
B. Check if the combo box is null or equals zero
C. Only require the user to click out of the field
D. Ignore the requirement and let the database handle errors
Q6. Why is it beneficial to refactor the "has food group" checking code into a function?
A. It increases the number of lines of code
B. It makes it easier to use the logic in multiple places without duplicating code
C. It allows the code to run faster
D. It always prevents all errors without further testing
Q7. When a user tries to add a new record but then decides to delete it before selecting a food group, what is the recommended action?
A. Force the user to select a food group before deletion
B. Allow deletion and handle it with Me.Undo, cancelling the edit
C. Ignore the deletion attempt and do nothing
D. Delete the food group record itself
Q8. What does the Me.Undo method do in the context of database forms?
A. It reverts the database to the previous backup
B. It undoes all edits made to all tables
C. It cancels any changes made to the current record before they are saved
D. It deletes all records in the table
Q9. What could be a drawback of enforcing referential integrity strictly through relationships in your database?
A. It makes writing queries impossible
B. It sometimes forces the developer to work around referential issues in code
C. It disables all forms from being used
D. It allows saving of incomplete records
Q10. If the tables are stored in different backend files, what issue can arise with referential integrity?
A. Referential integrity will not be enforced automatically
B. The tables will be automatically merged
C. All users will be locked out of the database
D. Backup will not be possible
Q11. In response to a viewer's question, what was said about adding tracking for food prices in the database?
A. Yes, food prices are fully tracked in this solution
B. Food pricing tracking is covered in another product database, not in this one
C. Price tracking is required for nutritional summaries
D. Price tracking is necessary for referential integrity
Q12. How should students find explanations for commonly used utility functions like "AreYouSure" if they are not explained in the current video?
A. Ignore them if not familiar
B. Search for them on the website mentioned in the video
C. Ask for them in the video comments only
D. They are always included in every video
Answers: 1-B; 2-A; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-A; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone continues with part 16 of my fitness database series. If you have not watched parts one through fifteen, I suggest you start there and return to this lesson afterwards.
I like to remind everyone that even if you have no particular interest in fitness itself, the subject serves as a backdrop for learning how to properly build a Microsoft Access database. The concepts and techniques I demonstrate can apply across many different types of databases, so what you will learn here is universally useful.
In our previous lesson, part 15, we ran into an issue when adding new food items to our database. For example, if you type in a new food like 'bread' and try to move on without specifying a food group, Access throws up a generic and not particularly helpful error message: "You can't add or change a record because a related record is required in table Food Group T." While an advanced user might understand this means a food group must be selected, it is not user-friendly for the typical person.
This happens because of the enforced relationship between our food items table and the food group table. We have set referential integrity in our relationships, meaning a food item must have a matching food group in the food group table or the record cannot be saved.
To prevent this confusing error message from appearing, it is better to catch the issue before it ever reaches the table. The best place to handle this is in the form's Before Update event. This event fires before Access tries to save the record, which allows us to check conditions and intervene if necessary.
Typically, you might consider intercepting errors by their error numbers, but that approach can be a bit complex for this situation. The Before Update event is a cleaner solution and is often all you need.
Inside the form's Before Update event, I check if the food group combo box has a value. If it does not, I can prompt the user to select a food group and stop the record from being saved. Usually, one would check if the value is null to determine if nothing was selected. However, because the combo box is bound to the Food Group ID field (which is a foreign key), its default value is actually zero, not null, when nothing is selected. This is a common pitfall and one that has resulted in many wasted hours for me in the past.
So, instead of only checking for null, I check both for null and for zero. If either is true, I prompt the user to select a food group, highlight the field, and cancel the save. This helps to prevent the user from running into that cryptic Access error message.
Once I set up this validation, I noticed we need to handle the same check not only when leaving a record, but also when clicking an Add New button to start another new record. If the current record is incomplete and missing a food group, we should prompt the user and stop them from navigating away. Rather than duplicating validation code in several places, I turned this logic into a separate function called HasFoodGroup. This function checks for null or zero in the combo box, prompts the user if appropriate, and returns a simple True or False. That way, both my Before Update event and the Add New button can call this function to unify the logic.
After implementing this, if the user tries to save or move away from a new food item without selecting a food group, the system now gives a clear prompt and prevents them from proceeding. This greatly improves the usability of the form.
There is another scenario to consider. Suppose a user starts to add a new record, then changes their mind and wants to delete it before completing the entry. If they attempt to leave the record without a food group, my validation would stop them, but they may not know to press the Escape key to cancel their changes. To improve the user experience, I added logic to check for this case: if the record is new and missing a food group, I simply undo their changes when they try to delete it. This prevents the error message from appearing and gracefully lets them cancel out of a new record.
This sort of issue demonstrates one downside of relying on Access's built-in referential integrity enforcement. While those relationships are helpful and convenient for some things, they can introduce complications that require extra coding to work around. Personally, I sometimes prefer to handle this kind of data validation in VBA code, as it can provide more flexibility.
You should also remember that Access's referential integrity enforcement does not work if your tables are split across different backend files.
Now, I want to address a couple of questions and comments I have received on my website and YouTube channel.
Some students have asked about adding recipes and nutritional data. In the past, I have built functionality to connect with external nutrition databases via an API, so if you are interested in retrieving macros like calories and protein, that is already covered in previous lessons for members. Regarding recipes, the database focuses on food items and building meals from these items, rather than getting into a full recipe database with ingredients like flour or eggs in that level of detail.
Another common question concerns tracking the price each time you purchase a food item to analyze food costs. This is not something I will be adding to this database. If you are interested in product pricing and inventory, I have other courses and sample databases available where I cover those topics.
One more issue was raised about the 'are you sure' confirmation function I use for delete operations. Sometimes students encounter errors with this because they do not have the function defined in their database. This function is available in a previous TechHelp video. If you are ever uncertain about where to find something I mention, especially if it is a function, use the search box on my website to locate the relevant tutorial. I try to reference prerequisites in my videos, but occasionally I forget, so searching my website is always a good strategy.
In today's Extended Cut for members, I address a particular annoyance: when you click the Add New button, the subform sometimes scrolls in a way that leaves the new record at the top out of sight instead of in view. I demonstrate how to fix this behavior in the Extended Cut. As always, Silver members and above get access to all extended cut videos and Gold members can download working databases. I encourage everyone to check out the resources available on my website.
That wraps up part 16 of the fitness database series. There is a complete video tutorial with step-by-step instructions covering everything discussed here available on my website at the link below.
Live long and prosper, my friends.
Topic List
Identifying relationship-caused error messages in Access forms
Understanding referential integrity between tables
Using the Before Update event to validate form input
Checking bound combo boxes for null or zero values
Handling default values in foreign key combo boxes
Implementing custom user-friendly error messages
Converting repetitive validation code into a reusable function
Using a function to validate required combo box input
Integrating validation checks with Add New button code
Handling deletion of incomplete new records
Using Me.Undo to cancel edits in Access forms
Discussing when to enforce referential integrity in Access
Explaining limitations of referential integrity with split databases
|
