Data Editing And Security Issues ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Data Entry and Editing are the major activities for maintaining the information up-to-date in databases before we are able to prepare any meaningful output for human consumption. It is important that we make these tasks easier for the User by providing Combo Boxes, Check Boxes, or Calculated Fields, like Payment-Due-Date after 30 days of Material-Delivery-Date and so on.

Another important aspect is data security. I am not referring to the protection against unauthorized access from outside, for which we have adequate built-in MS-Access Security features that can be put to use. Here, our emphasis is on unintentional changes or deletions of important information by Authorized Users.

For example, assume that our Employee database has a Memo Field that holds important detailed information on the educational, and past experience of employees. Normally when the Cursor (Insertion Point) moves into a field the entire field contents will get highlighted and selected. At this point, if the User's attention is drawn to something else and touches a key by mistake the field contents will be erased. If she is not aware of this or forgot to recall the lost data by pressing Ctrl+Z (Edit - -> Undo Typing) then it is lost forever.

Protecting from un-intended Changes.

We will concentrate on this particular behavior of the Cursor and how we can give some form of protection against such inadvertent mishaps. The Cursor behavior, while entering into a field, is governed by the settings in the Keyboard Tab of Options, in the Tools Menu. Find the three different options available under the Behavior entering field Options Group in the image below:

The first option Select the entire field is the default setting. One of the other two settings is the most preferable one to avoid the loss of information that we are focusing on. Out of the other two options, I prefer the last one, Go to the end of the field, why because even if you press the Delete Key or any other Key by mistake, the insertion point is at the end of the field contents, and nothing will happen to the existing information.

Since this is the global setting of MS-Access Application, the changes that you make here manually affect all the Forms in all Databases that you load into your machine. Conversely, the database that you have designed for your machine will not carry this option setting along with it when open on some other machine in a multi-user environment. Moreover, we may not need this setting to take effect on all Forms in the Database either.

So the best approach is to enable this feature through VBA Sub-Routines, wherever we need it on Forms, and turn it back to the default settings while the Form class. If your database is shared on a Network the Behavior Entering Field default settings can be different in other machines set by the User. We don't want to alter these settings permanently.

Following is the numeric value of each Behavior Entering Field Option setting:

Behavior | Description | Option Values.

Select Entire Field - 0
Go to Start of Field - 1
Go to End of Field - 2

When we open a Form, where we need this change of behavior, we will go through the following steps to enable it and when the Form is closed put the default setting back in its place:

Save the current default setting of Behavior Entering Field, before changing it.
Change the setting to Go to the end of Field behavior for the current session of the Form.
Reset it back to the saved value in Step 1 above before closing the Form.

We can achieve this with the following Event Procedures in the Form Module:

Option Compare Database
Dim DefaultBehavior As Integer

Private Sub Form_Load()
    DefaultBehavior = Application.GetOption("Behavior Entering Field")
    Application.SetOption "Behavior Entering Field", 2
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Application.SetOption "Behavior Entering Field", DefaultBehavior
End Sub

Copy and paste the above Code into the Form's Code Module and save the Form. The Dim DefaultBehavior As Integer statement must be placed in the Global area of the Module as shown above.

Try out the New Setting

Open the Form in normal View and try moving the cursor from one field to the other by tapping Tab Key or Enter Key. The insertion point will position at the end of the field contents.