Editing and Undo on a Record - MS Access

As you start to edit a record, the OnDirty event is fired. If you press the escape key or otherwise undo the changes you’ve made, the OnUndo event fires.

OnDirty →OnUndo

If you save your changes to an existing record, then the BeforeUpdate event fires, followed by the AfterUpdate event.

BeforeUpdate and AfterUpdate Events

You can use the BeforeUpdate event to perform validation and accept or reject a user’s changes to either a control or record. With the AfterUpdate event, you can take action after the validation has been completed.

BeforeUpdate → AfterUpdate

The BeforeUpdate event on both a form and the controls on a form are used primarily for validation checking before a value is saved. On a control, the control’s Undo method can be called to undo any attempted change. On a BeforeUpdate event, the Cancel argument on the procedure call is set to False to reject any changes.

In the frmCustomers BeforeUpdate Events form (in the companion content), the BeforeUpdate event on the [ZIP/Postal Code] field shows how you can start to build a sophisticated validation routine. This example also shows how the ctrl.Undo method can undo the user’s changes (only for that control). This code is intended for countries other than the United States to verify that the first character of the postal code is text (A...Z, a...z). If the first character is not text, it cancels the update and undoes the text typed in by the user:

What if a user has not yet entered a value for the Country, but begins to type the code? One solution is to approach this in a similar manner to that of the ContractClass, but this time write a statement to only enable the [ZIP/Postal Code] if the user has entered a [Country/Region]:

What if a user sets the Country to USA, types in a number for the ZIP code, and then changes the country to France? You can get around this by adding further code to the Country_Region_AfterUpdate event. An alternative, which would also mean that existing data was validated, is to add code to the form’s BeforeUpdate event. We have also added a SetFocus command to move the cursor into the field that needs to be changed:

The AfterUpdate event on a form and the controls on a form are used primarily to carry out special activities following an acceptable change to the data, such as controlling the visibility or values in other controls.

Locking and Unlocking Controls

The next code example is the frmCustomersAndOrders form, which illustrates how you can change a form’s behavior so that users are forced to either press a function key or a button to unlock the controls for editing. The form contains a call during the Current event to lock all controls against editing as well as a button to enable editing of data in all controls.

This example also illustrates how we can exclude certain controls from this process by using the control’s Tag property. This is a very useful property, and you can decide for yourself how best to use it. The following code has been added to the form:

In a module modForm LockUnlock, we have written a general purpose routine for locking and unlocking the controls on the form, as follows:

Using Me vs. Screen.ActiveForm to pass a reference to a form

When calling the procedure modFormLock Unlock_LockUnlock (detailed in the previous section), the calling form passes a reference to itself by using the special Me keyword. It is tempting in this module to consider Screen.ActiveForm as an alternative method of getting a reference to the active form. However, during the first call to Current, when the form opens, the window is not yet active, and so this reference will fail.

BeforeInsert and AfterInsert Events

After the first key press on a new record, the BeforeInsert event and then the Dirty event fire.

BeforeInsert → Dirty

If you save your changes to the new record, then the BeforeUpdate, AfterUpdate, and AfterInsert events fire, as illustrated in the following


As an example of coding for these events, frmCustomersBeforeInsert has code added on the BeforeInsert event that retrieves a special unique key value from a lookup table and adds this to the record. This can be used as a method for getting unique key values for data, as shown in Figure.

When inserting a new record, a key is generated by looking up values in a table that holds the last used keys.

When inserting a new record, a key is generated by looking up values in a table that holds the last used keys.

Unique key values are issued from a table of keys, as shown in Figure.

The unique key can have a prefix and pad out the number with extra zeros, which could be used for a number of tables.

The unique key can have a prefix and pad out the number with extra zeros, which could be used for a number of tables.

The following example shows the code that added to the BeforeInsert event on the form to retrieve a new value for the key:

Alternatively, this code can be added to the AfterInsert event, depending on exactly when you need the key issued. In the code that follows, modKeys_GetKeyValue looks up the tablename and then obtains the next sequenced key value. It also has some additional features including adding a prefix to the key (for example, Y2000) and padding the number with leading zeros; for example, 1 would become 001. The most important part of the routine is the lock retries to resolve any multi-user conflicts when two or more users try to get the next key at the same time.

This process is implemented with a transaction to protect against multiple users requesting keys at the same time; if a transaction fails, it performs a Rollback then issues a DoEvents to allow some time to elapse before trying the transaction again. We have also set a constant to control the maximum number of times that the transaction can be attempted:

The Delete Event

Deleting a record initiates the following event sequence:

Delete→Current→ BeforeDelConfirm→ AfterDelConfirm

You then have the option of writing code against these events to display your own confirmation messages and suppress the confirmation message that Access displays, such as shown in the following:

Using AfterDelConfirm to detect actions by a user when deleting records

In the AfterDelConfirm, you can detect and act upon the earlier action taken in the BeforeDelConfirm event and choices made by the user. To see an example of the following code in action, look at frmRecordsetClone, which is available in the companion content for this chapter (note that to see acDeleteUserCancel condition you would need to remove the code from the BeforeDelConfirm event):

KeyPreview and Key Events

You can pass information about what keys the user presses to the form’s KeyPress event by setting the KeyPreview property for a form to True. By default this is not set, so when typing into controls, you need to check the keystrokes for each individual control on the form. The KeyPreview feature was added to work around this problem and allow keystrokes to be checked at a single point on the form.

KeyDown →KeyPress→(Dirty if first edit)→KeyUp

The Error Event

The Error event allows you to take control of error message boxes that Access displays when a user edits data through the form, as shown in Figure.

A trappable error when a user tries to delete a record, but the record has related records.

A trappable error when a user tries to delete a record, but the record has related records.

The AccessError function can be used to lookup the Access Error message. By setting the Response flag, you can control whether the built-in error message is also displayed. See the companion content example form, frmRecordsetClone, for this code:

shows the new error message.

Note that because we looked up the error message, it does not contain the name of the table that contains the related records that blocked the delete action.

The Access error message box replaced with a custom dialog box.

The Access error message box replaced with a custom dialog box.

An example of how this is extremely useful is when you are working with SQL Server; in this case Access displays a generic ODBC error message. If you look in the Errors collection and display Errors(0), then you can provide a much better description of the error.

Saving Records

As users move to a new record or a subform, Access automatically saves changes to the record, but during interaction with the form, after the program code makes changes to a record, there will be points in time when you want to force a record to be saved. Several different methods exist for saving a record.

Alternative methods for saving records

The first and oldest technique for saving records is to use the SendKeys action to simulate the user pressing the Shift+Enter keystroke combination, which forces the currently active record to be saved. The True argument indicates that the code should wait until the keystrokes have been processed:

SendKey "+{Enter}",True

The second method is to use the RunCommand action:

RunCommand acCmdSaveRecord

Note that if you debug code when using either the RunCommand or SendKeys methods, the application throws error messages indicating that the record cannot be saved (if it is not already being edited). Usually, this means that you need to add an On Error Resume Next before the save actions; the problem with this is in knowing when there has been a genuine error during the save operation. A third method is to force the record to save by setting the Dirty property of the form to False, as shown in the following:

Me.Dirty = False

In some older systems, you might also see the use of the DoMenuItem command, which simulates the user making a menu selection to save a record.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics