Chapter 15

Protecting and Validating Data

Creating forms that expose data to users is much easier than creating forms that protect the data from users. One of the reasons an application needs forms (as opposed to simply exposing users to datasheets) is because forms contain properties and code. Form and control properties and event code can be used to minimize the ways that a user can enter erroneous or incomplete data into a database. In this chapter, I provide practical advice for creating forms that do the following:

Nightmare Finding Elm Street

The suburb I grew up in is laid out in a near-perfect grid. Streets run north to south and east to west, with even house numbers always on the east and north sides of the street. There are ten blocks to a mile. Give me any address in my home town and I can tell you how far it is from where I am standing and how to get there by walking, driving, or flying.

When I was in college, I sold real estate on the weekends to pay for tuition. I had to navigate all over Seattle, often late at night and far from the comfort of my home town's orderly grid. In Seattle, streets like Elm and Maple are nowhere near Oak and Pine, and each street, lane, park, or court can run in any given direction for two blocks, turn ninety degrees for two more, and then double back on itself completely. Suffice it to say, finding a building in Seattle requires good navigation aids.

When users work in your application, would you rather they savor the pleasure of its easy, orderly layout-like cruising the streets of my home town-or struggle with a map and compass, lost in a big-city labyrinth of features and forms?

If you can keep complex applications from looking complex to their users, you can ensure that they easily will find the data and features they need. A little extra coding and layout effort, coupled with a few clever interface techniques, can make the difference between a navigable application and scores of lost, wandering users.

An expert form must find a delicate balance: it must lead the user to the data and then protect the data from the user. Regardless of their aptitude or training, users are human and will make mistakes in your application at some point, and those mistakes will cost somebody time and money. Expert forms are designed to minimize mistakes and should do the following

In Chapter 14, "Navigating in Forms and Applications," I described the following concepts:

Each of these concepts is important to application usability. In this chapter, I drill down from broad navigation concepts to specific form design techniques to regulate the interaction between users and data. The techniques in this chapter are optional and follow a very restrictive application interface paradigm. You may choose to use a subset of these techniques or create your own derivative techniques from my examples, but in either case the problems and concepts addressed in this chapter deserve thoughtful consideration as you construct applications. The code samples will also provide you with food for thought.

Protecting Data and Assisting Users

We've all heard the common question asked of parents, "It's nine p.m.; do you know where your children are and what they are doing?" Parents like to know that their children are happy and safe. As a developer, this question translates to "It's nine a.m.; do you know where your users are in the application and what they're doing?" Developers like to know that their users are happy and their data is safe. Creating this kind of environment means controlling how the users interact with forms and other objects and with data.

As you monitor user interaction, you must observe and respond to various actions, including the following:

The following topics provide thoughts and tips for building applications in which such actions do not escape the notice of your underlying program code.

Trapping Dangerous Keystrokes

Access 97 provides you with more tools than ever to monitor the keyboard and to limit how users interact with your forms. As a bonus, several form coding techniques we relied on heavily in Access versions 1 and 2 have now become built-in product features, as discussed in the topics that follow:

Cycling: The Old Way and the New Way

When building Access expert forms, sometimes you will want to trap keyboard actions and provide the user with navigation assistance, directing the cursor to a specific location.

One simple navigation assistant is called a "tab sentry." A tab sentry is a control placed at a strategic location on a form to intercept Tab and Shift+Tab keystrokes and reroute the focus to a specific location or launch some other process. In Access 2, you most commonly placed a tab sentry at the bottom of each form as the last control in the tab order. When a user tabbed to the control, the Enter event of the sentry fired, executing code to intercept the intention of Access to move to the next record and "cycling" the focus back to the first control on the current form instead. In this manner, you could prevent the user from pressing Tab to move off of the current record.

Using tab sentries differs from using a control's LostFocus event to detect movement out of a control because the tab sentry is designed to trap the use of the Tab and Enter keys when leaving a control; LostFocus is activated by mouse movements as well.

Sentries at the Gate

Many of my Access articles and speeches over the past several years have focused on helping developers create systems to control form navigation. Tab sentries have always been a featured topic in such presentations. Generally, despite the several dozen tips that I might include in a navigation presentation, tab sentries generate an overwhelming majority of the positive feedback. I've concluded the following from this fact:

The new Current Record setting for the Cycle property of a form now removes the need for tab sentries for "inter-record" navigation. Cycle is a form-level property with the following values and uses:

Although the addition of the Cycle property solved the problem of users wandering from the current form record or page via Tab, the property still does not address other navigation situations. Therefore, even with the Cycle property, there are situations in which tab sentries can provide value to your forms. I'll give two examples.

The first useful placement of tab sentries is to control navigation into and out of single-record subforms. Subform controls present the following two navigation problems:

To provide a more friendly subform navigation model, place Command Button controls at the locations desired for the sentries-one just before the Subform control, and one inside the subform as the last control in the tab order. For these sentries, set the Transparent property of the buttons to True. This setting enables each control to receive focus via a Tab keystroke yet does not display the control to the user. Finally, add code to the Enter event for each control to initiate the desired movement when the control receives the focus via a Tab key. The Enter event to move focus into the subform uses this code:



You cannot move focus from a parent form directly to a control in its subform. The focus change requires the two steps shown in the previous code listing: one step moving to the Subform control and another moving to the subform's Text Box control.

The subform Sentry control's Enter event to move focus from the subform back to the parent form uses this code:


You can also use a text box as a Sentry control, but doing so involves more work on your part. A text box whose Visible property is False is removed from the tab sequence, so you must keep the control visible and instead set the BackColor and BorderColor to match the form's background. This makes the text box seem invisible when the form is run. These settings make the control invisible in design view also, however, which can be frustrating during your development work.

Figure 15.1 shows the design view of an example form with a subform and two tab sentries. Note the location of the transparent buttons that serve as sentries.

Fig. 15.1

This form has a tab sentry on the parent form and another on the subform.

