Fitness 9
By Richard Rost
6 months ago
Creating a Delete Button with SQL for a Form In this Microsoft Access tutorial I will show you how to efficiently add new food entries to your fitness database, handle duplicate records, and create a custom delete button using SQL for greater control over data deletion. We will also set up automatic record saving with a custom function and discuss how to use AfterUpdate events to streamline workflow as you categorize and update food items. This is part 9. MembersThere 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, fitness database, track food, track exercise, food table, Query Design, calories, protein, Datasheet mode, copy paste data, delete button, Allow Deletions, SQL delete, CurrentDB.Execute, AreYouSure, AfterUpdate event, SaveRecord function, Recordset.Requery, RunSQL, duplicate removal, categorize food
Subscribe to Fitness 9
Get notifications when this page is updated
Intro In this video, we continue building the fitness database in Microsoft Access by importing new food data using Datasheet view, handling duplicate entries, and creating a custom delete button that uses SQL for record removal with confirmation prompts. You'll see how to disable default record deletion, ensure changes are saved with Me.Dirty = False, and keep your view in sync after deletions. We also cover assigning categories to foods and using the AfterUpdate event along with a shared SaveRecord function to automatically save changes when editing key fields. This is part 9.Transcript This is part nine of my fitness database series. We're building a fitness database. I'm building a fitness database for me to track food and exercise and all that good stuff. Even if you don't care about fitness, this will still benefit you because I'm covering lots of cool stuff that applies to any Microsoft Access database.
Go watch parts one through eight and then come on back.
All right, I have one more little list of food that I want to load in here, and it's just this little simple list here, which is just a food name, calories, and protein. We're going to do the same trick we did last time real quick. I'm just going to create Query Design, bring in my food table. This time I'm not worried about using the query because I don't have categories in all these, and they're all different. So it's just food name and then calories and then protein. Those are the fields that I care about.
I'm going to go into this mode here, right, spreadsheet mode -- Datasheet mode. Sorry. I'm going to select what I want from here and copy it. I'm down to the end here. Select these three -- let's call them cells -- and then paste. There we go. That's how you get data nice and easily in there.
Save changes. No, I'm not going to save that query. Requery this list and now you're going to see I got a bunch of stuff in here that I just have to go through now and put categories on. You can see I got a couple of duplicates, like provolone cheese here.
I mentioned last time we're going to build our own delete button. I'm going to put it on this form. It's one extra step. It's too easy, I think, to let them delete stuff right from here. Although if you want to put the delete button here too, that's fine. I'm just going to stick it on this form for now. Maybe later we'll put one down here too, but I have a specific way of doing deletes.
I like to control the delete. I don't like to let the users do it from the form. So I'm going to come over here, we're going to turn on Data, we're going to set Allow Deletions to No. They have to use my button, which is going to be this button here, because I like to do certain kinds of things, certain weird stuff that you might not have seen before.
This will be my delete button -- oops, Delete button. Now, right-click Build Event. Now, there is a way to turn this off. There is a way you can do this just with the form, with the DoCmd.RunCommand Delete, all that stuff, but I like to do it in the background using SQL. I think it's just more efficient and you get better results this way.
First, we'll check to see if we actually have a food item. So: If IsNull(foodID) Then Exit Sub. You have nothing to delete. In fact, you could hide the delete button if you want to, in the OnCurrent event, but that's up to you.
Next, we're going to say, are you sure you want to delete this item? So we're going to say: If Not AreYouSure("Delete " & [food item] & "?", "Delete") Then Exit Sub. So if not AreYouSure -- if you're not sure, if they say No or Cancel for that delete, it'll back out.
Now, next thing is just in case they happen to be editing this record, we need to save it to the table so we can properly delete it: Me.Dirty = False.
All right, now here comes the magic. We're going to use SQL to delete this record. So: CurrentDB.Execute "DELETE FROM FoodT WHERE FoodID = " & [FoodID]. That will actually delete from the table.
If you do a recordset requery, it doesn't requery the whole form and put you back up at the top. It requeries the form and it reloads the records, but it leaves you at the position that you're in. It doesn't move the cursor position, so you stay on the same record, and if you were on a deleted record, it puts you on one right next to it.
So let's say you've scrolled all the way down to P and you delete something -- you'll still stay down there near that P item.
All right, here's a video where I teach you more about that. It's called "Requery In Place." It uses Recordset.Requery. If you want to learn a little bit more of the SQL language with Access, go check this out. Of course, I have a full SQL seminar. I'll put a link to that down below. In fact, I've got three different SQL language seminars to teach you all the different commands and stuff, and I also have an SQL Server seminar. I've got lots of stuff on my website if you want to learn this stuff.
One more good video -- if you want to learn more about that CurrentDB.Execute, go watch this. There's RunSQL and Execute, two different commands. They both have their own pros and cons. This video describes them.
All right, so that's our delete code, and we could easily put something similar in the other form too, but I like that extra little step that they have to make to open up the item and delete it from there. I don't like to make it too easy to delete stuff.
All right, save changes. Now, let's say we want to delete this duplicated provolone here. All right, hit delete. Are you sure? Yep, and see, it kept you right in that general area.
Here's another one -- beans, black beans, cup. Delete that one. Are you sure? Yep, kept you right there.
You can start from the top. I'm going to keep these two separate avocados because these are the stats for actual avocados. This is a stat my wife -- she buys these avocado trays, which is like a mashed up avocado, and they come in four ounce trays. They must add something to them because there's more calories for the protein that you get, but they are delicious. We just have to go through and put these into different categories like this and save that.
Same for banana. I'm going to delete that -- that's a duplicate.
And so on. Berries, one cup -- let's put this in the fruit category.
What else have we got? See, stuff like this you might want to put AfterUpdate events in some of these -- like cereal here, for example. That should go into grains as soon as I pick grains. You could refresh the record at that point. I think I kind of like that. See, like I said, sometimes you discover this stuff as you're doing it.
All you need here is a quick AfterUpdate event. Just put a Me.Dirty = False in whatever fields you want it to automatically and quickly update, like that one, maybe the name. I don't know. See, now I'm torn because now I do want to do the ones over here where it's calories, protein, and so on.
Since we're going to do that, let's not do it in a traditional event procedure. Let's make a function for it. So I'm going to have a public function. Let's call it SaveRecord, and then in SaveRecord we'll put the Me.Dirty = False. We'll get rid of this.
Now all we have to do is -- I'm going to resize this so you can see this better -- pick the fields that are over there. So that's picked, and then hold the shift key down, this guy, this guy, this guy. If any of those are changed, the AfterUpdate event can be: =SaveRecord()
Then that will call this. See how easy that is?
All right, Debug, Compile, and let's test it. Let me resize this so it's back here.
All right, let's try it. Save changes, yes. Let's come in here. Let's put this in the frozen dinners category, and you can see it automatically quickly updates over here and it doesn't confuse your users.
Okay, grains -- change protein to 22, and it instantly updates. I forgot what that was, we'll put it back to 18 -- okay, undo.
Little things like this, I want to get all the little things out of the way before we start getting into more big stuff. What's next in the list here? Let's see what we got.
Another thing I was thinking of -- when you go to add new, if I add ham sandwich and then I hit the Save button, it does it, but it doesn't do anything over here.
In fact, you have to manually hit the requery button to find your ham sandwich. Where is it? There's a ham sandwich. So I want to kind of do something similar to what the delete button did.
We're going to save it, do a requery of this, but then find that ham sandwich and highlight it. So, when you save it over here, it updates and shows it over here.
We'll do that in tomorrow's video. So tune in tomorrow. Same bat time, same bat channel.
Yeah, I didn't put the Batman screen up there. I should, but we'll cover that tomorrow. So that's going to be your TechHelp video for today.
Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 10.
TOPICS:
Pasting bulk data into a table using Datasheet view
Handling duplicate food entries
Creating a custom delete button on a form
Disabling default record deletion in form properties
Using SQL to delete a record via CurrentDB.Execute
Prompting user confirmation before deletion
Checking for valid record before allowing deletion
Saving pending form changes with Me.Dirty = False
Maintaining user position after record deletion
Categorizing food items in the database
Using AfterUpdate event to auto-save field changes
Writing a shared SaveRecord function for AfterUpdate
Assigning SaveRecord to multiple AfterUpdate events
Testing auto-save functionality after field changes
COMMERCIAL:
In today's video, we're continuing with part nine of building a fitness database in Microsoft Access. I'll show you how to import new food data, handle duplicates, and add a delete button with a confirmation step to safely remove records using SQL. You will also learn how to update records quickly with a custom SaveRecord function and automate saving changes when editing key fields. Along the way, you'll see how to keep your data display in sync and pick up some tips for organizing your database. 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 method was used to enter a new list of food items into the database?
A. Manually typing each record into the table
B. Importing a CSV file directly using Access import tools
C. Copying and pasting data from a spreadsheet into datasheet view
D. Using an external automated tool
Q2. Why did the instructor choose not to include the category field when pasting in the new food items?
A. The field was locked and could not be edited
B. The new list did not have categories and all items were different
C. The list was too long to categorize immediately
D. Access does not support categories
Q3. What is the purpose of setting Allow Deletions to No on the form?
A. To prevent any changes to the data
B. To force users to use a custom delete button rather than deleting records directly from the form
C. To hide all deletion options from the user interface
D. To allow only administrators to delete records
Q4. Before allowing a delete, what condition does the VBA code check?
A. If any fields are blank
B. If the database is in exclusive mode
C. If the specific foodID is not null
D. If the user is an administrator
Q5. How does the instructor confirm that the user wants to delete an item?
A. Using the built-in Access confirmation dialog
B. Automatically deleting without confirmation
C. Using a custom AreYouSure function to prompt for confirmation
D. Sending an email for verification
Q6. Why is Me.Dirty = False used before running the delete SQL statement?
A. To undo any pending changes
B. To ensure any unsaved edits are saved before deletion
C. To lock the record from edits
D. To reload the form data
Q7. What SQL command is used to delete a food item from the table?
A. UPDATE FoodT SET Deleted = True WHERE FoodID = [FoodID]
B. DROP FoodT WHERE FoodID = [FoodID]
C. REMOVE FROM FoodT WHERE FoodID = [FoodID]
D. DELETE FROM FoodT WHERE FoodID = [FoodID]
Q8. What effect does requerying the form have after deleting a record?
A. Brings the user back to the first record
B. Reloads the data while keeping the user in the same general position in the form
C. Closes and reopens the form
D. Deletes all duplicate records automatically
Q9. Why does the instructor prefer using a custom function for after-update events rather than a traditional event procedure for each field?
A. It is slower but easier to debug
B. It allows centralizing the logic and calling a function from multiple fields
C. It does not require saving any changes
D. It restricts the user from editing multiple fields
Q10. How does the SaveRecord function work in the context of after-update events?
A. It prevents changes from being saved if the data is incorrect
B. It logs user actions for auditing purposes
C. It quickly saves changes by setting Me.Dirty = False whenever a relevant field is updated
D. It deletes duplicate food entries automatically
Q11. What limitation was identified after saving a new record from the form?
A. The record was not saved at all
B. The linked form did not automatically retrieve and highlight the new item
C. The record was saved multiple times
D. Other users were logged out of the database
Q12. What is planned for the next part of the video series regarding saving new records?
A. Adding foreign key constraints
B. Automating the requery and highlighting of the new item after a save
C. Converting the database to SQL Server
D. Locking the form after each save
Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-D; 8-B; 9-B; 10-C; 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 our journey in part nine of building a fitness tracking database. My goal with this series is to create a system for recording food, exercise, and all the other details someone might want for personal fitness monitoring. Even if you're not particularly interested in fitness, the techniques I demonstrate here are applicable to any Microsoft Access database project.
If you have not yet watched parts one through eight of this series, I recommend catching up before proceeding with today's content.
For this lesson, I have one final set of food items to add to the database. This is a simple list that contains just the food name, calories, and protein content. Using the same approach as before, I start by creating a query in design view, adding in my food table. Because the new entries do not have categories and each one is unique, my focus for now is just on the food name and its corresponding calorie and protein values.
Switching into Datasheet View, I select the relevant data from my external list and paste it directly into the table. This method makes it quick and easy to add new information.
After pasting in the data, I save the changes and refresh the list to display the newly added items. Now I can see the updated entries, though this also highlights a couple of duplicates, such as provolone cheese.
Previously, I mentioned adding a dedicated delete button to our form, and now it's time to implement that. My preference is to control deletions carefully and not allow users to simply remove records from the form view itself. Therefore, I set the form property to disallow deletions through standard means. Users will only be able to delete items using the custom button I create.
I add the delete button to the form, and then set up its event procedure. While you can remove records using built-in commands, I prefer handling deletions in VBA using SQL, as this offers more control and efficiency.
First, the button checks if there is a selected food item to delete. If not, the operation cancels immediately. Optionally, you could hide the delete button when there is no item selected. Next, the user receives a prompt to confirm deletion. If the user declines, the process stops.
To make sure nothing is lost or in progress, any edits to the current record are saved before continuing. Then, using an SQL delete command, the specific food item is removed from the table. By requerying the form, the records refresh but your position in the list remains, rather than jumping you back to the top. This keeps the user experience smoother, especially when working with long lists.
I have another tutorial that explains this technique in greater detail, called "Requery In Place." It covers using Recordset.Requery to refresh the data while maintaining your place. For those wanting to learn more about SQL in Access, my website has several seminars covering SQL basics, advanced commands, and even working with SQL Server.
If you're interested in the differences between RunSQL and Execute, I also recommend my video on that topic, as each command has its own advantages depending on the situation.
With the new delete code in place, you could easily implement a similar feature on other forms within your database. Personally, I think it's important to require deliberate action when deleting records to avoid accidental data loss.
Testing the new feature, I remove a duplicate provolone cheese entry and a duplicate black beans item. Each time, the confirmation prompt appears, and once confirmed, the record is deleted but my focus remains near the previously selected record.
For items like avocado, I keep both entries since one refers to whole avocados and the other to mashed avocado trays my wife likes. These have different nutritional stats and should be grouped in the appropriate categories.
Going through the list, I place items like berries into the fruit category. For other fields, such as cereal, I realize it would be helpful if the form automatically saved changes to the database as soon as the user updates certain fields. This provides immediate feedback and keeps the user interface consistent.
To achieve this, I set up a function named SaveRecord, which handles saving changes programmatically. I then assign this function to the AfterUpdate events for any fields where I want changes to be saved automatically. This approach is efficient, keeping the data up-to-date and minimizing confusion.
Testing this, I change a food's category and see the record instantly update. I can do the same with numerical fields, like adjusting protein content.
Before moving on to more complex topics, I want to ensure all these small workflow details are addressed. One additional improvement to consider is ensuring the newly added record appears and is highlighted on the list after being saved. Currently, adding an item such as "ham sandwich" requires manually refreshing the list before it shows up. My plan for the next video is to automate this process, so when you add and save a new item, it is immediately visible and selected in the list.
We will work on that enhancement in the upcoming part ten of this series.
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 Pasting bulk data into a table using Datasheet view
Handling duplicate food entries
Creating a custom delete button on a form
Disabling default record deletion in form properties
Using SQL to delete a record via CurrentDB.Execute
Prompting user confirmation before deletion
Checking for valid record before allowing deletion
Saving pending form changes with Me.Dirty = False
Maintaining user position after record deletion
Categorizing food items in the database
Using AfterUpdate event to auto-save field changes
Writing a shared SaveRecord function for AfterUpdate
Assigning SaveRecord to multiple AfterUpdate events
Testing auto-save functionality after field changes |
