In this month’s column, Russell looks at implementing undo in form-subform applications and then goes on to demonstrate some reusable error-handling code.
I have a form that contains a subform with related records. I want my users to be able to undo changes to the whole form, but Access’s built-in Undo does both the form and its subform. Is this possible?
This is a question that I’m sure many of our readers have asked before. Although it might sound simplistic, one way to solve the problem is to store the state of the data in temporary tables each time you go to a new record on the main form and use this data as your base for undoing data entry. The easiest way to create these temporary tables is to duplicate the structure of the recordset the form uses. If you look at the CreateTempTable function in basUndoChanges in the sample database, you can see that the function simply loops through each of the fields in the recordset for the supplied form and creates a table with an identical structure. Once this is done, each time the form moves to a new record, the function will be able to save the original state of the records on the form to the newly created table.
In order to ensure that the original state of the data is saved to the tables when using this code, you must call the SaveMainForm and SaveSubForm functions in the Current event of the main form. The Current event is fired whenever a user changes from one record to another. Both begin by clearing the data from their temporary tables before doing anything else. Then SaveMainForm saves only the current record (if it’s not a new record), while SaveSubForm loops through all of the records in the form’s recordset and saves all of them. Both of the functions loop through the fields in the form’s recordset to know which fields to save the data to in the temporary tables.
Undoing the changes basically works in reverse. To undo the subform, all of the records are deleted, and the old records are reinserted. To undo the main form, the original record is deleted, and the original values for each field are restored.
You might notice in the code for saving and undoing the subform that the code checks the BOF and EOF indicators and the RecordCount of the form’s recordset to determine whether there are any records in the recordset. When I first created the function, I tried using just the BOF and EOF indicators. However, using the RecordsetClone method doesn’t necessarily set the current record, so BOF and EOF are unreliable. I then decided to try using just the RecordCount property to check whether there were records in the recordset. This worked, but not in every situation. Just to be safe, I changed the function to check both BOF/EOF and RecordCount and haven’t experienced any problems since.
You can try the function out by opening frmCustomers in the sample database and editing the data in the form. There are a couple of items you should be aware of when using the function. When you undo the changes to the form using these functions, you must undo the changes to the subform before the main form. Doing otherwise can cause errors to pop up if the undo violates the referential integrity of the database. Also, there are two places where the function won’t necessarily work as you’d like it to. If you delete the record on the main form, the function can’t restore the subform data, since deleting a record triggers the Current event of the form. When the Current event fires, my code saves the data of the form’s record and moves it to the temporary table, losing your old data. The second shortcoming of the function is that it might not work properly if the recordset of either of the two forms contains an Autonumber field. This is because the function will try to fill in the Autonumber value, and Access won’t let it.
To use the function in your own database, copy basUndoChanges into your database. In your Form_Load, you should call the CreateTempTable functions to create the necessary temporary tables (in a single-user database, these tables probably don’t have to be recreated every time). You’ll need to add the calls to my Save functions in the Current event for the main form, and create a command button or menu item that calls the Undo functions.
Error Handling
I find that users can often be confused by the default error message that Access shows when a required field hasn’t been filled in on the form. Because of this, I often write code in a form’s BeforeUpdate event that tells the user which field needs to be filled in and then move the focus to that form. Is there a way that I can do this without having to rewrite the code for each of the forms in my database?
I’ve included a function in the sample database called CheckRequiredFields that takes only one argument, the form you want to check, and does exactly this. Access 97 introduced a very useful feature that can help you do exactly what you need to do. Data entry controls that have an attached label control now have their own Controls collection. This collection includes only one item, the attached label. If you had a control called txtDataControl, you could reference its label with Forms!txtDataControl.Controls(0).
Essentially, this is how the function works. It loops through each control on the form and checks to see whether it’s bound to a field, and then checks that field in the form’s recordset to see whether the field is required. If the field is required and the field is currently Null, then the function fills a variable with the name of the control that should receive the focus and the name to display for the field in the error message (this variable is a user-defined datatype that I created called BoundSet). If the control has a Controls collection, the display name for the field is set to the Caption property of the associated label. If the control doesn’t have a Controls collection, the display name value is set to the name of the data control that’s being checked. When a violation occurs, the function displays a message and sets the focus back to the control that should be filled in. To use the function in your own database, copy basValidation from the sample database and add the following code to the BeforeUpdate event of the form you want to check:
Private Sub Form_BeforeUpdate(Cancel As Integer) If CheckRequiredFields(Me) <> True Then Cancel = True End If End Sub
The code sets the Cancel parameter to zero, as this causes the form to cancel the update.