Make your tab sentry buttons very small. Even though the tab sentries are transparent, the user can unintentionally click them, which may cause some confusion. You can type a small value such as 0.001 into the Width property of the Sentry control and Access will set the control to its smallest allowed size.

Alternately, you can place a tab sentry control behind another control so that users cannot unknowingly click it.

A second useful implementation of tab sentries is on multi-page forms. Simply setting the Cycle property to Current Page and teaching users to press PgDn and PgUp to navigate between pages may not provide the friendliest approach for some users. As an example, create a two-page form with text boxes on each page and the Cycle property set to Current Record. When you tab off of the last field on page 1, Access only displays enough of page 2 to show the next control in the tab sequence, as shown in Figure 15.2.

Fig. 15.2

Pressing Tab in the last control on a page scrolls to the first control on the next page instead of synchronizing the page display.

If users want to navigate between controls on consecutive form pages with the Tab and Enter keys and have the pages align properly, you need to employ tab sentries. To create a tab sentry for pagination, use a Command Button control with the Transparent property set to True. The user can tab to the button, triggering the Enter event, but the button does not display on the form. Attach to your tab sentry button an Enter event in code that performs the desired movement:

Private Sub zscmdSentry1_Enter()

Me.GoToPage 2

End Sub

When the user tabs to the sentry button, the Enter event fires and synchronizes the form to page 2, making for a much more pleasurable form navigation. Once you are on page 2, however, you have the problem of how to return to page 1 because using Shift+Tab to do so introduces the same out-of-sync display problem that occurs when moving forward through pages. To compensate, place a tab sentry at the top of page 2 to trap a Shift+Tab moving backwards off the page. Each page now has a top sentry (to handle a Shift+Tab at the first control on the page) and a bottom sentry (to handle a Tab at the last control on a page).

Unfortunately, the top sentry on page 2 can receive focus in two different ways, as follows:

The challenge is that your code must trap and ignore the second situation while facilitating the first; otherwise, the sentry at the top of page 2 will receive focus as page 2 is displayed, execute its code, and bounce the focus right back to page 1. You will have created a nonsensical loop.

The sentry at the top of each multi-page form must cleverly know when the user is tabbing backward through the controls and wants to see the previous page, and when the user is coming from the previous page. The code in Listing 15.1 addresses this need.

Listing 15.1 AES_Frm3.Mdb-A Smart Page 2 Tab Sentry

Private Sub zscmdSentry2_Enter()

' If not moving to page 1, stay on this page

If Screen.PreviousControl.Name <> "txtSecond" Then


Else ' Must be moving to page 1 from txtSecond

Me.GoToPage 1

End If

End Sub

When using tab sentries, you can safely use Screen.PreviousControl in the code to determine which control from which the user is tabbing. Because tab sentry code is tied to the Enter event, which fires before the control's GotFocus event, Sentry controls technically never receive the focus and thus never register as a PreviousControl.

Now consider the ultimate navigation hybrid: buttons that serve as tab sentries for keyboard users and can also be clicked to navigate with the mouse. Such buttons must be located at form locations and in the tab sequence in a manner that enables them to fulfill both of these objectives, and they must not be set as transparent because users need to see the caption or graphic on the button to click it.

Figure 15.3 shows a form with tab sentries that also serve as visible navigation buttons. Because the Access Enter event is fired by both the keyboard movement to the button and a mouse click the button, this single event provides the mechanism for both types of navigation from the same button.

Fig. 15.3

This form has three pages and tab sentries to allow users to use Tab and Shift+Tab to move between the pages.

Tab sentries are very useful if you need to create a form that has pages aligned not only downward but across. You can create forms in Access that are wider than the screen display and use the Right and Down arguments of the GoToPage method to move the screen window from left to right to view the pages. Usually this technique is employed to create forms with more pages than can be created in the Access maximum form section height of twenty-two inches. Because the user cannot use PgDn and PgUp to move to pages located to the right or left, tab sentries are your salvation in this situation.

Disabling Keystrokes with Startup Properties

Beginning with version 95, Access provided a new property for limiting some specific user keystrokes. The property is set in the Startup dialog box available from the Tools menu, as shown in Figure 15.4.

Fig. 15.4

The Startup options dialog box has an option to disable four powerful Access keystrokes.

Clearing the check box labeled Use Access Special Keys disables the high-level keyboard shortcuts shown in Table 15.1. These keystrokes and the new Startup dialog box are explained in greater detail in Chapter 17, "Bulletproofing Your Application Interface."

Table 15.1 Keystrokes Disabled by the Startup Option Use Access Special Keys



F11, Alt+F1

Shows the Database window


Shows the Debug window


Alternates between menu bars


Pauses code and displays the module window

Startup property settings travel with the database-not the workstation, so you need only clear the special keys check box once when building the application. You must close and reopen the database to see the effect of the setting.

In spite of this useful new feature, many other keystrokes remain to be concerned about in an application, and they are not so easily disabled. The next two topics address further protective techniques you can use to watch the keyboard.

Disabling Keystrokes with AutoKeys

Several Access keyboard shortcuts provide powerful functionality to careful users and developers. The same keystrokes can just as easily prove dangerous to an application's data, however.

Two convincing examples of dangerous keystrokes are the Ctrl+H shortcut to display the Replace dialog box and the Ctrl+A combination to select all records in a form's recordset. In both cases, an unsophisticated user can easily affect disastrous bulk changes on multiple records, perhaps even thinking that he or she is only altering a single record.

Many keystroke combinations like these can be disabled using an AutoKeys macro entry that maps the keystroke to do nothing by leaving the Action value blank, as shown in the AutoKeys macro in Figure 15.5. Restricting these keystrokes is described in more detail in Chapter 17, Bulletproofing Your Application Interface."

Fig. 15.5

This AutoKeys macro disables most built-in keyboard shortcuts for unsophisticated users.

