Welcome

Welcome! Undo System: Change Log, Delete, Errors

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Developer Level 46. In this course we will build an undo system for your Microsoft Access database, allowing you to undo multiple changes such as edits and deletions by creating a change log table. We will cover making a custom delete button, discuss challenges with restoring deleted records that use autonumbers, and address how CurrentDb.Execute handles errors. You will learn to track different types of record changes and adjust the undo button display based on available actions. Prerequisites include familiarity with recordsets and SQL statements, as these skills are necessary for the lessons.

Navigation

Prev: D46
Next: Lesson 01

Keywords

Access Developer, undo system, change log, undo edits, undo deletions, undo additions, custom delete button, restore deleted record, autonumber problem, CurrentDb.Execute error, recordsets, SQL statements, INSERT, UPDATE, DELETE, show hide undo button

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 46, brought to you by AccessLearningZone.com. I am 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 are working on an order and you want to undo the last three or four things that you did, such as an edit or a delete? That is what we are going to cover in today's class.

It is 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 are all separate.

We will make a custom delete button. We have done that before, but there are some subtleties with this one. Then we will talk about two significant problems that come up.

One is when you delete a record from a table and then restore it with its autonumber, a problem comes up, and we will talk about how to fix that.

We will also see how CurrentDb.Execute does not always give you, or does not ever really give you, error messages. We will deal with that problem too.

The prerequisite for this class is ideally the one before it, but Developer 44 and 45 dealt with customizing the ribbon, and I know a lot of you probably will never need to do that, so I am not going to call these prerequisites.

You really should have taken Developer 16. That is 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, DELETE statements.

If you have not taken those courses, I do have a couple of free TechHelp videos to cover the very basics. Here is one on recordsets, and here is one on SQL with Access. This one only covers SELECT statements, but it gives you the gist.

I cover all of this stuff in all the previous Developer lessons. So that is why I say, take all these classes, and do not skip levels, and then you will not miss anything.

It is currently December 2024, and so the current version of Access is Access 2024, or, in my recommendation, get an Access 365 subscription. I did not update the picture here, but you get the point.

The lessons today - all this material should work going back as far as at least Access 2007, so this stuff is pretty good. If you are using any of the modern versions of Access, they should work. It might even work going back as far as 2003, but I do not know. I would not try it.

Of course, if you have questions, just scroll down to the bottom of the page you are watching this on. 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 guys and all the moderators. We have great moderators on the website.

All right, let's take a closer look at exactly what is covered in Access Developer 46.

In lesson one, we are beginning our undo system. We are going to build a change log table so we can track the undoes and track the edits in the table. We will make the code to save those changes into the log table. We will build our undo button, and we will begin writing the code to actually restore that edited record when the user clicks undo.

In lesson two, we are continuing with our undo system. We are going to finish undoing an edit, an edited record. Once the change is made, we will move the focus to that record, so the user can see, OK, that was the last one that we modified and that is what was undone.

In lesson three, we are going to track what type of change it was, whether it was an edit, an addition, or deletion. Then we will build our own custom delete button, because I like to handle that myself 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 four, we are going to address two problems that will come up, though actually, one has already come up, we just did not realize it. One is the problem that happens with autonumbers when you insert an autonumber back in the table. We will see how that is fixed. Then we will also talk about a problem that happens with CurrentDb.Execute and it not telling you that there is a problem.

In lesson five, we are going to show or hide the undo button based on whether or not there is stuff to undo. If you open up a new record, you do not want to see an undo button when there is nothing to undo. Then we will 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 are going to cover that in lesson five.

Intro

Quiz

Q1. What is the main goal of the lessons in Access Developer 46?
A. To build a multi-level undo system for Access databases
B. To design a ribbon interface for Access forms
C. To create new user login and password logic
D. To import data from Excel into Access

Q2. Which of the following changes are specifically addressed in the undo system covered in this class?
A. Only additions
B. Only edits
C. Edits, additions, and deletions
D. Only deletions

Q3. Why is it necessary to build a custom delete button for the undo system?
A. The built-in delete button cannot be customized for color
B. The built-in events for delete cannot be relied on for undo tracking
C. The built-in button automatically backs up records
D. The built-in delete event triggers form-level validation

Q4. What is one key issue discussed related to deleting and restoring records with autonumbers in Access?
A. Autonumbers are re-used automatically when restored
B. Autonumbers can cause referential integrity issues when re-inserted
C. Restoring a deleted record with its original autonumber can be problematic
D. Autonumbers cannot be imported from other databases

Q5. What is a limitation of using CurrentDb.Execute, as mentioned in the video?
A. It only works with SELECT statements
B. It will always display an error message
C. It does not provide error messages when something goes wrong
D. It is slower than DoCmd.RunSQL for all queries

Q6. Which prior knowledge or Access skill is especially recommended before taking Developer 46?
A. Macros usage
B. Report building
C. Recordsets and SQL (INSERT, UPDATE, DELETE statements)
D. SharePoint integration

Q7. Which Access versions should the material covered in this class work on, according to the video?
A. Only Access 365
B. Access 2024 and newer
C. Access 2007 and newer (possibly 2003)
D. Only Access 2010 and earlier

