|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Access Developer 46 Lessons Welcome to Access Developer 46. Total running time is 1 hour, 21 minutes. Lessons
Database FilesLinks
Resources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn this lesson you will learn how to build a multi-level undo system for your Microsoft Access database. We will create a change log to track edits, additions, and deletions, then add an undo button that can handle each case. I will show you how to use recordsets and SQL to restore previous states, address issues with autonumbers when restoring deleted records, handle errors with CurrentDB.Execute, and make sure the undo button appears only when there are actions to undo. This lesson is designed for users familiar with recordsets and SQL in Access. This is part 46.TranscriptWelcome 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 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 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 really ever gives you, error messages, and we will 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. A lot of you probably will never need to do that, so I am not going to call these prerequisites. However, 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, 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 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. That is why I say take all these classes and do not skip levels. Then, you will not miss anything. It is currently December 2024, so the current version of Access is Access 2024. In my recommendation, get an Access 365 subscription. I did not update the picture here, but you get the point. The lessons today and all this material should work going back as far as Access 2007. So this stuff is pretty good. If you are using any of the modern versions of Access, it should work. It might even work going back as far as 2003, but I would not try it. 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. 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 undos and 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 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 edited record. Once the change is made, we will move the focus on that record so the user can see that was the last one modified and 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 a deletion. Then, we will build our own custom delete button, because I like to handle it myself instead of relying on the built-in events for delete. 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 one has already come up and 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. We will also talk about a problem that happens with CurrentDB.Execute and it not telling you that there is a problem. That is coming up in this lesson. In lesson five, we are going to show or hide the undo button based on whether or not there is anything to undo. If you open up a new record, you do not want to see an undo button when there is nothing to undo. 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. QuizQUIZ FOR ACCESS DEVELOPER 46LESSON 1: Q1. What prompted the creation of the undo system discussed in Developer 46? A. Microsoft's new feature updates B. A need for multi-user data handling C. A question from a moderator about Microsoft Access D. Problems with Windows rollback features Q2. How many undo actions does the default "undo" control in Microsoft Access allow? A. Unlimited B. Two C. Three D. One Q3. What type of table is proposed to track changes in order details for an undo system? A. Historical archive table B. Order detail update table C. Order detail change log D. Main transaction table Q4. Why is a date-time timestamp important in the order detail change log table? A. To identify when the database was last backed up B. To keep track of the user's login time C. To know the order in which changes were made D. To verify the age of the records Q5. What event is used to trigger saving information to a change log before changes are committed in the order detail table? A. After Insert B. After Update C. Before Delete D. Before Update Q6. How are multiple changes for the same order detail ID handled in the change log? A. By using multiple tables B. By using individual databases C. By having multiple entries with a unique change log ID and timestamp D. By overwriting the previous changes Q7. What command is used to insert a record into the order detail change log? A. INSERT INTO ... SELECT ... FROM B. UPDATE ... SET ... WHERE C. DELETE ... FROM D. ALTER TABLE ... ADD Q8. In a multi-user environment, how should access to orders be managed to prevent conflicts with the undo system? A. Use a shared access mode for all users B. Allow all users to make changes simultaneously C. Lock orders for a single user when changes are being made D. Implement a rolling backup system Q9. What is the first step in getting the most recent change for an order to implement the undo function? A. Use the DCount function to count recent changes B. Use the DMax function to find the latest timestamp C. Use the DSUM function to compute the change total D. Use the DMin function to find the first timestamp Q10. How should the code be structured to restore the details of an order from the change log back into the order detail table? A. Use a simple SQL query to restore all fields B. Open a record set and selectively copy over the required fields C. Directly overwrite the existing record D. Export the log to a CSV and re-import it Answers: 1-C; 2-D; 3-C; 4-C; 5-D; 6-C; 7-A; 8-C; 9-B; 10-B. ------------------------------------------------------------------------------- LESSON 2: Q1. What was the main task being continued in lesson two? A. Creating a new record B. Finishing the undo system C. Implementing a search filter D. Designing a new user interface Q2. Which operation are we performing on the order detail table? A. Inserting new records B. Deleting all records C. Editing and undoing changes D. Exporting records to a CSV file Q3. What is the purpose of setting rs2.productname to rs1.productname? A. To compare product names in different tables B. To copy and restore the product name in the undo operation C. To create a new, combined product name D. To display the product name on the form Q4. Why is a record set preferred over an SQL insert statement when copying large amounts of information? A. To avoid accidental data insertion B. Because SQL cannot handle large text fields C. To simplify the handling of special characters in text fields D. Due to SQL syntax limitations Q5. What must be done after updating and closing rs2 to clean up the process? A. Send a confirmation email B. Delete the record from the change log C. Restart the system D. Archive the order detail table Q6. Which command is used to update the form after all changes are made? A. Me.refresh B. Me.recalculate C. Me.requery D. Me.reload Q7. How is the recently changed record located if the ID is not visible on the form? A. By guessing the record position B. By looping through the records until the correct one is found C. By using a timestamp search D. By asking the user to input the ID Q8. What is stored in the variable temp_order_detail_id and why? A. The full database connection string, to optimize connection reuse B. The user's previous search query, to remember user preferences C. The order_id, to create a backup of changes D. The order_detail_id, because it is needed after rs1 is closed Q9. How does the lesson suggest handling a situation where the loop might not find the desired record? A. By displaying an error message B. By sending an alert to the developer C. By exiting the loop if it reaches a null order_detail_id D. By restarting the loop from the beginning Q10. What is the focus of lesson three as mentioned in the video? A. More advanced filtering methods B. Adding user authentication C. Restoring records after they are added or deleted D. Designing a new order form layout Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-D; 9-C; 10-C. ------------------------------------------------------------------------------- LESSON 3: Q1. What is the default change type ID value when a new record is added in the change log? A. 0 B. 1 C. 2 D. 3 Q2. Why is a custom delete button created in the tutorial? A. To handle changes in the table B. Because Access does not have a good on delete event C. To handle multiple table modifications D. To simplify the delete confirmation process Q3. Which VBA constant is used to confirm a delete action with a question dialog? A. VBYesNoCancel B. VBCritical C. VBDefaultButton2 D. VBSNoCancel Q4. How can a record be permanently deleted in Access from the tutorial's context? A. By using SQL DELETE command B. Using delete record command via do command.run command ACCMDDeleteRecord C. Deleting it manually from the DB table D. By setting the change type ID to 3 and saving Q5. What is the purpose of copying the deleted record into an undo table before deletion? A. To keep a record of deleted entries B. To allow the user to restore it if they undo the deletion C. For compliance and auditing purposes D. To save memory space in the main table Q6. Why is setting the order detail ID important when undoing a deletion? A. To avoid conflicts in key constraints B. To ensure record uniqueness C. So the deleted record can be restored with its original ID D. To maintain a history of changes in a clear sequence Q7. What happens if the user confirms the deletion with the custom delete button? A. The app gives a confirmation message but no deletion occurs B. The record is copied to a log table and removed from the main one C. The record is removed entirely with no trace D. The change type ID is reverted to 0 Q8. In what scenario is it possible to set an auto number to an order detail ID programmatically? A. When performing a bulk update B. When the record was previously deleted and the ID doesn't exist C. During an initial database setup D. In the case of primary key setting for a new table Q9. Which conditions trigger the undo operation to add a new record? A. If the change type ID equals 1 B. If the change type ID equals 2 C. If the change type ID equals 3 D. If the change type ID equals 0 Q10. Leveraging what feature allows the undo button to handle various types of changes? A. Creating multiple undo buttons for each change type B. Using a loop to process each change sequentially C. Implementing a case structure based on change type IDs D. Programmatically creating tables for every type of undo operation Answers: 1-B; 2-B; 3-D; 4-B; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C. ------------------------------------------------------------------------------- LESSON 4: Q1. What issue is discussed in this lesson regarding auto numbers in Access? A. Auto numbers must be manipulated manually in Access because they are visible to users. B. Auto numbers are for user-oriented identification, making them crucial for data entry. C. Access resets the auto number counter when a number is restored, potentially causing duplicates. D. Auto numbers can be cycled back to the start if Access runs out of available numbers. Q2. Why is manually inserting and deleting a dummy record suggested as a solution for the auto number problem? A. It helps purge unused records from the database. B. It allows users to reset all fields manually. C. It prevents the error of creating duplicate records. D. It re-establishes the auto number sequence by consuming the next available number. Q3. What alternative SQL feature does the speaker mention is available in SQL Server but not in Access regarding auto numbers? A. Auto increment property B. Primary key designation C. Seed property for resetting auto numbers D. Foreign key constraint Q4. What is the issue with using current DB.execute according to the lesson? A. It always throws detailed error messages for every SQL command. B. It bypasses Access's query engine and does not provide error messages for validation issues. C. It automatically runs every query twice, leading to data duplication. D. It is slower than using manual table interactions for inserting data. Q5. What is the after insert event used for in this context? A. To change the design of the table after a record is inserted. B. To log changes made to a record after it is added to a table. C. To delete all records in the table after a new one is added. D. To notify users of record additions using a pop-up message. Q6. Why does the lesson emphasize not relying on auto numbers for anything other than relationships? A. Because auto numbers are meant for user identification purposes. B. Auto numbers are unreliable and prone to deletion by users. C. They are internally used by Access, and there's a risk of mismanagement if used for identification. D. Auto numbers reset automatically every time a database is restarted. Q7. How does the speaker suggest resolving the lack of error messages when using current DB.execute? A. Run additional diagnostic tools provided by Access. B. Switch to using do command run SQL, which engages Access's query logic checking. C. Enable a debugging mode within current DB.execute. D. Leverage manual code rewriting to catch potential errors. Q8. What two major problems does this lesson aim to fix? A. Inconsistent font sizes and slow query execution. B. Auto number duplication and lack of error reporting by current DB.execute. C. Database connectivity issues and outdated access permissions. D. Data loss during undo operations and incorrect foreign key assignments. Answers: 1-C; 2-D; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B. ------------------------------------------------------------------------------- LESSON 5: Q1. In the context of the video tutorial, when should the undo button be hidden? A. When a new record is added. B. When there are no changes to undo in the change log. C. When the form is first opened. D. When a record is updated. Q2. Where is the best place to update the visibility of the undo button as per the tutorial? A. On Open event. B. On Current event. C. On Load event. D. On Close event. Q3. What is the purpose of using the DLookup function in this tutorial? A. To count the number of records in the table. B. To find the Order ID in the change log. C. To retrieve the ChangeLog ID for the current order. D. To delete unwanted records from the table. Q4. What programming concept was demonstrated to avoid duplicated code when locating the most recent change for an order? A. Creating a new table for change logs. B. Using global variables. C. Writing a Private Function to encapsulate the logic. D. Using multiple subroutines for similar operations. Q5. How is the subtitle of the undo button determined in the tutorial? A. It is set to a default subtitle of "Undo Last Change." B. By using Select Case based on the ChangeTypeID. C. By querying the database for the latest field change. D. Through user input during the operation. Q6. What optional feature was suggested to give users more context about undo operations? A. A feature that displays detailed logs in a message box. B. A redo button to reverse undo actions. C. A pop-up table showing the list of recent changes. D. Automatic email notifications for each change. Q7. What are some proposed improvements discussed for the undo feature in the video? A. Adding a clear log button to reset changes. B. Implementing a redo feature alongside undo. C. Automatically locking records when they're changed. D. Displaying a history of user actions in a timeline. Q8. How does the video tutorial propose dealing with already undone changes if a redo feature is implemented? A. Deleting the entries from the current change log. B. Ignoring records with a new status field indicating they are undone. C. Adding separate redo buttons for each type of record change. D. Automatically updating records based on user preferences. Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-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. SummaryToday's video from Access Learning Zone focuses on creating a robust Undo system for your Microsoft Access database. Normally, Access only lets you undo your last action, but what happens if you need to roll back more than one change, such as editing or deleting several records within an order? In today's lesson, I am going to show you how to implement a change log to track all modifications to orders. This will allow you to undo not just edits, but also additions and deletions, each handled separately.We will begin by designing a change log table that will keep a record of all changes. I will walk you through setting up the logic so that every edit, addition, or deletion is logged appropriately. Then, we will create an Undo button, which must handle reversing each of these different action types. Specifically, we will add a custom delete button, which we have covered before, but this version will include a few new intricacies necessary for our undo system. There are two significant challenges that we need to address. The first is an issue with autonumbers. When you delete a record and later try to restore it with the same autonumber, complications can arise. I will explain exactly why this occurs and how to correct it. The second challenge is that running commands with CurrentDB.Execute does not provide error messages if something goes wrong. I will show you how to detect and resolve these situations as well. To be prepared for this class, you should be comfortable with recordsets and writing SQL queries in Access, particularly Insert, Update, and Delete statements. Developer Level 16 covers recordsets in detail, which is fundamental for what we will be doing today. If you have missed these topics or want a refresher, I have some free TechHelp videos available that cover the basics of recordsets and SQL, although the SQL video introduces only Select statements. The concepts and code in this lesson will work in Access 2024 and older versions, all the way back to Access 2007. I recommend getting a Microsoft 365 subscription for the latest features, but as long as you are using a modern version, you should have no issues. If you run into questions, you can post them at the bottom of the page where you are watching this video, or drop by the Access Forum on my website where our community moderators can help you out. Let me outline what we will cover in this lesson, Access Developer 46: In the first lesson, we start building the undo system by setting up a change log table. We will write the code necessary to store changes in this log and begin the work to create an Undo button, including writing the logic to restore an edited record. The second lesson continues with fine-tuning the Undo system. We will finish the process for undoing edits, ensuring that the user interface reflects the last modified record, so it is clear what has been changed or undone. Lesson three will have us tracking exactly what kind of change was made to each record, including edits, additions, and deletions. This is also where we implement a custom delete button that handles deletions in a way that supports undo, and then we write the functionality to restore deleted records back to an order if the user requests. In lesson four, we tackle the two major problems mentioned earlier. First, I will show you how to manage restoring deleted records that use autonumbers, addressing the issue this can cause. We will also discuss the lack of error feedback from CurrentDB.Execute and how to overcome this challenge. Lesson five covers making the Undo button dynamic. The button will only be visible when there is actually something that can be undone, and it will indicate what the last action was - whether it was an edit, an addition, or a deletion. This feature helps keep your interface cleaner and more informative for the user. If you want step-by-step instructions and a complete walkthrough of everything we discuss here, you can find the full video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListBuilding a change log table for tracking editsSaving changes to the change log table Creating an undo button to restore edited records Restoring focus to the last modified record after undo Tracking change types: edit, addition, deletion Building a custom delete button for records Undoing record deletions by restoring deleted records Handling autonumber issues when restoring deleted records Addressing error handling with CurrentDB.Execute Controlling visibility of the undo button based on changes Displaying last action type on the undo button |
||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access developer 46 lessons PermaLink How To Build a Multi-Level Undo System With Change Log and Custom Delete Button in Microsoft Access |