If your application's users are quite familiar with these Access keyboard shortcuts, some of them may be confused when familiar keystroke combinations stop functioning. To assist such users, you can place a MsgBox statement in the Action column of the macro instead of leaving the column blank, and your message box text can tell the user what menu or toolbar options they should use to replace the removed functionality. Alternately, you can use a keyboard macro or a form keyboard handler to detect the standard keystroke and redirect it to run your own version of the feature.

A few keyboard shortcuts cannot be intercepted with keyboard macros. These keystrokes, such as Ctrl+Shift+Spacebar to select all records, are listed in Chapter 17, "Bulletproofing Your Application Interface." You must use code to trap these keys, as shown in that chapter and in the next topic.

Trapping Keystrokes Coming to a Form or Control

You may want to add code to a form to watch the keyboard for specific events. Such code (often called a keyboard handler) is one of the ways to trap and discard keyboard shortcuts that you choose to disable on a specific form.

You can also use keyboard handlers to watch for keystrokes that have special meaning to the form or application. To trap keystrokes, use one of the following Access events:

KeyDown can fire multiple times if a key is held down, making it a poor choice for trapping keystroke combinations that should execute a process rather than a single action. Use KeyUp instead, subject to the caveat described for it.

You can trap keystrokes at the form level, the control level, or both. Each control, as well as the form itself, lists the three keyboard events. Setting the KeyPreview property of a form to True ensures that the form's keyboard events will fire before a control's events. The events are not mutually exclusive, meaning that a key pressed on txtCust fires the KeyDown event for the form first then the same event for the text box.

Based on these event descriptions, you can discern that KeyDown is the most reliable overall choice for trapping keystrokes coming to a form or control. KeyPress is also useful but to a lesser degree-use it when the keyboard test does not need to provide shift key information and does not involve cursor movement keys.

As an example, assume that your new Access application replaced an older application that you could use the Ctrl+F2 key combination to highlight the remaining text in an entry field, much the same as Shift+End works in Access. Your users expect and want the Ctrl+F2 keystroke to function the same way in your Access application as in their previous applications.

Listing 15.2 shows a sample KeyDown event at the form level to trap Ctrl+F2 and provide the text highlighting functionality your users request.

Listing 15.2 AES_Frm3.Md-A Keydown Event Providing a Form-Level Keyboard Handler

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' Purpose: Trap the key combination Ctrl+F2 coming to a text box

' control and highlight the remainder of the text.

' The form's KeyPreview property must be True.

Dim ctl As Control ' Active control

Dim blnCtrl As Boolean ' To test for Ctrl key

Set ctl = Me.ActiveControl

If ctl.ControlType <> acTextBox Then

Exit Sub

End If

blnCtrl = (Shift And acCtrlMask) > 0 ' Get status of Ctrl key

If blnCtrl And KeyCode = vbKeyF2 Then ' Ctrl+F2 pressed

If ctl.SelStart < Len(ctl.Text) Then ' Nothing to do if at end

ctl.SelLength = Len(ctl.Text) - ctl.SelStart ' Highlight text

End If

End If

End Sub

Use generic keyboard handlers whenever you want your form to be "smart" about keystrokes. In the previous example, notice that I created the keyboard handler at the form level rather than at the control level, then simply made the handler abort when the current control was not of the desired type. This type of centralized, generic keyboard handler is much easier to create and maintain than one placed in the keyboard events of each individual control.

Note in Listing 15.2 that the state of the Shift argument is detected by storing in a variable the comparison between the key state in the Shift argument and a keyboard constant (acAltMask, acCtrlMask, and acShiftMask are the intrinsic constants to use). The Shift argument is a bit field with bits 0, 1, and 2 flipped to represent Shift, Ctrl, and Alt respectively; thus, you must test their state with bitwise math (as in Shift And acCtrlMask).

If you intend to trap keystrokes to disable certain key combinations rather than to trigger an action, you can render keys inert by altering the KeyCode argument in the KeyDown and KeyUp events. Set the argument to 0 at the end of the event procedure to disable the keystroke, as in this code:

KeyCode = 0

To disable the key sent to KeyPress procedure, set the KeyAscii argument to 0 in place of the KeyCode.

When detecting keystrokes coming to a control, be aware that the control that currently has the focus generates KeyUp and KeyPress, making using these events somewhat dangerous for detecting navigation keystrokes. For example, if the user presses the Tab key, the current control receives the KeyDown event, then the focus moves to the next control in tab order. This second control actually executes its KeyPress and KeyUp events; these same events for the first control do not fire.

Using keyboard handlers, you can detect and discard or reroute keystroke combinations, such as Ctrl+Shift+Spacebar (Edit, Select All Records), that you do not want to be active in your application.

Determining Allowed Menu and Toolbar Options

You may have noticed that some of my expert form examples in this book do not use the Access built-in toolbars but rather place toolbar buttons directly on the forms, as shown in Figure 15.6.

Fig. 15.6

This form has embedded toolbar buttons only.

This embedded toolbar button model provides me with complete control of the user's environment with no activities assigned to macros or command bars and little else left to chance. Whether you agree with my metaphor, you should consider the relative merits and dangers of using Access menus, Access toolbars, and embedded toolbar buttons in various combinations.

If you have not done so already, read the section "Navigating via Menus and Toolbars" in Chapter 14, "Navigating in Forms and Applications," to gain a better understanding of the strengths and limitations of the new Access command bar technology.

Deciding Between the Access Commands and Your Own Commands

The navigation code shown in the section "Replacing the Navigation Buttons" in Chapter 14, "Navigating in Forms and Applications," can enable or disable embedded buttons at will or even hide, show, and rearrange buttons. This provides my application with control over its environment, a situation that does not arise when I rely on the Access toolbars, over which I have little control. Additionally, the following are a few other reasons why embedded form toolbars can prove useful:

Despite this rationale, most developers are quite comfortable using custom or built-in toolbars and menus in their applications for specific forms or form groups. You will have to experiment and decide which style suits you best and when your needs warrant a more restrictive model.

