Access Developer 46
Building a Microsoft Access Undo System for Orders
Welcome
Welcome to Microsoft Access Developer Level 46, brought to you by Access Learning Zone. I'm your instructor Richard Rost.
Today's lessons are going to cover building an undo system for your Microsoft Access database. Access allows you one undo, but what if you're working on an order and you want to undo the last three or four things that you did? An edit, a delete? That's what we're going to cover in today's class.
This is going to involve building a change log so we can track exactly what changes are made to the order, then an undo button. We'll have to do three separate cases: undoing edits, additions, and deletions. They're all separate. We'll make a custom delete button. We've done that before, but there are some subtleties with this one.
Then we will talk about two significant problems that come up. One, when you delete a record from a table and then restore it with its Autonumber, a problem comes up, and we'll talk about how to fix that. We'll also see how CurrentDB.Execute doesn't ever really give you error messages, and we'll deal with that problem too.
Resources
Topics Covered
In Lesson 1, we are beginning our undo system. We are going to build a change log table so we can track the undos and the edits in the table. We will write the code to save those changes into the log table. We will build our undo button and begin writing the code to restore the edited record when the user clicks undo.
In Lesson 2, we will continue with the undo system. We will finish undoing an edited record, and once the change is made, we will move the focus to that record so the user can see that it was the last one modified and what was undone.
In Lesson 3, we will track what type of change was made, whether it was an edit, an addition, or a deletion. We will build a custom delete button to handle deletions instead of relying on the built-in events for delete. Then we will handle undoing a record deletion by adding it back to the order.
In Lesson 4, we will address two problems. One is the issue that occurs with auto numbers when you insert an Autonumber back into the table. We will discuss how that is fixed. The second issue involves CurrentDB.Execute and how it does not provide error messages. We will explore how to deal with that problem.
In Lesson 5, we will refine the undo system by showing or hiding the undo button based on whether there is anything to undo. For example, if you open a new record, you do not want to see an undo button when there is nothing to undo. We will also make the undo button show the last action item, such as whether it was an edit, an addition, or a deletion.
Enroll Today
Enroll now so that you watch these lessons, learn with us, post questions, and more!
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access, access 2016, access 2019, access 2021, access 365, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, undo system, change log table, save changes code, undo button, restore edited records, undo additions, undo deletions, restore deleted records, auto number issues, custom delete button, CurrentDB.Execute errors, error handling in VBA, Access record recovery, Access transaction log, VBA undo functionality
Intro In this lesson, you'll learn how to build an advanced Undo system for your Microsoft Access database. We'll walk through creating a change log to track edits, additions, and deletions, then set up an undo button that can handle each type of change. You'll also see how to manage issues with autonumbers when restoring deleted records and address challenges with CurrentDB.Execute not providing error messages. Finally, we'll enhance the interface by showing or hiding the undo button based on user actions and displaying details about the last change. This is Developer Level 46.Transcript Welcome to Microsoft Access Developer Level 46 brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today's lessons are going to cover building an Undo system for your Microsoft Access database. Access allows you one undo, but what if you're working in an order and you want to undo the last three or four things that you did, such as an edit or a delete? That's what we're going to cover in today's class.
It's going to involve building a change log so we can track exactly what changes are made to the order. Then an undo button will have to do three separate cases: undoing edits, additions, and deletions. They're all separate. We'll make a custom delete button. We've done that before, but there are some subtleties with this one.
Then we will talk about two significant problems that come up. One, when you delete a record from a table and then restore it with its autonumber, a problem comes up and we'll talk about how to fix that. We'll also see how CurrentDB.Execute doesn't always give you, or doesn't ever really give you, error messages, and we'll deal with that problem too.
I like to say that the prerequisite for this class is the one before it, but Developer 44 and 45 dealt with customizing the ribbon. I know a lot of you probably will never need to do that, so I'm not going to call these prerequisites. However, you really should have taken Developer 16. That's where I cover recordsets. There will be a lot with recordsets today and, of course, SQL. You should know your SQL, especially how to write SQL statements: insert, update, and delete statements.
If you have not taken those courses, I do have a couple of free TechHelp videos to cover the very basics. Here's one on recordsets. And here's one on SQL with Access. This one only covers SELECT statements, but it gives you the gist. I cover all of this information in all the previous Developer lessons. That's why I say take all these classes and don't skip levels, and then you won't miss anything.
It's currently December 2024, so the current version of Access is Access 2024, but in my recommendation, get an Access 365 subscription. I didn't update the picture here, but you get the point. The lessons today and all this material should work going back as far as at least Access 2007, so this information is pretty solid. If you're using any of the modern versions of Access, they should work. It might even work going back as far as 2003, but I wouldn't try it.
Of course, if you have questions, just scroll down to the bottom of the page you're watching this on and you can post them right there. If you have questions beyond that, be sure to visit the Access forum on the website and chat with the moderators. We have great moderators on the website.
Let's take a closer look at exactly what's covered in Access Developer 46.
In Lesson 1, we are beginning our undo system. We're going to build a change log table so we can track the undoes and track the edits in the table. We'll make the code to save those changes into the log table, build our undo button, and begin writing the code to actually restore that edited record when the user clicks undo.
In Lesson 2, we are continuing with our undo system. We're going to finish undoing an edited record, and then once the change is made, we'll move the focus on that record, so the user can see that was the last one that we modified and that's what was undone.
In Lesson 3, we're going to track what type of change it was, whether it was an edit, an addition, or a deletion. Then we'll build our own custom delete button because I like to handle it myself instead of relying on the built-in events for delete. Then we'll handle undoing a record deletion by adding it back to the order.
In Lesson 4, we're going to address two problems that will come up. Actually, one has already come up, we just didn't realize it. One is the problem that happens with autonumbers when you insert an autonumber back in the table. We'll see how that is fixed. We'll also talk about a problem that happens with CurrentDB.Execute and it not telling you that there's a problem. So that's coming up in this lesson.
In Lesson 5, we're going to show or hide the undo button based on whether or not there's something to undo. If you open up a new record, you don't want to see an undo button when there's nothing to undo. Then we'll make the undo button show what the last action item was. Was it an edit? Do you have to undo an add? Do you have to undo a delete? That will be pretty cool.
We're going to cover that in Lesson 5.Quiz Q1. What is the primary objective of Microsoft Access Developer Level 46?
A. Learning how to write SELECT SQL statements
B. Building a multi-level undo system for Access databases
C. Customizing the Access ribbon interface
D. Importing external data into Access
Q2. Why do we need to build a change log when implementing an undo system in Access?
A. To permanently delete records from the database
B. To track changes so edits, additions, and deletions can be undone
C. To increase the size of the database
D. To automate form creation
Q3. What are the three separate cases the undo button must handle?
A. Data imports, data exports, and security changes
B. Edits, additions, and deletions of records
C. Linking tables, hiding forms, and changing queries
D. Filtering, sorting, and compressing data
Q4. What issue arises when restoring a deleted record with an autonumber primary key?
A. The database shuts down unexpectedly
B. Duplicates are automatically created
C. The original autonumber may not be restored correctly
D. Referential integrity is always broken
Q5. What is one limitation of using CurrentDB.Execute for SQL statements in Access?
A. It automatically renames all fields
B. It does not provide error messages if something goes wrong
C. It can only delete data
D. It only works with Access 365
Q6. Which previous Developer lesson is considered essential to fully understand Level 46?
A. Developer 44 (Customizing the ribbon)
B. Developer 45 (Customizing the ribbon)
C. Developer 16 (Recordsets)
D. Developer 10 (Forms and Reports)
Q7. Which SQL statements should you be comfortable with for this lesson?
A. SELECT, GROUP BY, HAVING
B. INSERT, UPDATE, DELETE
C. ALTER, DROP, CREATE
D. MERGE, SPLIT, RENAME
Q8. What version of Access is recommended for best compatibility with these lessons?
A. Access 2003
B. Access 2007
C. Access 2024 or Access 365 subscription
D. Access 2013
Q9. In which lesson do you build the initial change log table and undo button?
A. Lesson 1
B. Lesson 2
C. Lesson 3
D. Lesson 5
Q10. What additional user convenience is added to the undo button in the final lesson?
A. It changes color based on the data
B. It displays the last action type that can be undone
C. It automatically deletes the last record
D. It always stays disabled
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-A; 10-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 video from Access Learning Zone focuses on building an Undo system for your Microsoft Access database. Microsoft Access gives you a single undo, but if you're in the middle of processing an order and you realize you'd like to undo multiple recent changes—like a series of edits or deletions—Access does not provide this natively. That's exactly what I'm going to teach you how to create in today's lesson.
The core of this solution is a change log, which will record every significant change made to your data. This approach makes it possible to undo not just the last action but multiple recent changes. We'll go through the process of designing this log and discuss how our custom undo feature will deal with three situations: undoing an edit, undoing additions, and restoring deletions. Each of these scenarios needs separate logic. For deletions, a custom delete button will be made. Although we've designed delete buttons before, there are particular needs for this project that we'll address.
There are some significant issues that arise as we build this system. The first is tied to autonumber fields. When you remove a record and need to restore it using its original autonumber, Access presents a challenge. I'll explain how to work around this. The second issue involves the lack of feedback from CurrentDB.Execute when problems occur, which can make troubleshooting harder; I'll show you strategies for handling these errors more effectively.
As for prerequisites, you do not have to complete Developer Levels 44 and 45, since those focused on customizing the ribbon and might not be relevant for everyone. However, you should have completed Developer Level 16, where I cover recordsets in detail, because a lot of what we cover today depends on working with recordsets and SQL. Comfort with writing SQL statements, especially for inserting, updating, and deleting records, will make this lesson much easier to follow.
If these concepts are new to you, I have some free TechHelp videos that cover the basics. One video introduces recordsets, and another covers the basics of SQL in Access. That SQL video sticks to SELECT statements, but it serves as a good starting point. All of these foundational skills are covered throughout the earlier Developer levels as well, so I always recommend working through the lessons in order to avoid missing important background information.
Currently, it's December 2024, and Microsoft Access 2024 is the latest version out there. Personally, I suggest getting an Access 365 subscription for the best experience, but all of the material covered here should work in any version from Access 2007 onward. If you are using a modern installation of Access, you should be fine. While a lot of this may even function in Access 2003, I do not recommend trying it on versions that old.
If you have any questions about the lesson, you can ask them in the comments section on my website. For questions not directly tied to the current video, check out the Access forum and talk to the moderators there—they're an excellent resource.
Let's break down what's covered in each lesson of Access Developer 46:
In Lesson 1, we begin constructing the undo system by building a change log table that will record each change for tracking and reversal purposes. I'll demonstrate how to write the code that saves edits to this table, build an undo button, and start the code needed to restore a previously edited record when undo is clicked.
In Lesson 2, we continue finalizing the code that undoes changes to an edited record. We'll also update the user interface so that after making an undo, focus is shifted to the restored record and the user can see exactly what was changed.
Lesson 3 focuses on tracking the type of each change—whether it was an edit, an addition, or a deletion. We'll also create a custom delete button, which lets me control exactly how deletions are handled rather than relying on Access's standard delete events. This lesson also covers the technique for undoing deletions by re-inserting the record that was removed.
In Lesson 4, we deal with two special problems. The first is related to re-inserting records with the same autonumber when undoing deletions, which does not work by default. I'll show you the workaround here. The second is addressing the challenge when CurrentDB.Execute fails silently. I'll guide you through techniques to detect and handle errors so they do not go unnoticed.
Finally, in Lesson 5, we'll refine the interface so that the undo button is only visible when there is actually something the user can undo. For example, you would not want the button to appear when you are looking at a brand new record. We'll also configure the undo button to display information about what the last action was, letting users know whether the next undo would reverse an edit, an addition, or a deletion.
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 Building a change log table for undo tracking
Saving changes to the log table using VBA code
Creating an undo button for restoring edited records
Completing undo functionality for edited records
Setting form focus to the recently undone record
Tracking the type of change: edit, add, or delete
Building a custom delete button for records
Handling undo of record deletions by reinstating records
Solving autonumber issues when restoring deleted records
Addressing lack of error messaging with CurrentDB.Execute
Dynamically showing or hiding the undo button
Displaying the type of last action for undo (edit, add, delete) |
