Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Data Editing And Security Issues

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.
  1. Select Entire Field - 0
  2. Go to Start of Field - 1
  3. 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 the 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 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.

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code