It is also possible to create a hybrid style that uses a generic Access toolbar for truly generic tools and that embeds tools on forms for the rest of the work, as shown in Figure 15.7.

Fig. 15.7

This form mixes custom embedded buttons on the form with the Access built-in toolbar buttons.

While it is tempting to assume that many Access toolbar buttons and menu options are good candidates for use with your forms, even some seemingly innocuous features can be dangerous. Consider the complexity of the following "simple" toolbar buttons:

My point here is tools that save or delete a record and all of its related events may cause problems when placed on a form toolbar or menu, so you should use them with caution or at least be alert to their power.

For any form that you can navigate between records using the Access toolbar or menus, you must locate the form's record validation code in the BeforeUpdate event so that it will be called as the form navigates. Placing validation code on an embedded navigation button that can be circumvented by a toolbar or menu selection is dangerous.

In deciding how to balance the placement of Access' features against your coded routines (as embedded buttons on a form or custom options on a menu or toolbar), consider three more key points:

Crafting a balance between custom form buttons and built-in Access features is both an art (the art of user interface design) and a science (the science of coding). Consequently, you may find strong discussion among your fellow developers about the points I have made in this topic.

Selecting the Appropriate Menu Options

Crafting retail-quality expert forms requires that you pay close attention to usability and interface design. While generally-accepted interface guidelines require that you provide your users with a menu bar, you are sometimes faced with the conflicting desire to control the user's interaction with the form, and menu bars in Access have historically not facilitated this well.

The advent of programmable command bars has improved the developer's lot somewhat in this area. When building your user interface elements, you can now easily choose between using a built-in Access feature or creating a custom one. In either case, you can provide users with access to your feature set through menus or toolbars that look and work professionally.

Keyboard-centric users historically prefer a rich set of menu options, so if you lean toward embedded form buttons (as discussed in the previous section), they may feel slighted. On the positive side, you can balance your custom form options with standard menu options. In doing so, you can create generic, reusable menus for your application that perform standard tasks while still disabling the Access features that you want to replace and handle through form code.

For a menu bar or toolbar to be generic, it must apply to any kind of form your application might display: data-bound forms, fill-in dialog boxes, switchboard menus, and so forth. Therefore, a truly generic menu is quite sparse, as shown in Table 15.2.

Table 15.2 The Truly Generic Access Menu Options



Action or Code



Application.RunCommand acCmdExit


Contents and Index

Launch the application's help



OpenForm on a custom About dialog box

If you want to add more commands than these to your standard form menus, you must understand the advantages and dangers of each Access menu item exposed through the RunCommand method (the core command for your menu actions). Table 15.3 lists examples of the internal menu options that I would not normally use on an expert form.

Table 15.3 Some Built-in Menu Options Dangerous to Custom Forms




New Database...


Open Database...


Get External Data




Save As/Export...


Database Properties


Design View














Run Macro...


ActiveX Controls...




all options


Microsoft Access Help


What's This

In your particular application, a feature in the preceding table may, in fact, provide useful capabilities to a specific form. Providing one of these native Access capabilities on your standard menu may expose too much opportunity to the user to modify the application objects or disrupt application flow. In such a case, replace the functionality that the Access menu option provides with a custom menu option that calls your own code routines that safely achieve the same objectives. The fact that the Access runtime mode provides none of the built-in menu options listed in Table 15.3 (except for the window and help options) reinforces their limited usefulness in applications.

The following list describes the commands that may be useful for your form menus and toolbars, subject to the concerns stated with each command. Note that a built-in command may perform its job in a way that is not fully appropriate to an expert solution, so I usually prefer making custom toolbars for many applications. (See examples of my highly customized menus and toolbars in Chapter 14, "Navigating in Forms and Applications.") In this list, commands that the runtime mode in Access provides are noted with an :

Be careful when allowing users to apply the built-in Delete Record button or the Del key from the keyboard for two reasons. First, it's hard to intercept this type of deletion and write a log record of the deletion or create backup copies of the records to be removed. Second, did you know that a user can press Crtl+Shift+Spacebar to select all records in a form's recordset, then select Delete Record and wipe out all the records in the form's recordset? By controlling deletions from your own button and code, you can better control such situations.

If you provide users with the ability to filter a form's recordset, give them a button on the toolbar or form that shows them the current filter criteria. Otherwise, they often forget they have filtered the data and become confused when they cannot find a specific expected record. Simply have your button display a message box that shows the value of the Me.Filter property.

When you add the Advanced Filter/Sort toolbar or menu option to a custom menu, Access presents the user with several built-in menu options when in the query grid for filtration, even if you disabled these options on your custom menu. For example, the File and Tools menus are available even if the form's custom menu does not include them. If your application is not running in runtime mode, consider clearing the Allow Full Menus Startup property to remove the display of the most dangerous of these menu options.

Listing 15.3 A Code Routine to Check Spelling Only on the Current Record

Private Sub RecordSpell()

' Spell check the current record

Application.RunCommand acCmdSelectRecord

Application.RunCommand acCmdSpelling

End Sub

AutoCorrect is a helpful but dangerous feature. You cannot detect and disable this option on the workstation through program code, so your application will inherit the AutoCorrect behavior installed on its user's workstation. You should teach your users about its existence and the vigilance required as data items are entered then changed by auto correction. If this feature is deemed too dangerous, disable it by setting the AllowAutoCorrect property to No on each Text Box and Combo Box control on a form.

You should also teach users that changes to the AutoCorrect behavior in one Office application trickles into the others. Access, Excel, PowerPoint, and Word all share the same ACL (auto correct list) file in the Windows directory, so any change to the behavior made in any other Office tool will trickle into the Access data-entry sessions!

