Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Data Editing And Security Issues


Introduction

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.


Protecting from un-intentional 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 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
  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 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.


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 are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Running Sum in MS-Access Query

Introduction. We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), publi...

Labels

Blog Archive

Recent Posts