Account Balances 6
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 6
This is part 6 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
Today, we're going to construct a delete button for our account form. I prefer managing the deletion process myself rather than relying on the default Access deletion routine, so we'll accomplish this using some VBA code. We will establish relationships between our account and transaction tables to prevent users from deleting accounts that have transactions, unless they delete the transactions first. However, we will utilize our own warning and error messages instead of the default Access error messages. Providing our own messages makes the process more user-friendly.
Members
Members will learn how to automatically reorder the list if an item's sort order value changes. Additionally, we'll create buttons to manually move items up and down the list with just a click.
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
Suggested Template
Links
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, delete button, relationships, referential integrity, cascade deletes, custom warning and error messages, user-friendly errors, auto re-sort list, move items up and down with buttons
Intro In this video, I will show you how to add a delete button to remove accounts in Microsoft Access, while enforcing referential integrity to prevent deleting accounts with existing transactions. We will disable Access's built-in delete features, set up custom user prompts with message boxes for confirmation, and make a few interface improvements like adding an open account button and resizing form fields. This ensures accounts can only be safely deleted, protects related data, and provides a better user experience. This is part 6.Transcript Today we're going to build a delete button so we can delete accounts. We don't just want people to delete stuff willy nilly, so we're going to first set up some referential integrity so you can't delete any accounts that already have transactions. That's bad.
We're not going to rely on Access's built-in error messages because they're not very good. If we don't have transactions, you can delete it. We're going to make sure you're really willing to delete this because you shouldn't. If you want to, here you go.
That's all coming up in part six. Here we go.
Okay, and we're back. One of the things that you're going to find throughout this series and most series that I do like this is that there are going to be some minor changes that I make off camera sometimes. Like yesterday when I wrapped part five, I started using it for a bit on my own and I noticed a few things I wanted to change. Just little minor things, but I always make a list of them so I can mention them to you in the video in the next class. That way you're not confused looking at my database, going, why is it different? What did I miss? So let me just show you a couple of things that I changed off camera.
Nothing major, like I said. I made the account fields a little wider, like this, a little wider here, because some of my account names are a little bigger than that. No big deal.
I added the notes field here, down here. If you go into an account, let's see, pick one that's got some notes here, cancel. I don't think I have any notes. Do I have any notes in these? No, I know. But if you put notes in one of them, it's rock. Save. And you'll see it down here. Because I like to just be able to, when I'm on the account, see whatever my notes are down here. Minor thing.
That is just a matter of, for those of you who are not familiar, anything that is on a continuous form, obviously, the details of each record show up here, but whichever record you happen to be on, if you want to see additional information for it, you can put it in the form header or footer. This notes field will be for whichever account you're on. So if I'm on the accounts page and I click on region checking, that's blank. If I click on MX Platinum, it's now showing you that record. I do that trick a lot.
I also made the detail form wider. Again, for me, I've got some notes that I want to be able to see more of over here, made these a little bit wider.
Oh, and one big thing that I did add. Go back to design view. Go into your save button. We need to add a me.refresh right here because when we save the record in this detail section and we want to go back and requery the parent, right, back in here - the list. This will requery, but this information isn't saved to the table until this line here when the close event happens. So we have to refresh this form first. Then we can requery the form behind it. Then when we close this form, that guy gets updated.
So without this me.refresh, if that's not there, save it. If I add a record, save it. It's not there. It's in the table, but it doesn't show up until we requery this guy. You can do it with an F5, but we don't want to do that. So again, you have to come in here, design view, and you have to put that me.refresh there. So it refreshes this record, then requeries the form behind it, then closes this one.
So those are the things I did off camera, but I figured I'd mention them. Let's get rid of this. We're going to work on the delete button today.
Now, again, yesterday after class, I sat down with it a bit and I was putting in some of my data, my sample data, my actual data that I use. That's how you really get to know the database the best - when you work with it yourself with real data. Obviously, I'm not going to rely on this as my only source of the real data until it's done and it's gone through testing. I'm going to run this simultaneously side by side with my Excel spreadsheet, but you get a better feel for the database and how it's going to work in the long run if you use actual real data in it, but just don't rely on it.
One thing that I kind of decided was that I don't really like the double click to open up this thing. I want to have it so that there's an open account button down here in the bottom. A, I don't use it that often. B, a lot of other users aren't going to necessarily know that you double click here to get the details like that. So I'm going to get rid of that. I like having it so that when I double click on the name of the account, it does that refresh recalculations thing. I'm just used to it. I like that.
So we're going to put down here an open account right above it. I'm going to slide this down. We're going to put open account above that. So you'll click on the account you want and then hit open account. I know it's kind of backwards to what I do in a lot of my databases, but again, I was using it yesterday and this is kind of how I want to do it. So this is what we're going to do. You're at my mercy. This is my database. I'm putting the Legos together the way I want them to be. You can do yours however you want. You don't have to do it. That's why you're learning how to do this stuff.
Go to all open account. The account button. BTGN. Is that my line? Add account button. Open account button. Right click. Event. This time we're going to do command open form. Account detail. F comma comma comma. Million where the account ID equals the account ID on this form. Again, you don't have to have the field on the form as long as it's in the underlying table. Close it. Close it. Close it. Open it. Region savings. Open account. That's my region.
As for the double click event, I'm going to set that equal to the double click event that's over here, which is just to be held. Copy. Account name. I'll put it in the after update as well. The date updates. For this double click event here, that's a good idea that I see this here because I forgot about that forcing that AC form edit. So we're going to take this stuff and move this down into my open account button. I forgot about forcing that AC form edit. Good idea. Then we can get rid of this. Save it. Then we can add here and this and that. Save. Close. Close.
Now if I open this, if I'm just running down my list and I want to update the region savings, I just double click and it updates today. Plus, I kind of don't like to have when you double click on this, it highlights the stuff. You come back to this form and that's all highlighted. There are tricks you can play to get away from that.
Next up is deleting an account. Generally, I am of the mindset that I don't like to delete anything, except for temporary data. If you've got, like, processing batches of emails and you send out 20,000 emails from your database, and you know, once it's done, maybe keep them around for a couple days. Then you can delete that. Fine. But as far as customer data, order information, all that kind of stuff, generally, I am of the mindset that you don't delete it, you hang on to it, and you just mark it deleted in the table - is deleted, or is active, or whatever you want to call it.
In fact, I've got a whole video on why it's a good idea not to delete data, especially important data. For example, a customer moves away or dies. You know you're never going to need this information again. You delete this customer record, well, now you got orders in the system that don't have a related customer, so there's problems. So you just mark a customer inactive. You're never going to see them again, they don't show up on your reports, that kind of stuff.
I've got another whole video that you can use to prevent users from deleting records. I disable the built-in delete, like allow deletions, which we're going to do some of that today for this database, and you provide them with your own delete button, which is essentially what we're going to do today. Go watch this if you want to learn more, but I'm going to show you the nuts and bolts of it today.
I like to also provide my own delete button for the user to use instead of them getting used to clicking over here and hitting delete because I can control exactly what happens. There are on delete events in the form, but they're very weird. I don't like using them. Some of them are like, before delete confirmation, after delete confirmation. I turn off delete confirmations. I don't like them. I think they're annoying. So I like to control the delete process myself with a button. I don't usually let users delete stuff on their own by using the normal methods.
For prerequisites for today's video, before we get into the delete stuff, I want you to go watch this message box. Are you sure you can use a message box to display information on the screen, but you can also use it to ask the user if they're sure, yes or no. Cancel, that kind of stuff. So go watch this. We're going to use this when they hit delete. We're going to go, are you sure you really want to delete this account?
Then I want you to make sure you understand referential integrity, which is relationships between tables and special rules. For example, we're not going to allow them to delete an account if they have any active transactions for that account. Because remember, we're going to have a transactions table too over here. This guy has got our transactions in it. We're not going to allow them to delete an account if there are transactions. You could go the other way and say, okay, we can cascade delete those transactions. Cascade delete is when you delete a table and then it deletes the records in the related tables. That's very dangerous. I don't like that.
So for this database, I'm just going to say you can't delete an account if there are any transactions in the transaction table. One thing, you can't delete an account type if it has accounts. You can't delete checking from the account type table as long as it's got accounts. You have to delete the children first. Like with an order, you can't delete an order unless you delete the details. You can set up cascade delete so that if you do delete the order, it automatically wipes out all the line item details. But again, that's dangerous, so you have to be careful.
Now, when it comes to referential integrity, controlling the integrity between these, you have to do it in the back end. You can't do it here in the front-end table. So once again, let's take a trip to the back end table and you can see why normally I do this when I'm finished with the database - splitting the database, that is. Sometimes I set up the relationships when I'm almost done too - the global relationships, which I don't use that often. But it's under Database Tools, Relationships. We're going to bring in account type. We're going to bring in account. We're going to bring in transactions.
The only time I do this is to enforce referential integrity. Account type ID to here, we're going to enforce referential integrity. Don't pick cascade update or delete. Cascade update is stupid. You're never going to use it. Cascade delete says if you delete the account type, it's going to delete all of the accounts. That's bad. You don't want that. So hit Create. There's that relationship.
I will do the same thing, going from account to transaction. So account ID down to here, enforce referential integrity. That way you can't delete an account if there are transactions under it. You have to delete the transactions first. Again, if you enforce cascade delete here, if you deleted the account, it would delete the transactions. If you've got cascade deletes between all three of these, if you deleted an account type, all of these are gone and all of the related transactions are gone. That's why this is dangerous. Be careful with it.
Save that. Close it. Now, I'm going to have to do it in the other two company tables. In fact, I'm going to get rid of company B at this point. Bye. I think I'm going to rename company A to sample data. Because we know this works now, all I have to do is maintain that and risk data. I'm going to go do that right now and I'll be back in a second.
Now when I open up this again, there is our error message. Can I find Company? Please relink the backend file. I renamed it at the table level, so this is an admin thing. You're going to have to go into your company T. I'm going to get rid of company B. I'm going to put Sample Data in here. Now we can rename this to Sample. Close it and then we'll do our relink. Drop it down, Sample Data, and we're good. Beautiful.
So now with referential integrity involved here, if I go to the account type table and I try to delete credit, it says you can't do it. This record can't be deleted because account T includes related records. So that's protected.
So about that delete button, let's go to the accounts and I want to put a delete button. I'm going to put it on the account detail for this guy, so it makes it even one step harder to delete an account. You can't just do it from here. Got to open it up. I see. I got used to doing that in this database by double clicking here, open account. We'll put the delete button. Let's put it over here on this side so you don't accidentally click it.
The first thing I'm going to do is make sure that Allow Deletions is off on this form. Go to Data. Allow Deletions is No. We already did that and we'll make sure that it's on the other form as well. Design View. Here, Allow Deletions is No. Allow Additions is going to be, yeah, leave that on. That's fine. We turned off the record selector so they can't just go to a new record. But this button will take the new record, so that's fine. Allow Edits obviously has to be on, but turn Allow Deletions to No so they can't delete it by any other means aside from using your button.
Speaking of your button, copy one of these guys, Control C, Control V. Put it over here. Delete. Properties. Delete button. Right click. The event.
First thing we're going to do is see if you don't like the default messages that Access gives. It says the referential integrity message, right? If you don't want that stock Access message, you can figure out yourself if there are related records. You can look into the transaction table and see if anybody in here has that account ID. That's simply a DLookup.
So in here, first check for related transactions. It's just more user friendly than relying on the stock Access messages, right? So we need an ID. You can count them or you can look up one. It doesn't matter. That's the same thing. I think DLookup is faster. So ID equals NZ DLookup. What are we looking up? A transaction ID in the transaction table where the account ID equals the account ID on the current form. We have NZ did, so comma zero.
If it looks in that table and it sees a transaction with the current account ID, it'll return that ID. Otherwise, it'll return a null, which turns into zero. So if ID is not zero, then there are transactions for this account. Now we can tell them, message box: You cannot delete this account because it has transactions. Delete those first. Maybe critical and the defaults.
Can't really do anything about it. If you wanted to be nice, you could program in here and say there's a transaction. Would you like me to delete those now? That's a little better than just doing a cascade delete. You can handle that here in code with a little SQL statement. But I'm not going to. I'm going to make them go and have to delete the transactions manually.
So at this point, we'll just Exit Sub.
I forgot one more thing we got to check too. We don't want to allow them to click this button if they're on a blank new record and it has no ID. So if IsNull(AccountID) then just Exit Sub. You could hide the button. You could give an error message. I just make the button not work. You just come in here. Close it. If they go to Add Account and at this point hit Delete, just nothing will happen. You could hide the button; I don't care at this point. Those are little things that I add in Version 2 - those little tiny tweaks. For now, I just want to make this thing work, then we'll make it pretty. Well, it is pretty already because I'm building it.
Coming in here, making a delete button. We check to make sure there are no related transactions. If we get here and there's no related transactions, this is okay, now we can ask them if they're sure.
I'm going to put this in a Reply as a long. Reply equals MsgBox: This will delete the current account. Are you sure? Question. I like vbYesNoCancel. I've mentioned this in a couple of other videos. Basically, if I give them Yes or No and they're not sure, they panic. But if they see Yes, No, and Cancel, Cancel always tells people No, get out of here, forget it. That way, they don't even have to think about answering the question. If they accidentally click the button, they just know to hit Cancel.
At this point, if they answer anything other than Yes, we're going to just Exit Sub. If they say No, Cancel, Exit Sub. So if Reply is not equal to vbYes, then Exit Sub.
At this point we've cleared all the hurdles. We can delete the record. How do you delete a record? The normal command is DoCmd.RunCommand acCmdDeleteRecord. The other one's for deleting objects; you don't want to use that.
Let's see what happens. Save it. So back here, I don't want to delete this one. Let's make a bogus one. Add an account. XXX. Save it. It's in the table now. Let's open it back up again. Open account. Delete. This will delete the current account. Are you sure? Yes. And there it goes.
Now, notice we should probably close the form after that point. Let's close the form. Then we're going to want to requery this guy behind it again, just like we did when we added the account. Where's the Add Account code? Requery it and close it. I'm going to put it here to delete. Save it.
Let's do that again. Add account. Add account. Save it. Click. Open. Delete. Sure. Yeah. There we go. Perfect.
Let's put a transaction in the table for something and test that too. Add account. I got mail. Yay.
So this guy is account number, which we don't see anywhere. Notice how I'm not displaying auto numbers anywhere. I usually display auto numbers in my beginner databases, or for you, the developer, they're handy to see too. In my finished end-user databases, I usually don't display those. I've got lots of reasons why, but they don't need to see them. If you need your own kind of account number, you can make your own. Here's a video you can watch on that. Why you'd want to make a customer code, some kind of unique code. You don't want to give consecutive customer numbers to people. So that's the reasons why. Go watch this video for details.
One more thing I want to mention. Sometimes this command doesn't work. Sometimes you have to use a little bit of SQL to delete records. It all depends on how it's set up. In this particular database, this works just fine. Sometimes you need to use a little delete query. But we don't need to today. If it comes up later, we'll get through it then.
So 28 is the autonumber for this new account. Why? Because I added and deleted a bunch of them in the middle when I was testing and playing and stuff, like that X and the Y and the other one. Does this matter? No. This number is not for you. You shouldn't care what that ID is. It's completely out of your brain. Only is for Access to create those relationships. I get asked this one a lot. Oh, the QR code is there. There we go, I can see the QR code.
I get asked this one a lot. I get all kinds of people going, how do I add an auto number in the middle? I miss a deleted customer. No, you don't. Yes, there are ways to do it. Yes. But these are not numbers that are for you. They're for Access. So watch this for more.
We're going to create a transaction for account 28 just to test this and see if it's working.
Transactions. Sample transaction, whatever. Account 28. It's for $1 on this day. Now I should not be able to delete that account now.
Open account. Delete. See? Can't delete this account because it has transactions. Delete those first.
Let's get rid of Microsoft Access. I try to avoid that. Not that I don't love Access. I love Access. But a lot of people, there's a bias where they don't want other people to know that they're using Access. I've got a whole video on how you can make it so you don't look like you're using Access. At least because I did those first. Let's do this. Cannot delete. Save it.
One more thing I think I'm going to do is, because I open accounts a lot more than I add them and these buttons are very similar, I think I'm going to move the Add Account button over here on this side, keep Open over here by itself. Just because I want to come over here and click on it. I'm going to put all the stuff that I'm not going to use as often over here. It's just a visual thing. I'm sitting here almost like I have to take a half a second more to think about which button I want to click on, when this is the one I want to use most of the time. I'm going to add more stuff over here later on. That's the seldomly used stuff. But this guy is going to be right, maybe even make it bigger like that. Now it's going to look a little more open account. Here, open. Click on this. Let's get rid of that transaction. We never waited to delete transaction. We don't know transaction before me. We're getting there, a little bit at a time.
Open account. Delete. Are you sure? Yup. We're good. We're golden.
Before we go, I just finished the extended cut for this video. What we did was, and I'll talk about this in a few minutes, we worked on the sort order and made some buttons here to move accounts up and down. I have a couple of off-camera notes. They're off-camera for you, but I did them in the other class.
First, I changed the tab order on the account details page. This thing here. Because it was going tab, tab, tab. Let me account details page. This thing here. Because it was going tab, tab, tab, tab, tab, tab. Changing tab order is real simple. Design view. Form design. Tab order is right there. If you don't know how to do that, go watch my tab order video. I'll put a link down below.
Also, I changed the cycle, which is here. Other cycle to current record. That way, when you tab, tab, tab, tab, tab, it doesn't go to the next record. As we saw earlier, it stays on the same record. But you don't want to go to a blank record because that, no, that's bad. That's also covered in my tab stop order. I keep missing the QR codes. The tab stop, tab order stop cycle, that's all covered in the same video. Go watch that if you don't know what those things are.
One thing I also did was I changed the account type sort order to double. The reason why I did that was because in the extended cut, we're working on this sort order. What we're doing is, let's say I want to move Fidelity up here after PayPal before Regions. If I put one in there, or two, you're not really sure whether it's going to resort above or below PayPal. Let's say I put it two in there. It might go above regions. It might go below it. In that particular case, it went above it, which is fine. But if I want to be able to specifically put it exactly where I want it to go, then I need to be able to use like 6.5. Now when it resorts, it sticks it in that right spot.
What you just saw was it's resorting automatically in the background. So I can take this guy and I can say, put it in position 1.5. It sticks it right there. That resorting is what we covered in the extended cut. Also, click here. We can now go down, down - look at that - it moves it, resorts it automatically in the background, and puts you right back on the same record. That's all covered in the extended cut.
Extended cut is where I cover all the really cool bells and whistles, the nifty stuff. The stuff that takes you from an Access user to a professional Access developer, the little things that really make your project stand out. So check it out - extended cut.
Silver members and up get access to all of my extended cuts, and as of today folks, what is it? It's July of 2023. Silver membership is $5.99 a month. That's it for the whole month, and you can watch all of my extended cut videos. I think there's like 300 of them now. You could be watching for years. You can watch like one a day. It'd take you a while. Check it out.
Of course, Gold members can download these databases, and members get a free class every month on my website for my full course. So yeah, it's definitely worth it folks.
That's going to do it for part six. I'll see you again for part seven soon.
That'll be your TechHelp video for today, folks. I hope you learned something. Guess what's coming up next? Live long and prosper. That's right, that's my sign off. See you next time.Quiz Q1. What is the primary reason for setting up referential integrity before enabling the deletion of accounts?
A. To prevent accounts from being deleted if they have transactions
B. To enable users to delete multiple accounts at once
C. To allow cascading updates to related tables
D. To automatically remove transactions when deleting an account
Q2. Why does the tutorial recommend not relying solely on Access's built-in error messages for deletions?
A. They are too detailed and slow down performance
B. They are unclear and not user-friendly
C. They always include a warning sound
D. They prevent any deletions from happening
Q3. What is the recommended way to handle accounts that should no longer be visible rather than deleting them?
A. Delete them permanently from the table
B. Mark them as inactive or set an 'is deleted' field
C. Move them to an archive table and remove all references
D. Change the account type to hidden
Q4. What happens if you enforce cascade delete between Account Type, Account, and Transactions tables?
A. Deleting an account type deletes only that record
B. Deleting a transaction deletes the account type
C. Deleting an account type deletes all related accounts and their transactions
D. Nothing, cascade delete has no effect on relationships
Q5. Which user interface design change was made regarding how users open account details?
A. Added a right-click context menu for opening
B. Added a dedicated 'Open Account' button instead of double-clicking the list
C. Required users to type in the account name manually
D. Allowed accounts to open automatically on single click
Q6. When setting up the delete button, what was advised regarding the 'Allow Deletions' property on the form?
A. It should be set to Yes for all forms
B. It should be set to No, so deletions are only possible through your custom button
C. It makes no difference and can be ignored
D. It should be set to Yes on the detail form only
Q7. What is the purpose of the DLookup function in the delete button's code?
A. To find the oldest transaction date for the account
B. To check if there are any transactions related to the current account
C. To retrieve the account's last modified date
D. To count the number of all accounts in the table
Q8. How should the application respond if the user tries to delete an account that has related transactions?
A. Allow deletion but display a warning after the fact
B. Prevent deletion and display a message telling the user to delete transactions first
C. Automatically delete all related transactions (cascade delete)
D. Hide the account from the user's view
Q9. Why is using cascade delete generally discouraged in the approach described in the tutorial?
A. It requires too much manual coding
B. It may result in accidental loss of important data
C. It has no effect in Access databases
D. It disables data entry forms
Q10. What additional safeguard is included in the delete button's code to prevent accidental deletion?
A. Requires administrator approval for all deletions
B. Checks if the current record is blank or new and ignores delete requests in that case
C. Asks for the user's password before deleting
D. Logs every deletion to a separate audit table
Q11. What type of message box is used to confirm the deletion of an account?
A. Message box with only 'OK'
B. Message box with 'Yes', 'No', and 'Cancel' options
C. Message box with 'Retry' and 'Ignore' options
D. Silent deletion with no message box
Q12. After deletion, what should happen to the forms to ensure the user sees updated data?
A. Close and reopen the entire database
B. Refresh the current record, requery the underlying list form, and close the detail form
C. Do nothing and expect the user to manually refresh
D. Prompt the user to press F5 to refresh
Q13. What is the main reason for not displaying autonumbers (such as Account ID) to end users?
A. They are confusing and not useful to end users
B. They are too difficult to retrieve from the database
C. They might reveal confidential information
D. They are required to be hidden by Access
Q14. Which table design modification allows for more precise control over where new accounts appear in the sorted list?
A. Changing the sort order field to a double type
B. Making the primary key a text field
C. Adding more indexes to the transactions table
D. Enabling cascade update on relationships
Q15. What is meant by the 'tab order' modification described toward the end of the video?
A. Changing the order in which records are displayed alphabetically
B. Adjusting the sequence of controls that are selected when pressing the Tab key
C. Enforcing referential integrity between forms
D. Hiding certain fields from users
Q16. Why is it a good idea to use real data alongside test data while developing a new database, according to the video?
A. It eliminates the need to do further testing
B. It helps identify usability issues and necessary changes more effectively
C. It allows immediate full deployment to all users
D. It speeds up the database performance significantly
Q17. What is the potential problem with letting users delete data solely via built-in Access methods?
A. Users may forget how to navigate the Access interface
B. There is no way to stop them from deleting critical data by mistake
C. Deleting data this way is much slower
D. It is impossible to check for related records first
Q18. In the context of the video, what is the danger of allowing automatic cascade update of foreign keys?
A. It disables cascade delete options
B. It offers no benefit and can cause confusion
C. It might change key values unexpectedly, potentially breaking relationships
D. It makes queries run slower
Q19. What does the 'Cycle: Current Record' option do for navigating forms?
A. Allows users to cycle through all records when tabbing
B. Takes the user to the new record after finishing the current one
C. Keeps the tab key navigation limited to fields within the current record
D. Locks the form to prevent further edits
Q20. According to the tutorial, what is a good programming practice after successful deletion of a record via your custom button?
A. Offer to let the user immediately undo the delete
B. Automatically add a new blank record to replace it
C. Close the detailed form and refresh or requery the list form
D. Change the color of the deleted record to red
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-A; 14-A; 15-B; 16-B; 17-B; 18-C; 19-C; 20-C
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 how to build a delete button in your Access database so you can remove accounts safely while preserving your data integrity. It is important not to let users delete records too easily, especially when those records are related to other data, such as transactions, because this could disrupt the integrity of your database.
We start by addressing referential integrity. Before allowing anyone to delete an account, you need to ensure that there are no associated transactions for that account. If transactions exist, deleting the account could cause inconsistencies or orphaned records. Instead of relying on Access's default error messages, which are often unclear, we will implement user-friendly checks in our interface.
Let me point out that as I develop these lessons, I sometimes make minor adjustments off camera – small tweaks to improve usability or accommodate real-world data. I always make note of these changes so you are not confused by differences you might spot in my demonstration database. For example, I made the account fields and detail forms a bit wider to better accommodate longer account names and more visible notes. I also added a notes field so you can quickly see any notes tied to a selected account in the header or footer area, reflecting whichever record is currently active.
Another adjustment was to the save process in the detail form. After saving a record, I added a step to refresh the form, ensuring the data is immediately visible in your account list without needing to manually refresh. Without this, you might not see added records right away, even though they're in the table.
Next, I considered the way accounts are accessed. Instead of a double-click to open account details, I decided to add an "Open Account" button beneath the list. This change is meant to improve usability, especially for people who might not realize that double-clicking opens the details. Double-clicking now performs a simpler update function, like refreshing calculations. I encourage you to build your interface in a way that suits your workflow. That is part of the learning experience.
Now, let's discuss deleting accounts. My general philosophy is to avoid deleting data whenever possible. In many cases, it is better to mark a record as inactive or deleted, rather than physically erasing it. This helps maintain historical relationships between records, such as past orders tied to customers who are no longer active. That said, there are legitimate reasons to delete records, especially temporary data, but for important records like accounts, extra safeguards are necessary.
For this reason, I advocate providing your own delete button rather than allowing users to use Access's default deletion mechanisms. This approach gives you full control over the deletion process, including the ability to check for related records, display custom confirmation prompts, and prevent accidental data loss.
A few things I recommend reviewing before building your custom delete button: first, ensure you know how to use message boxes to prompt users for confirmation. This lets you ask "Are you sure?" before carrying out potentially destructive actions. Second, you must understand referential integrity, which refers to relationships between tables and rules that prevent orphaned records. In our scenario, you must not allow deletion of an account that still has transactions.
Rather than enabling cascade deletes, which could automatically wipe out all related transactions when an account is deleted, I recommend keeping referential integrity enforced without cascading. That way, users must explicitly delete transactions before an account can be removed, which reduces the risk of accidental data loss.
To enforce these relationships, use the database tools in Access to set up referential integrity between account types, accounts, and transactions in your backend tables. Do not choose the cascade update or cascade delete options. This ensures that deleting a parent record with associated children will be blocked and helps maintain data consistency.
After setting up your relationships, you might have to relink your backend tables if you make structural changes. Keep your objects clearly labeled so you can easily identify them when making adjustments.
At this point, you are ready to build your delete button. I recommend disabling Allow Deletions in your forms so users cannot delete records except through your designated button. This way, you can manage the process and intercept errors more gracefully than Access does natively.
The actual delete button should start by checking whether the current record is blank or has no ID, and if so, simply do nothing. You could also hide the button or display a message, but for now, disabling it is sufficient. Next, check for related transactions by searching for any records in the transactions table with the same account ID. If any are found, display an informative message telling the user that the account cannot be deleted until all related transactions have been removed.
If there are no related transactions, prompt the user with a confirmation message box offering Yes, No, and Cancel options. Canceling out provides a comfortable escape for uncertain users. Only proceed if the user selects Yes.
Once you've passed all the checks and received user confirmation, use Access's built-in command to delete the record. After deletion, close the detail form, then requery the list on the main form to show the updated data. This helps keep everything in sync visually for the user.
Test the logic by adding new accounts, deleting them, and verifying that the protection works when transactions exist. Remember, if you use autonumbers for IDs, they may not be consecutive thanks to adding and deleting records during testing, but this is not important for users or most applications.
In step with these interface changes, I also made refinements to the tab order and cycling behavior in the account details form, streamlining data entry. Modifying the sort order field to type double allows for precise positioning of accounts in the list, which is helpful when refining the order beyond simple integers. More on these enhancements is covered in the Extended Cut of this lesson.
In the Extended Cut, we explore how to adjust sort orders for accounts by using decimal values and buttons to move items up and down with immediate visual feedback. These refined user interface features transform your project from a basic database into a polished, professional tool.
Silver members and above have access to all extended cut videos, where I share these advanced techniques. Gold-level members can also download the databases used in my lessons, and free full classes are available to all members each month.
That concludes part six. Be sure to check out my website for the complete video tutorial, including step-by-step instructions for everything we covered here. Live long and prosper, my friends.Topic List Adding a delete button to delete accounts
Setting up referential integrity to prevent deleting accounts with transactions
Widening account fields on forms
Adding and displaying a notes field on the account form
Syncing additional info fields using form header/footer
Ensuring account detail form refreshes with Me.Refresh and requery
Creating an Open Account button for selecting and opening account details
Reassigning double-click events to update account information
Disabling built-in deletions and providing a custom delete button
Checking for related transactions using DLookup before deletion
Displaying a confirmation message box before deleting an account
Handling the delete button on new/unsaved records
Deleting a record with DoCmd.RunCommand acCmdDeleteRecord
Automatically closing and requerying forms after deletion
Testing account deletion with and without related transactions
Preventing deletion of account types with related accounts
Renaming and relinking backend data tables after changes
Rearranging and customizing button layout on the form
Disabling Allow Deletions property on forms except for the custom button
Preventing cascade delete in table relationships
Explaining the use of auto numbers as primary keys only for relationships |