For example, AutoCorrect as shipped changes the letters "CNA" to "CAN." If you have a customer named "CNA Shipping," AutoCorrect makes entering the customer's information in the database frustrating because it constantly changes it. In this example, you may have to customize the AutoCorrect behavior on each user workstation to remove the suggestion for "CNA" to protect data during entry and editing.

Carefully reading this lengthy listing of access menu and toolbar commands should leave you with the impression that almost every built-in Access menu option has a specific value in a specific application but that most options also have the ability to place power in the hands of the user that your application cannot easily detect or control. As you build an application, you must review this list and consider which built-in Access features are appropriate to place on your application's menu system, which features should be provided by program code instead, and which features are not appropriate at all.

Restricting Access from the Database Window

By definition, an application usually does not include the ability for users to wander in the Database window. Some applications built for sophisticated users may provide navigation from a switchboard menu as well as allowing some work to be done within the Database window. This provides the developer with a conundrum, as follows:

Access security provides the only fail-safe mechanism for limiting users' ability to interact with your objects directly. In its absence, you can use the following techniques to limit the risk when users wander into the Database window:

You may want to check and set a user's workstation options when he or she opens the database to disable the display of hidden objects. The following code resets the user's Show Hidden Objects option setting:

Application.SetOption "Show Hidden Objects", False

When an object is hidden via the Properties dialog box, the attribute is stored as part of the object's Properties collection within its Document object in the DAO. The hidden attribute of the object is read-only within the document, and therefore can be determined but not set from code.

Fig. 15.8

Set the property to hide an object from its Properties dialog box.

Listing 15.4 AES_Frm3.Mdb-Preventing a Subform from Opening Except via Its Parent Form

Private Sub Form_Open(Cancel As Integer)

' Purpose: Don't allow opening from the Database window

If Not lci_FrmIsLoaded("frmSentry_WithSubForm") Then


MsgBox "Open frmSentry_WithSubForm to see this subform.", _

vbOKOnly + vbCritical, Me.Name

Cancel = True

End If

End Sub

Additionally, Access provides a fairly clever capability that you can use to create self-policing objects, but unfortunately provides the capability only for reports and not for forms. The technique derives from a report's ability to execute its Open event before it actually looks for its RecordSource data. Using this capability, you can have a report link its own data tables into the database as it opens and delete the links as it closes. Thus, the tables are only available in the Database window while the report is printing, then they are gone from the user's view.

Listing 15.5 provides a sample of the Report_Open() and Report_Close() procedures that use this technique.

Listing 15.5 Open and Close Event Routines That Link a Report's Record Source

Private Sub Report_Open(Cancel As Integer)

' Purpose: Temporarily attach the report's source data

Dim dbs As Database

Dim tdf As TableDef

Set dbs = CurrentDb

Set tdf = dbs.CreateTableDef("tblCust")

tdf.Connect = ";Database=C:\DATA\CUSTOMER.MDB"

tdf.SourceTableName = "tblCust"

dbs.TableDefs.Append tdf



End Sub

Private Sub Report_Close()

' Purpose: Detach the report's source data

Dim dbs As Database

Set dbs = CurrentDb

dbs.TableDefs.Delete "tblCust"



End Sub

Note the following points about the techniques shown in Listing 15.5:

If users must see the Database window, the techniques in this section can at least reduce the number of unpleasant data situations that result from their interaction with application objects.

Deleting Form Records

Deleting the current record when using the expert forms model is not always as simple as allowing the user to press the Del key. Depending on the amount of control your application requires, you are faced with several techniques from which to choose.

There are many good reasons to control and monitor record deletions, not the least of which follow:

There are essentially three different record deletion scenarios to consider when you create an application form, as follows:

To support the first scenario, simply ensure that the AllowDeletions property of your form is set to True, as in Figure 15.9, and provide the user with menu or toolbar options for the Delete Record action. Access will perform the deletion and provide the alerts for confirmation and if a problem occurs.

Fig. 15.9

The AllowDeletions property of a form is located with its other data properties.

The second scenario is also easy to enable in its basic model. To disallow all deletions, merely set the AllowDeletions property of your form to False. If you have several forms in an application that allow deletions and other forms that do not, however, your users may not readily understand the selective limitations. If your forms have been set to disallow deletions, you cannot intercept the form's deletion events and show a message to the user explaining the situation (the form's Delete event no longer fires). In such a case, you must employ a combination of the techniques described in the following two sections to selectively trap deletion attempts and provide an alert instead of a deletion.

When you allow some users to delete some records while retaining strong program control of the event, you must use the deletion event model as described in the next section.

Detecting, Enabling, and Disabling Deletions

For users to use a form to delete records selectively, the form's code must determine who can delete which record and when. There are a variety of scenarios that you will want your forms to be this smart.

One example involves record ownership. Assume that each record in your form has an ownership field indicating that a designated department or user is responsible for the record. The form may need to determine whether the current user is in this ownership group for the record and disallow deletions if he or she is not.

Allowing and disallowing record deletions using program code is not a substitute for applying an Access security layer where appropriate. Some record ownership logic cannot be expressed by the security model alone, however, because Access security applies to tables and forms but not specific records. Many of your applications will need to balance both programmatic security and built-in security, and to disallow direct user interaction with table data.

You cannot disallow a record deletion at the form level unless your application watches each backdoor deletion provided to the user. In the following ways, a user can delete records:

In addition, Access provides several very powerful and terribly dangerous bulk-deletion options. The existence of these options and their danger to unsophisticated users provides another good reason to regulate record deletions. A user can delete all the records in a form's recordset with the following techniques:

To watch for deletion events, you most often place code in the Delete event for the form. This event is triggered at the point where the user initiates the deletion but before any built-in alert is shown or any actual action is taken. You have the following two choices when the Delete event fires:

In addition to the Delete event, Access also provides the BeforeDelConfirm and AfterDelConfirm events. You can place code in these events to manage the display of the delete confirmation message and to test the post-deletion status of the action. These events do not fire if the user has cleared the Confirm Record Changes check box in the Options dialog box, however, so use these events at your discretion or fix the user's option setting during application initialization.

