Fitness 22
By Richard Rost
3 months ago
Delete Button with Cascade Delete, Requery Button
In this Microsoft Access tutorial I will show you how to add and configure delete and requery buttons to your meal management form, set up and modify cascade delete relationships, control deletion options on your forms, and update event handling to improve user workflow when adding records. This is part 22.
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 series, meal list form, sort buttons, filter box, design view, delete button, requery button, Me.Requery, cascade delete, referential integrity, relationships, meal subform, AfterUpdate event, SetFocus, meal log, resync subform
Subscribe to Fitness 22
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part 22 of my fitness database series, and again, whether or not you care about fitness, this is a database tutorial. So it does not matter what you are tracking; all this stuff is cool for any database that you want to build.
Let's get into it.
We are still working with our meal list now, just to bring people up to speed. I have gotten a couple of emails that are like, "Hey, my form does not look like yours. What happened?" I did a few things in the last extended cut for the members. After the last video, part 21, we put the little sort buttons up here so you can sort these columns.
I put a filter box up here so they could type in, like, "ri," and it will just filter for things that have "ri" in them. That is in the extended cut for the members. I also made it so you could double-click down here in this empty box and it will go to a new record, but it is not right up at the top. I will give everybody else's, and I am going to make this bigger. I always make this big. It does not fit in the box there, see?
Yours just puts it up at the top, but it scrolls everything else way up. That is one of the things we fix in the extended cut.
Next up, we have to add the other two buttons down here for delete and requery. That is mostly just grabbing them from here with some minor modifications, so let's throw everybody in design view and grab the delete and the requery buttons, copy those, and paste them over here. Now, when you paste, they come in up top here, so we just have to click and drag them down. I do not think I got them both, did I? Nope. I just got the one. Copy, paste. Now I got both.
Let's resize this guy so it is the same as the other ones. You do not have that. All right, so we got delete, add new, and requery.
Now, if you want to slide them over here under notes so that they look like they are in the same places in the bottom right corner of each form, that is up to you. I think they are fine over here. I am trying to save myself some space because I have a small window that I record in. My monitor is obviously much larger than this, but it is okay.
Let's start with requery; that is the easy one. This is just Me.Requery, so copy, right-click, build event. It is putting you down in the members-only area. Let's move that and put it up top there. Me.Requery, easy enough.
Oh, and I almost forgot. I got it in my notes here. In the subform up top here, this meal subform, this guy right there, bring up its properties. This text box for the description was actually - oh, it is not, it was named, I fixed it. Oh, I did, I fixed it. So your version of - I am like, why is it different? Your version of the database, if you are following along at home, the name was quantity, but it was still bound to description.
So if you did not catch that building it yourself, just make that change. I noticed that I must have accidentally goofed and copied it somewhere wrong, but it would not have mattered because there was no code pointing to that field at all. So just rename it to description.
Back to our regularly scheduled program. Let's come back down here.
The delete button now. Let's take a look at the delete button in the other form, right-click, build event. There is a bunch of stuff in here, and we do not need all of this. We do not need this food group combo stuff. But to make things easier, I am going to copy this and then we will just edit out what we do not need.
So back over here, let's go into this delete button's code now. Paste that in. So, "delete description, are you sure?" all this stuff is the same. This should be "if is null meal ID," right?
We do not have a food group, so we do not have to worry about that; get rid of that block. We still want "Me.Dirty = False" so if they are in the middle of editing, at least it gets saved.
Now, here is the interesting part right here. This is an easy change: "from mealT where mealID equals mealID."
Right now, we do not have cascade deletes on. If you are planning on using this database as just an Access database, then you can delete the child records in here as well. I normally do. I normally delete these myself manually. I will do something - whoops, I just pasted the whole thing - I will normally do this: I will just copy this guy and paste it in here "delete from mealDetailT where mealID equals mealID." In fact, you want to delete the details first. Put that first. Delete the child records first, then delete the parent records.
If you do this, then you do not have to worry about cascade deletes.
But I am going to put them on anyway, just in case. It is up to you if you want to. You can totally leave this off and do the delete with the cascade delete. What is a cascade delete? That is set up in the relationships.
So, Database Tools, Relationships. Here is our relationship from Meal to MealDetail. Open this up, and we have got referential integrity enforced. All you have to do is check this box here: cascade delete related records.
What that means is when you delete a record from the Meal table, it will delete any and all MealDetail records that are belonging to this Meal. Think of it like Orders - if you delete an Order, all of the Order Details go away. If you have referential integrity with cascade deletes on between, say, Customers and Orders, if you delete a customer, all of their orders go away, and that can be bad.
So it is up to you if you want to use this. Again, referential integrity and this kind of stuff does not work if these tables are in different back-end files. If you have got two split back-end databases and you have, say, Customers in one and Orders in the other, you cannot use referential integrity there.
So you have to learn how to maintain stuff like that in code. That is why I am showing you both ways. Pick one. You can do them both if you want to; there is nothing wrong with having both of these. You can have it manually managed here and also have it as a relationship thing, too, just in case someone comes in here.
I am also going to turn off Allow Deletions for the form. I am going to leave it down here to allow deletions for food items in the meal, but for the meal itself, we are going to turn deletions off here. That way, they have to use my button to delete it. I mean, did I turn it off already? I am pretty sure I did. Yeah, I did.
So I am managing it with code and we are also managing it with a cascade delete. Save it.
Debug, compile once in a while. Let's close that, close it, close it, open it. Let's delete Happy Popcorn. Let me try deleting it from here. Cannot, because that is an aggregate query. Someone is beaming in, hold on a second. I love that.
Can I delete Happy Popcorn from here? Nope, cannot delete with this form. How about delete Happy Popcorn down here, delete, are you sure? Yep.
It deleted it, but we have one more thing we need to do: this guy. This needs to be refreshed too. If you close this and reopen it, it is gone, but that does not help me. So what we also are going to do is, after we requery this guy, now we need to also requery the subform up top, right? That is mealListF.Requery.
We also need to make sure to resync the two because it is going to requery the top and it might be on a different one than the one on the bottom side. It should fire the current event. Let's see. Let's try it and see.
Well, let's go to Happy Popcorn here, delete. Yes. All right. I think it is going to work without it, but you could manually refire that on the current event too. I think if we add something new, like Junk Food.
Junk Food.
And that is one other minor thing I want to change too. After that, in the AfterUpdate event, I want to jump down here. Let's just put something in here. Delete it. Yes.
I think when we do the requery, it is going to be fine just the way it is. You could put the form current event here or just call that as a function too. Either one is fine. Requerying that meal list though should sort everything out.
So let's fix that AfterUpdate event right here, this meal name AfterUpdate event. I do not like the way that behaves. I want to go to this guy, set the MealDetailF.SetFocus. Let's try that.
So after all of this, let's see. Yeah, MealDetailF.SetFocus. So after you put that in there, we are going to move down to the subform, ready to enter the next or the first item.
So if I add new Junk Food again, Tab, there we go, now we are right down here, ready to add an item now in our Junk Food.
Every little bit that we work on counts. Every little thing we do is magic. We are going to get there.
So we have our Add New, our Delete, our Requery, see? They are just slightly different from this one, but it helps that we have got this guy built pretty much finished. This is pretty much as I like it because now we can base other forms off of this one without having to reinvent the wheel every single time.
I am starting to get a little antsy to get the meal log going, so we are going to start that in the next video. Pretty sure I am going to wait for it. I know, I want to start using that myself. I am still using Excel for tracking my stuff until this is to a point where I can start using this for that. I want to get that over myself so I can do that.
But that is going to do it for part 22. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part 23.
TOPICS:
Adding delete and requery buttons to a continuous form
Copying and pasting button controls between forms
Adjusting button placement and resizing on forms
Implementing the Me.Requery method on a button
Editing a delete button VBA event for custom needs
Handling deletion logic for parent and child records
Manually deleting child records before parent in VBA
Setting up cascade delete via relationships window
Enforcing referential integrity for related tables
Differences between manual and cascade deletes
Implications of referential integrity with split backends
Disabling Allow Deletions property for a form
Managing deletions using VBA instead of default behavior
Refreshing a subform after deletions
Requerying and syncing subforms after data changes
Setting focus to a subform using SetFocus in VBA
Modifying AfterUpdate event to shift focus for entry flow
Ensuring data synchronization between main form and subform
COMMERCIAL:
In today's video, we are continuing with part 22 of the fitness database series. You will learn how to add delete and requery buttons to your meal form, set up their VBA event code, and manage referential integrity with cascade deletes in table relationships. I will show you how to update your forms so records refresh properly after changes and make sure subforms stay in sync. We will also tweak the form to move the user focus down to the subform after adding a new meal, making data entry smoother. 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 purpose of adding the delete and requery buttons to the Meal List form?
A. To allow users to easily delete records and refresh the data displayed
B. To create a backup of deleted records
C. To limit user access to certain records
D. To export data to a spreadsheet
Q2. What does the "Requery" button accomplish in the form?
A. Sorts the records alphabetically
B. Refreshes the data displayed to reflect changes
C. Clears all the records in the table
D. Locks the form for editing
Q3. Why is it important to delete child records from the MealDetailT before deleting the parent record from MealT?
A. It prevents orphan records and maintains database integrity
B. It reduces the size of the database
C. It allows users to recover deleted parent records
D. It sorts the records by date
Q4. What does enabling cascade delete in relationships do?
A. Deletes child records automatically when a parent record is deleted
B. Prevents any deletions in the database
C. Transfers records between tables
D. Creates a copy of the deleted records
Q5. Which of the following statements is TRUE regarding cascade delete and database back-end setups?
A. Cascade delete works between tables in different back-end databases
B. Cascade delete only works within the same back-end database file
C. Cascade delete automatically backs up all deleted data
D. Cascade delete exports the data to another database
Q6. Why might you choose to manage deletions both with code and with cascade delete set in the relationships?
A. For redundancy and added data integrity protection
B. To confuse users about how deletions work
C. To speed up data entry
D. To disable all record deletions
Q7. What property was incorrectly named in the subform and needed to be fixed in the tutorial?
A. The description textbox was incorrectly named quantity
B. The meal date was labeled as calories
C. The notes field was named as ID
D. The meal type was mislabeled as description
Q8. Why was Allow Deletions turned off directly in the form for meals?
A. To force deletion of records to use the custom delete button for better control
B. To prevent editing of any data by users
C. To auto-save changes after each update
D. To allow new records to be entered
Q9. After deleting a record, why is it important to requery both the main form and the subform?
A. To ensure the displayed data is updated and synchronized
B. To automatically save the deleted record in a backup table
C. To log out the current user
D. To import new data from another database
Q10. What user experience improvement was made in the Meal Name AfterUpdate event?
A. Automatically setting focus to the MealDetailF subform for quick entry of detail items
B. Automatically exporting the meal details to Excel
C. Sorting all entries by meal type
D. Asking the user to confirm every change to meal details
Answers: 1-A; 2-B; 3-A; 4-A; 5-B; 6-A; 7-A; 8-A; 9-A; 10-A
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 focuses on continuing our work with the fitness database, specifically with meal management features. While this is set up for fitness tracking, the techniques and steps you will learn are valuable for any Access database project, regardless of the data you are working with.
In this lesson, I am picking up from where we left off in the previous installment. If you have been following along, you may have noticed a few differences between your meal form and what you saw in my last video. That is because, in the previous extended cut for members, I added several enhancements. For instance, I implemented sort buttons at the top of each column and a filter box, allowing users to type in a keyword and quickly filter items in the list. Another feature lets you double-click in the empty space at the bottom of the form to jump directly to a new record. Also, I adjusted the behavior so that creating a new record does not disrupt the view by scrolling everything unnecessarily. These improvements are detailed in the extended cut available to members.
For everyone else, today's lesson focuses on adding two important buttons to the meal form - delete and requery. These functions are essential for managing records efficiently, and I will explain how to add and fine-tune them.
To begin, you should enter Design View in your meal form and copy the delete and requery buttons from an existing form. Once pasted, they tend to appear at the top of the form, so you will need to drag them to the appropriate location. You may want to align them to the bottom right with the other action buttons, but placement really depends on your preference and available screen space.
The requery button is straightforward and simply needs to refresh the data displayed in your form. I recommend tying this to the Me.Requery command, which forces Access to reload the current records.
Next, I want to address something that may cause confusion with your own copy of the database. Within the meal subform, check the properties for your description field. If you followed along step-by-step, you might have accidentally named this control 'quantity' even though it is bound to the 'description' field. If that is the case, rename the control to 'description' for consistency and to avoid future confusion.
Now let's talk about the delete button functionality. Review the existing code for deleting records in your other form. You do not need everything from that routine, especially code that deals with food group combos, if those are not present in your meal form. The most important part is checking whether there is a meal ID to delete, saving any edits in progress, and then performing the deletion.
Here is a critical point: It is best practice to delete child records before parent records to avoid referential integrity errors. That means deleting any related records in the MealDetail table before you remove the parent Meal record itself.
You have a choice in how to manage related record deletions: You can handle them in code as I have outlined here, or you can take advantage of Access's cascade delete feature in table relationships. Cascade delete is enabled in the Relationships window within Database Tools. With referential integrity and cascade delete turned on between Meal and MealDetail tables, deleting a meal will automatically remove its related detail records. This can be helpful and convenient, but be careful - cascading deletes across relationships like customers and orders can result in losing a lot of related data if you are not careful.
Be aware that cascade delete only works if both related tables are within the same back-end database file. If you have a split back-end with tables in separate files, then referential integrity will not function between them, so you must manage deletes manually in your code.
For extra control, I also turn off Allow Deletions in some forms, so users can only delete meals through the dedicated button and not directly through the form interface. I suggest having both manual and automatic options available to ensure nothing gets overlooked if someone modifies data in an unexpected way.
Remember to periodically save your work and use Debug and Compile in the VBA editor to check for any issues. After implementing the delete and requery buttons, test them out by creating and deleting meal records. When you delete a meal, make sure the related meal list on your form updates immediately. This may require refreshing both the main form and the subform to keep everything synchronized.
There's another small improvement you can make for better usability: After adding a new meal, set the focus automatically to the subform where you can enter the meal details right away. This keeps the data entry process smooth and intuitive.
At this point, with your Add New, Delete, and Requery buttons in place and functioning, your meal management form is in great shape. Every little enhancement refines your database and builds a solid foundation for future features. In the next lesson, I will move forward with creating the meal log, which is essential for tracking what you eat day-to-day. Personally, I am eager to switch from Excel and start using this database for my own tracking very soon.
As always, you can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Adding delete and requery buttons to a continuous form
Copying and pasting button controls between forms
Adjusting button placement and resizing on forms
Implementing the Me.Requery method on a button
Editing a delete button VBA event for custom needs
Handling deletion logic for parent and child records
Manually deleting child records before parent in VBA
Setting up cascade delete via relationships window
Enforcing referential integrity for related tables
Differences between manual and cascade deletes
Implications of referential integrity with split backends
Disabling Allow Deletions property for a form
Managing deletions using VBA instead of default behavior
Refreshing a subform after deletions
Requerying and syncing subforms after data changes
Setting focus to a subform using SetFocus in VBA
Modifying AfterUpdate event to shift focus for entry flow
Ensuring data synchronization between main form and subform
|