Q8. What is one feature planned for the undo button as mentioned in lesson five?
A. It will always be visible regardless of context
B. It will print a report when clicked
C. It will show or hide based on whether there are actions to undo
D. It will export the change log automatically

Q9. What is the purpose of the change log table introduced in lesson one?
A. To record user login attempts
B. To keep track of changes made to order records for undo purposes
C. To store form designs
D. To manage permissions for different users

Q10. After undoing an edit, what does the system do to help the user, as described in lesson two?
A. Deletes the restored record
B. Refreshes the main menu
C. Moves focus to the restored record
D. Closes the current form

Answers: 1-A; 2-C; 3-B; 4-C; 5-C; 6-C; 7-C; 8-C; 9-B; 10-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 video from Access Learning Zone is Developer Level 46, and I am your instructor, Richard Rost.

In this lesson, I will show you how to create an undo system for your Microsoft Access database that goes beyond Access's simple "one level" undo. If you are working on something like an order and need to roll back several recent changes, such as edits or deletions, this course will guide you step by step through how to make that possible.

To do this, we need to build a change log table. This change log is essential because it lets us track every modification made to any record in the order. From there, we will create an undo button, but it is not as simple as a single undo command. We have to be able to handle three specific actions: undoing edits, undoing additions, and undoing deletions. Each case involves a different approach.

Part of this process is building a custom delete button. While we have created these before, there are a few special considerations you need to keep in mind for this implementation. I will also explain two important problems that you will likely encounter during this project.

The first challenge involves restoring deleted records to a table that uses autonumber fields. When you try to reinsert a record with its original autonumber, some complications can arise, and I will show you the method to resolve this.

Second, I will explain an issue with CurrentDb.Execute in Access. It does not directly show you error messages if something goes wrong during a database operation, so I will cover ways to compensate for this limitation as well.

For prerequisites, the previous lesson is ideal, but if you have not watched Developer 44 or 45, you do not need to worry, since those focus on customizing the ribbon, which many people might not ever need. The real foundation you need here is from Developer 16, as that is where I introduce recordsets, which are used extensively in these lessons. Solid knowledge of SQL, especially how to write INSERT, UPDATE, and DELETE statements, will be essential.

If you have not covered those basics, I recommend some of my free TechHelp videos. There you will find an introduction to recordsets and another to writing SQL. The SQL video focuses on SELECT statements, but it covers the foundational concepts you will need.

Everything I am teaching here is covered throughout the Developer series, so if you have followed the course in order, you should be ready for this material.

Currently, as of December 2024, Access 2024 and Access 365 are the main versions out there. While I have not updated screen images for the absolute latest version, the content I present will work in Access versions going all the way back to 2007. In fact, it might run on Access 2003, but I recommend using something more current if you can.

If you run into questions as you are working through the material, you will find a space at the bottom of the course page to post them. For more in-depth conversations or help, the Access Forum on our website is an excellent place to interact with other students and our team of moderators.

Now, let me walk you through a summary of what you will learn in Access Developer 46.

Lesson one starts with setting up the undo system. We will create the change log table to record every significant edit, addition, or delete action taken. I will cover the process of saving each of these changes into the log and beginning the code required for the undo button. This includes handling how to restore an edited record.

In lesson two, we continue developing the undo system. I will show you how to finish undoing an edited record, and once that is done, we will make sure to move the focus to the restored record so that the user can see exactly what has changed and where.

Lesson three introduces tracking the type of change that occurred, be it an edit, addition, or deletion. Then, instead of relying on Access's built-in delete functionality, we will create our own custom delete button to offer a better and more controlled experience. Finally, I will explain how to restore a deleted record by adding it back to the order.

Lesson four focuses directly on addressing two common problems that come up. The first was already present earlier although it may not have been immediately obvious: the challenge of reinserting records with their original autonumber values. I will show you the proper way to address that. The other problem deals with the lack of error feedback from CurrentDb.Execute, and I will show you strategies to detect and handle errors when they occur.

Finally, lesson five is all about making the undo button more user-friendly. We will set things up so that the button only appears if there is actually something to undo. If the user is working with a new record, we do not want to show an unnecessary undo button. I will also make the undo button indicate what the last action was, whether it is an edit, addition, or delete, so that the user knows exactly what will be undone. That adds a nice touch of clarity for the end user.

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 an undo system for Access databases
Creating a change log table to track record changes
Saving changes to the change log table with VBA
Programming an undo button for edits, additions, and deletions
Restoring edited records using undo
Tracking the type of change: edit, add, delete
Creating a custom delete button in Access forms
Undoing a deleted record by adding it back
Handling issues with autonumber fields when restoring records
Addressing errors with CurrentDb.Execute not returning messages
Showing or hiding the undo button based on undo availability
Displaying the last action type on the undo button

Primary Topics

undo system, change log table, tracking edits, undoing edits, undoing deletions, undoing additions, recordsets, custom delete button, SQL statements, restoring records

Secondary Topics

Access versions compatibility, error handling, showing/hiding undo button