Also be aware that when the BeforeDelConfirm event fires, the record has been removed from the form's recordset and the next record has become the current record. Consequently, any references in your event code to the current record in the form will not point to the deleted record.

It is possible to selectively allow and disallow deletions while letting Access manage the work:

  1. In the Current event of a form, run code to detect whether the current user is allowed to delete the current record.
  2. Set the AllowDeletions property of the form equal to the user's deletion rights. Changing this property setting does not cause Access to query again the form's recordset, so there is no negative visual or performance impact of this property change.
  3. Access detects the property change and enables or disables the Delete Record menu option and toolbar button accordingly.

Let's examine the two approaches I just described for selectively allowing a deletion. Assume that your company's customer database has customer records with unique ID numbers starting at 2. The number 1 is reserved for your parent company to whom you sell (transfer) products as well. Under no circumstances should users be able to delete the record for the parent company.

You may approach this problem by disallowing a deletion of the parent company record via code in the form's Delete event. The code would look like the first event in Listing 15.6.

Alternately, you can make the form smarter and the interface friendlier by disabling the built-in deletion capabilities of the form when the parent company record is the current record, as shown in the second event routine in Listing 15.6.

Listing 15.6 AES_Frm3.Mdb-Two Different Code Approaches to Disallowing Deletion of a Specific Record

Private Sub Form_Delete(Cancel As Integer)

' Purpose: Do not allow deletion from UI of parent company record

If Me!txtCustomerID = 1 Then


MsgBox "Cannot delete parent company record.", _

vbOKOnly + vbCritical, "Disallowed"

Cancel = True

End If

End Sub

Private Sub Form_Current()

' Purpose: Turn off the form AllowDeletions property when ID = 1

Me.AllowDeletions = (Me!txtCustomerID <> 1)

End Sub

Either method is equally effective; they simply have a different manifestation to the users.

Once you have set the AllowDeletions property of a form to False, your code cannot use the RunCommand method to initiate a deletion. Access will generate the error message The command or action Delete Record isn't available now. You must use code in this circumstance.

Deleting Records via Program Code

With Access's innate abilities to manage record deletions and to alert users when they occur, under what circumstances would you not simply let an Access form provide record deletions? Actually, there are quite a few scenarios in which you may desire tighter control of record deletions. The following are a few examples:

In scenarios such as this, you may choose to delete records with program code. At the simplest level, you can use program code to delete a record by calling on the deletion capabilities of Access:

Application.RunCommand acCmdDeleteRecord

You can trigger such code from a menu or toolbar option or from a custom button on the form or other event. When you depend on Access to notify you of a deletion, place code in the Delete event for the form. This event has a Cancel argument that your code can set to True to override the built-in delete routine that would be provided by Access. The remaining code in the event can then proceed with your programmatic deletion, as in Listing 15.7.

Listing 15.7 A Delete Event Shell for a Programmatic Deletion

Private Sub Form_Delete(Cancel As Integer)

' Purpose: Code shell to enable programmatic deletions

Cancel = True ' Cancel the internal event

' Check the user's rights to delete the record

' Validate and save the record

' MsgBox to ask if the user is sure

' Create a backup copy of the record or audit log entry

' Delete the record

End Sub

You cannot rely on Access to notify you of a deletion when the AllowDeletions property of the form is set to False. This property setting causes Access to remove Edit, Delete Record from the menu and to disallow related keyboard shortcuts, such as Ctrl+Minus Sign ([ms]). In such a case, you must add a custom delete function to your form and attach it to a custom event. The user can call the event by a button, a custom menu option, or a related event.

The code in Listing 15.8 shows a programmatic record deletion triggered by a custom button on the form.

Listing 15.8 AES_Frm3.Mdb-A Custom Single-Record Deletion
Routine Called by a Form Button

Private Sub btnDelete_Click()

' Purpose: Delete a record using program code

On Error GoTo btnDelete_Click_Err

Const cstrProc As String = "btnDelete_Click"

Dim blnInTrans As Boolean

Dim dbs As Database

Dim rst As Recordset

Dim wsp As Workspace


If MsgBox("Permanently delete customer " & Me!txtCompanyName _

& "?", vbYesNo + vbExclamation, "Delete") = vbNo Then

GoTo btnDelete_Click_Exit

End If

' If writing an archive record, save and test the record first

' so the archive copy can be un-archived and remains valid

' Alternately, send Me.Undo before deleting to clear edits

If Me.mtdValidate Then ' Save and test the record first

Set wsp = DBEngine.Workspaces(0) ' Current workspace

Set dbs = wsp(0) ' Current database

Set rst = dbs.OpenRecordset("SELECT * FROM tblCust WHERE CustomerID = " _

& Me!txtCustomerID, dbOpenDynaset)


blnInTrans = True

' Your deletion code could write audit records here

' or save a copy of the record before deleting

rst.Delete ' Delete the record on the form

' Your code can delete child or related records here


blnInTrans = False


End If


On Error Resume Next



Exit Sub


Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number _

, Err.Description, True)

If blnInTrans = True Then


End If

Resume btnDelete_Click_Exit

End Sub

Note the following points that Listing 15.8 reveals:

One simple way to delete a form record is to use the RecordSetClone object. You cannot wrap code lines using this object in a transaction, however; therefore, you cannot protect the following delete routine as well as you can protect the code shown in Listing 15.8:

Me.RecordsetClone.Bookmark = Me.Bookmark



Deleting records through code sounds like a winning strategy, and it is. It provides you as the developer with complete control of one of the most important events in a database application.

Selectively Disabling Features

When your application needs to restrict access to specific controls or processes, it can "switch" form controls and features on and off as needed. Developers can easily give users selective access to specific features and controls in Access by using any combination of the following techniques:

You can use the new Tab control in Access to create a group of controls bound to one rectangle, and enable or disable all of the controls together by simply setting the Enabled property of the tab.

