Data Editing And Security Issues

Data Entry and Editing is a major activity 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 to 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 detail information on 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 for ever.







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 Tools Menu. Find the three different options available under the Behavior entering field Options Group in the image below:



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


Since, this is the global setting of MS-Access Application, the changes that you make here manually affects all the Forms in all Databases that you load into your machine. Conversely, the database that you have designed on your machine will not carry this option setting along with it when open on some other machine in a multi-user environment. More over, 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 closes. 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 this settings permanently.


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

    Behavior Descritpion  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:

1. Save the current default setting of Behavior Entering Field, before changing it.


2. Change the setting to Go to end of Field behavior for the current session of the Form.


3. 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 at the Global area of the Module as shown above.


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.

---

Link External Tables with VBA
Source Connect Str Property and ODBC
Access Live Data in Excel-2
MS-Access Live Data in Excel
Database Connection String Properties
Opening Excel Database Directly