Introduction.
Data entry and editing are among the most crucial activities for keeping a database accurate and up to date. These steps ensure that the information remains reliable and ready for generating meaningful reports and analysis. To make data entry easier and more efficient for users, it is good practice to include combo boxes, check boxes, and calculated fields—for example, automatically determining a Payment Due Date as 30 days after the Material Delivery Date.
Another key consideration is data security. Here, the focus is not on preventing unauthorized external access—MS Access already provides robust built-in security features for that—but rather on protecting the data from accidental modifications or deletions by authorized users during routine operations.
For example, suppose our Employee database includes a Memo field that stores detailed information about each employee’s educational background and prior work experience. Normally, when the cursor (insertion point) moves into a field, the entire content of that field becomes highlighted and selected. At this stage, if the user’s attention is momentarily diverted and a key is pressed accidentally, the entire content of the field may be deleted. If the user does not immediately notice this or forgets to restore the data using Ctrl + Z (Edit → Undo Typing), the information could be lost permanently.
Protecting from unintended Changes.
We will focus on this specific behavior of the cursor and explore how to provide a level of protection against such inadvertent data loss. The way the cursor behaves when entering a field is determined by the settings found under the Keyboard tab of the Options dialog box (available from the Tools menu). Under the Behavior Entering Field section, you will find three different options, as shown in the image below:
The first option, Select Entire Field, is the default setting. However, choosing one of the other two options is generally more advisable to prevent the kind of data loss we are focusing on. Of the remaining two, my preferred choice is Go to End of Field. The reason is simple—when this option is selected, even if you accidentally press the Delete key or any other key, the insertion point is positioned at the end of the field content, and the existing information remains safe.
Since this is a global setting in Microsoft Access, any manual changes you make here will affect all forms in every database opened on your machine. Conversely, a database you design on your system will not carry these settings when opened on another computer in a multi-user environment. Moreover, you may not want this behavior applied to every form in your database.
The best approach, therefore, is to enable this feature programmatically through a VBA routine and restore the default settings when leaving that particular form. In a shared network environment, users may have different default settings for the “Behavior Entering Field” option on their own machines, so it’s important not to change these preferences 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 opening a form that requires this modified cursor behavior, we will follow specific steps to enable it during the form’s initialization. Then, when the form is closed, we will restore the default settings to ensure that the global behavior of Access remains unchanged.
Save the current default setting of Behavior Entering Field before it is changed.
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 the Tab Key or the Enter Key. The insertion point will position at the end of the field contents.
No comments:
Post a Comment
Comments subject to moderation before publishing.