If a significant number of controls or features apply to one group of users and not another, consider grouping the controls or features onto different form pages. Then you can simply disable the ability to go to a particular page for the restricted users. Limiting access to a form page involves trapping the keystrokes, including PgDn and Tab, that take users between pages, as described earlier in this chapter.

Validating Important Data

The most important and complex element of expert forms is data validation. Without good data, your application is worth nothing.

An application with an unfriendly interface is an annoyance, an application with imperfect features is a work in progress, but an application that does not collect accurate data and protect it is truly a disaster.

Depending on the needs of the application, the skills of your users, and your personal development style, potentially complex program code may be required to fully validate a record before saving it. Some brief guidelines to follow:

The remaining sections in this chapter discuss these validation concepts and related techniques in greater detail.

Denoting Required Elements

Select your favorite phrase from the following two:

An application that does not tell the user what is expected of him or her regarding data validation and then pops up a host of validation failure alerts when it tries to save the record is essentially shouting the first phrase at the user. On the other hand, a friendly form that clearly denotes value ranges, required fields, and data types will enable the user to fill in data elements correctly the majority of the time. As each record slides smoothly past validation and out to some disk sector, the user receives the second phrase as implied feedback, and smiles wryly.

Application users have limited bandwidth that can be exceeded easily by the data-entry needs of a complex application if the following questions remain unanswered:

Users crave information that will help them understand the application better and receive the least amount of negative feedback from it. Chapter 17, "Bulletproofing Your Application Interface," describes user feedback in more detail, so for the purposes of this section, we talk only about required fields.

Designating When a Value Is Required

The most common interface challenge in database development is denoting required fields to the user. No Office applications or Office Compatible documents give insight to Microsoft's recommended method for noting data-entry requirements, so you must contrive your own. We decided to use asterisks next to required fields. For the rationale behind this design strategy, review our user research on the issue described in Chapter 8, "Designing Effective Interfaces."

Using an asterisk to denote required fields is simple, and you can let Access do the work for you. We place a small, disabled (but visible) text box next to each required field. Each text box has a formula similar to the following:


This expression places a red * character next to the label of a required field as long as the control is Null. When the user enters data into the field and moves to another control, however, the expression recalculates and the asterisk disappears. Figure 15.10 shows a form with some required fields noted via this technique.

Fig. 15.10

Required fields on this form that are not filled in are denoted with an asterisk.

In addition to this simple technique, you can contrive more elaborate schemes to denote required fields. For example, using the KeyPress event, your code can watch each keystroke coming to a control and update the asterisk or some other marker as characters are typed into a control. This has the benefit of removing the required field marker as soon as the first keystroke is pressed, rather than delaying until the user moves focus from the control, as in our model.

Recently, we have taken to denoting required fields in the table design as well. We place the string at the end of a field's Description property, regardless of whether the required nature of the field is enforced at the engine level (via the field's Required property or a validation rule) or by the application interface. The Description of the field is pulled into the StatusBarText property of related form controls, thus providing a message to users on the status bar that reinforces the data-entry requirements.

Conveying a Control's Data-Entry Requirements

Denoting required fields is only part of the equation. It is also desirable to tell users what type of data goes into each field tied to a control and what the size and "shape" of the data is. For this, you have five primary interface assistants, as follows:

Fig. 15.11

Placing a control tip on a form control assists users with data-entry requirements for the related field.

Once you have conveyed the data-entry requirements to the user, you must communicate the breach of these requirements as well. Validating data and notifying the user of a validation failure is discussed in the next several topics.

Validating at the Field Level

The term validation is not represented only by complex rules applied to fields and records. When you use the Access features in its purest form to set a field's DataType and FieldSize properties, you are using the product's more subtle validation capabilities. The majority of validation that Jet provides to Access is in the form of rules, however.

Jet validation rules (often called engine-level validation) are entered into tables via the following properties:

Fig. 15.12

Access displays a default message when a ValidationRule is broken.

The simplest method of validating form data is to create engine-level rules and then enable the form to use them. The relationship between a form and the validation rules in its underlying tables follows:

Field and table validation rules provide a straightforward way to validate data on a form. They do have a few negative characteristics, but many developers choose to live with the following annoyances:

To determine your application's validation strategy, you will need to experiment with table-level rules and compare their strengths and weaknesses to other validation options.

Validating at the Control Level

As noted in the previous section, most Access form controls provide a ValidationRule property that can be used to mirror, replace, or augment a table-level rule for the bound field. A control-level rule is superior to a field-level rule in that it can call expressions, your functions, intrinsic functions, and object references.

In addition, the nature of form and control events provide several candidate events for you to consider when you place validation code on individual controls, as follows:

Listing 15.13 Making Error Event Messages More Explicit

Private Sub Form_Error(DataErr As Integer, Response As Integer)

' Purpose: Provide intelligent validation alert

Const cintFldNull As Integer = 3314 ' Field can't be null

Const cintFldZLS As Integer = 3315 ' Field can't be ZLS

Const cintTblRule As Integer = 3316 ' Table rule failed

Select Case DataErr

Case cintFldNull, cintFldZLS, cintTblRule


MsgBox "Validation failure in " & Me.ActiveControl.Name _

& ": " & Choose(DataErr [ms] 3313, "Null not allowed.", _

"Zero-length string not allowed.", "Table rule failed."), _

vbOKOnly + vbCritical, "Customer Form"

Response = acDataErrContinue

Case Else

' Let Access show its message

Response = acDataErrDisplay

End Select

End Sub

In summary, bear in mind that placing too many different types of validation on a single form can lead to awkward event models and potentially confusing alerts for users. Nevertheless, because control-level validation is more powerful than field-level validation, it is a superior choice when the runtime or Access security can guarantee that users will not be interacting with table datasheets directly and bypassing validation on the form.

For more regulation of the validation process than rules provide, use code in a control's BeforeUpdate event for unlimited flexibility in the approach to validation and the message to users. Finally, to test each individual keystroke, consider the keyboard and Change events.

