Introduction

Welcome! Undo System: Change Log, Delete, Errors

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

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 Introduction
Get notifications when this page is updated

Intro

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.

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

Article

Welcome to this tutorial on building an advanced Undo system in Microsoft Access. By default, Access allows you to undo just one step. That means if you accidentally edit a field, you can hit Undo (Ctrl+Z) and revert that change, but no further. What happens, though, if you want to undo several actions, like multiple edits, additions, or deletions on a record? In this tutorial, I will guide you through the process of creating a robust Undo feature that allows for multiple undos, handles edits, additions, deletions, and overcomes some tricky issues along the way.

To begin, we need a way to keep track of every change a user makes in your application. This is where a Change Log comes in. A Change Log is simply a table in your database that records every time someone edits, adds, or deletes a record. Typical fields in your Change Log table might include LogID (autonumber), TableName, RecordID, FieldName, OldValue, NewValue, ChangeType (Edit, Add, Delete), DateTimeChanged, and possibly the UserID if you want to track who made the change.

Now, every time a user makes an edit in your form, you will use VBA code to capture the previous value of the field and any other details you need. When the change is committed, you save the details to the Change Log table. This is straightforward when you are handling edits because you know both the old and new values. For this, you can use the BeforeUpdate and AfterUpdate events on your form. For example:

Private Sub FieldName_BeforeUpdate(Cancel As Integer)
Me.OldValueStore = Me.FieldName.OldValue
End Sub

Private Sub FieldName_AfterUpdate()
Dim db As Database
Set db = CurrentDb
db.Execute "INSERT INTO ChangeLog (TableName, RecordID, FieldName, OldValue, NewValue, ChangeType, DateTimeChanged) " & _
"VALUES ('Orders', " & Me.RecordID & ", 'FieldName', '" & Me.OldValueStore & "', '" & Me.FieldName.Value & "', 'Edit', Now())"
End Sub

It is important to use your field and table names as appropriate for your application. You may also have to handle nulls or data types carefully.

For undoing additions or deletions, you need extra logic. When someone adds a new record, make an entry in the Change Log marking it as an Add, and store all the initial values. When someone deletes a record, you cannot use Access's built-in record deletion events directly, as they may not give you all the details you need. Instead, create your own delete button on your form with VBA code that first logs the record's current values into the Change Log, then issues the delete command. For example:

Private Sub btnDelete_Click()
Dim db As Database
Dim strSQL As String

' Log the current state of the record before deletion
db.Execute "INSERT INTO ChangeLog (TableName, RecordID, FieldName, OldValue, NewValue, ChangeType, DateTimeChanged) VALUES " & _
"('Orders', " & Me.RecordID & ", '', '', '', 'Delete', Now())"
' Now delete the record
strSQL = "DELETE FROM Orders WHERE OrderID = " & Me.RecordID
db.Execute strSQL
Me.Requery
End Sub

One tricky issue relates to Autonumber primary keys. When you delete a record and later want to restore it (for example, as part of an Undo Delete), Access by default will assign a new autonumber if you simply issue an INSERT. However, this breaks referential integrity and can cause problems, especially if other tables refer to that key. Instead, you have to write an INSERT statement that includes the deleted record's original primary key value, enabled by temporarily turning off Autonumber or using an identity insert where possible. Unfortunately, Access is more restrictive than SQL Server here, and there is no built-in way to do this unless your Autonumber is set to allow manual inserts (which is rare). A workaround is to redesign your tables to use a Long Integer primary key that you manage yourself, or, as a stopgap, only allow Undo for deletions where the related child records do not depend on that key.

When executing SQL with CurrentDb.Execute in VBA, you should also know that Execute does not pop up error messages by default. If there is a problem with your SQL, it simply fails silently unless you add error handling. For example:

On Error GoTo Err_Handler
CurrentDb.Execute strSQL, dbFailOnError
Exit Sub

Err_Handler:
MsgBox "Error: " & Err.Description

This way, you capture and display any issues that occur when you execute your database commands.

Once the Change Log and undo logic are built, you can create an Undo button on your form. This button should only be visible when there are actions that can be undone. For instance, when a record has just been edited, added, or deleted (and logged), the button appears; otherwise, it is hidden. In your Undo button code, you can query the most recent change for the current record from the Change Log. Depending on what type of change it was, you either restore the old value (in case of an edit), delete the just-added record (in case of an addition), or re-insert a just-deleted record (in case of a deletion), taking care to handle primary keys as described before.

For example, if the last change was an edit to a field called Quantity, your Undo button would run SQL like:

UPDATE Orders SET Quantity = [OldValue] WHERE OrderID = [RecordID]

For an undo of a deletion, you would build an INSERT statement to restore the record, using the values stored in your log.

Finally, you can enhance the Undo button by showing the type of change that will be undone next. For example, its caption could change to "Undo Edit" or "Undo Delete" based on the last logged change.

This system works on all modern versions of Access going back to at least Access 2007, and possibly even earlier, though I recommend using current versions like Access 2024 or an Office 365 subscription. Be sure to have a good familiarity with SQL and VBA recordsets, as most of this logic relies on those foundations.

With this undo system, you give users much more flexibility and safety in working with data, allowing them to reverse multiple changes without fear of permanently losing information. Implementing a Change Log and custom Undo routines takes some effort but greatly enhances your application's usability and professionalism. If you encounter issues, check your VBA error handling and review how your primary keys are managed for deletes and inserts, as these are the most common sources of problems. The end result is a much more robust and user-friendly Access database application.