Validating at the Record Level

Your users will help you to determine the best validation model to use either based on an existing preference or via their feedback on your applications. For us, many of our users have migrated to PCs from other computer systems and are not only accomplished typists but are accustomed to high data-entry throughput. Consequently, they have provided much feedback to us over the years stating a preference for bulk validation in applications as opposed to validation that tests each field as it is exited.

Record-level validation works best when unencumbered by Jet validation rules popping up unpredictably. This means that stronger form validation comes at the cost of removing table-level and field-level validation. In applications that use the runtime or Access security, users do not have direct access to the underlying data without the form, and the lack of the regulation should not be a problem. In other situations, however, you must weigh the dangers of not using engine-level validation in favor of programmatic validation.

Bulk validation simply refers to the aggregate testing of an entire record at the end of the data-entry session, just before the record is saved. Users who are rapid typists or who are keying data into a system from hardcopy often prefer to type all the information into a form record and only then to validate the data.

To facilitate bulk validation, place your testing code in the form's BeforeUpdate event procedure or at least call your validation routine from that event. BeforeUpdate is triggered when your code attempts to save the current record as part of navigation or closing the form as well as when the user saves the record via a menu selection or keyboard shortcut before heading for lunch. Listing 15.14 shows a sample event procedure calling a validation routine.

Listing 15.14 AES_Frm3.Mdb-A BeforeUpdate Event in a Form That Calls
Record Validation

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Purpose: Test and save the record

If Not Me.mtdValidate Then ' Didn't survive validation

Cancel = True


' Stamp the log fields in the record before saving

Call lci_FrmRecStamp(Me)

End If

End Sub

The record stamping function, lci_FrmRecStamp(), in Listing 15.14 is discussed in Chapter 17, "Bulletproofing Your Application Interface."

The validation routine, mtdValidate, referred to in Listing 15.14 is a public procedure in the form serving as a validation method that can be called from inside or outside the form to check the current record. It can be as simple or complex as needed to facilitate the objectives for the data. The following are three approaches to bulk validation:

Listing 15.15 AES_Frm3.Mdb-A Brute-Force Form Record Validation Routine

Private Function mtdValidate() As Boolean

' Purpose: Validate the current record

' Returns: True/false, True=Record is valid

Dim blnValid As Boolean

Dim ctl As Control

Dim strValid As String

blnValid = True ' Assume record is valid

' Let the user leave an empty record

If Not Me.Dirty And Me.NewRecord Then

mtdValidate = True

GoTo mtdValidate_Exit

End If

' Validate rules

If lci_IsBlank(Me!txtCustomerID) And blnValid Then

Set ctl = Me!txtCustomerID

strValid = "Customer ID is required."

blnValid = False

End If

' Add additional rules here

If blnValid Then ' Passed all validations

mtdValidate = True



MsgBox strValid, vbOKOnly, "Validation Failed"


End If


Exit Function

End Function

To make forms more friendly for our users, you'll notice in Listing 15.15 that I return focus to the first control that failed validation to make it easy for the user to begin the data correction process without requiring any focus changes.

Listing 15.16 AES_Frm3.Mdb-Form Record Validation Using the Tag Property

Private Function mtdValidate() As Boolean

' Purpose: Validate the current record

' Build and display a string of failures

' Required field tags format: "NotNull;prompt"

' Returns: True/false, True=Record is valid

Dim ctl As Control ' First control to fail

Dim ectl As Control ' Collection element

Dim intFailed As Integer ' Number of failures

Dim strValid As String ' Failure string

' Let the user leave an empty record

If Not Me.Dirty And Me.NewRecord Then

mtdValidate = True

GoTo mtdValidate_Exit

End If

' Validate all rules with a validation loop

For Each ectl In Me.Controls

If Left(ectl.Tag, 7) = "NotNull" Then

If lci_IsBlank(ectl) Then ' Failure

If intFailed = 0 Then

strValid = Mid(ectl.Tag, Trim(InStr(ectl.Tag, ";") + 1))

Set ctl = ectl ' First control to fail


strValid = strValid & ", " _

& Mid(ectl.Tag, Trim(InStr(ectl.Tag, ";") + 1))

End If

intFailed = intFailed + 1

End If

End If


If intFailed = 0 Then ' Passed all validations

mtdValidate = True


strValid = "The following required " & _

IIf(intFailed > 1, "fields are", "field is") & " blank: " _

& strValid


MsgBox strValid, vbOKOnly, "Validation Failed"

ctl.SetFocus ' Go to first failure

End If

End Function

Of course, there are more advanced types of validation than simply required fields. You can still handle a variety of validation needs with a tag loop by creating your own flags for different types of validation and including multiple flags in the control's Tag.

For each control that fails validation in Listing 15.16, the string to display to the user indicating the control's name is pulled from the Tag value. Starting with Access 97, you can take an alternate approach to this model by retrieving the Caption property from the Label control bound to the control being tested.

Each form control now has a Controls collection pointing to its subordinate controls. For all controls except for the Tab and Option Group controls, this subordinate collection has only one member-the bound label. Thus, to address the label caption for the label attached to a text box, use this syntax:


Your navigation and validation events must not collide. If necessary, place Debug.Print statements in your various form events to track and analyze the event flow of your validation scheme during testing. Alternately, print out your code and review the event flow of your validation logic.

Also, review Listing 15.10 for examples of code that carefully validate and save data before moving focus away from the current record.

All manner of simple and complex validation processes are available to you from VBA code. Some records require complex validation across multiple fields/controls while other forms demand that you create a recordset and perform domain operations to ensure the uniqueness of a value or the integrity of a relationship. Validation processes are limited only by the needs of your users and by your imagination.

From Here...

This chapter brought together a combination of concepts involving interface design, VBA coding, form event management, and data validation. Review the following chapters for more information on these subjects:

1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.