Private Sub MyAttachment_AttachmentCurrent()
‘ update the text boxes for form view where the
‘ default view is not a split form or datasheet view.
If (Me.CurrentView = AcCurrentView.acCurViewFormBrowse _
And Me.DefaultView <> AcDefView.acDefViewSplitForm
And Me.DefaultView <> AcDefView.acDefViewDataSheet) Then
Me.txtFileName.Value = Me.MyAttachment.FileName
Me.txtFileCount.Value = Me.MyAttachment.AttachmentCount
Me.txtFileType.Value = Me.MyAttachment.FileType
Else
Me.txtFileName.Value = Null
Me.txtFileCount.Value = Null
Me.txtFileType.Value = Null
End If
End Sub
With this code, you can now view the attachment filenames and file types (extension) as you browse
through the attachments. It may seem redundant to explicitly retrieve the file type because it typically
shows up in the filename, but the purpose here is to demonstrate how to retrieve the information.
This example code starts by retrieving the values for the attachment only if the form is not displayed in
Split Form or Datasheet view (we already learned that lesson). There may be issues or unexpected
results when trying to obtain these values from other form views, so the value is set to null for other
cases. In specifying the values, the left side of the equation identifies the text box that will display the
value retrieved. The right side identifies the source of the data; in this case, it is looking at the control
called
MyAttachment. Because it’s an attachment control, it has some special properties, including
FileName, FileType, and AttachmentCount.
❑
FileName: The actual name of the file (MyPicture.jpeg will display as MyPicture.jpeg).
❑
FileType: Refers to file extension or type (JPEG, TXT, BMP, PDF).
❑

AttachmentCount: The number of attachments stored for that record.
The event
AttachmentCurrent works similar to the form’s OnCurrent event in that it fires when mov-
ing the focus from one attachment to another. Within this event you can both extract information and
specify the way that attachments will be represented. As the preceding code demonstrated, you can use
the
AttachmentCount property to list the number of attachments. It is helpful to use this count in con-
junction with the icon and image displays since they do not indicate the number of attachments. The
attachment control uses an image and has the following three display options:
❑
acDisplayAsIcon: Displays the default icon for that file.
❑
acDisplayAsImageIcon: Displays icons for txt files; actual picture for jpeg’s and bmp’s.
❑
acDisplayAsPaperclip: The default setting. Although this does not indicate the file type, it is
the only option that, by default, displays the
AttachmentCount.
303
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:25 AM Page 303
In the next example, an option group demonstrates the three different views to display the image. The
option group is added to the form, and Figure 10-7 uses the
DisplayAsImageIcon option in showing
the same record and attachment shown in Figure 10-6.
Figure 10-7
The following code allows the user to choose whether the attachment control will display as an icon,
icon/image, or paper clip.
Private Sub grpAttachView_Click()
Dim OptSelect as Integer
OptSelect = Me.grpAttachView.Value

Select Case OptSelect
Case 1
With Me.MyAttachment
.ControlTipText = “This option displays file Icons only.”
.DisplayAs = acDisplayAsIcon
.Height = “450”
End With
Case 2
With Me.MyAttachment
.ControlTipText = “This option displays file Images and Icons.”
.DisplayAs = acDisplayAsImageIcon
.Height = “1440”
End With
Case 3
With Me.MyAttachment
.ControlTipText = “This option displays files as a Paperclip.”
.DisplayAs = acDisplayAsPaperclip
End With
End Select
End Sub
For options acDisplayAsIcon and acDisplayAsImageIcon, you add the Height property and set it
to
“450” twips and “1440” twips (1 inch), respectively. Why specify the height? Because the default
height for an attachment control that is set to
acDisplayAsPaperclip is only 0.1667 inches. By specify-
ing the height in code, the control can grow or shrink as appropriate for the display type.
304
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 304
When using code to specify the height and width in twips, do not use commas as regional settings may

cause the comma to be interpreted as a decimal point. Notice that the code uses 1440 not 1,440 and that
the numbers are enclosed in double quotes.
While you are at it, you may also want to use the Property Sheet Format tab and change the Picture Size
Mode to
Zoom, which will preserve the image proportions. If you are concerned about space or present-
ing a compact look, you may also want to adjust the cell padding around images and icons. Figure 10-8
illustrates the effects of several of these settings.
Figure 10-8
The attachment control has its own pop-up menu for adding, removing, opening, saving, and moving
through attachments. This is a powerful little control because it works like the
OpenFile dialog box but
saves the collections of files within the record rather than in a separate table, streamlining the process of
saving or attaching files to a record. However, with the built-in control, you must select working with
none, one, or all attachments. Unlike a fully functional multi-select group, it does not allow users to
select several (but not all) attachments.
Combo Boxes
The combo box is a potent control that can combine the processes to search, select, and populate data
into fields (or other objects), as well as limit the values that a user may enter. Many nuances affect how
I don’t know about you, but I certainly am not accustomed to calculating in twips.
So, if you are trying to specify image heights, you may appreciate knowing that a
twip is
1
⁄1,440 of an inch, which is the equivalent of
1
⁄567 of a centimeter or
1
⁄20th of a point.
305
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 305

combo boxes work; this section addresses some of the more common ones. It also covers some new fea-
tures, including two new properties:
Allow Value List Edits and List Items Edit Form.
The combo box and list box have a lot of the same properties and functionality. The primary difference is
that the contents of the list box are always displayed on the form, so the list box takes more space. List
box values are also limited to the list, which can be updated programmatically by adding values to the
row source, but not directly by the user. Combo boxes, on the other hand, generally use the space of a
normal text box and employ a drop-down list to display the number of rows specified in the properties.
The row source can be set to be updateable or to allow only existing values.
Before continuing, let’s clarify the difference between control source and row source. The control source
is specified if the value selected in the combo box will automatically (rather than programmatically) be
stored in a field. The last question in the Combo Box Wizard, for instance, is about remembering or stor-
ing the selected value. If you choose to store the selected value in a field, that field becomes the control
source. The row source provides the values that are displayed and used by the combo box. It can be a
table, a query, a value list, or a field list. The row source value can even be an empty string when the
form opens and then be set programmatically. For the most part, the row source consists of multiple
columns with only one column visible to the user. The other columns can be used to populate other
fields or as the link to other record sources. For example, one column could be the field that links the
current form to a subform. Again, referring to the wizard provides a quick demonstration of some of the
properties related to the row source. When you tell the Combo Box Wizard, for instance, that the combo
box will look up values in a table or query, you are specifying the row source. The wizard then goes
through the process of selecting the fields for the columns and allowing you to specify the column
widths. The wizard automatically adds the primary key if one is available.
The other main element that you’ll work with is controlling the values that the user can use or add. But
first, you’ll tackle an easy example of using a combo box to display existing data.
Combo Box as a Look-Up
An excellent use for the combo box control is to display descriptive information for the user even though
the data that’s actually stored in the database is the value of some sort of key (primary or foreign). For
example, you may have a form in which users allocate sales and you want the users to identify a depart-
ment. If that form (or even just the combo box) is bound to a table that stores the Department ID rather

than the name of the department, you don’t want to force the user to remember or figure out which
Department ID to enter. Instead, you can use a combo box to display a list of departments (field name
Department) and let the form manage how the Department ID is stored. The following table lists the
combo box settings for this scenario. You will also find this in
frmContactsComboLookUp in this chap-
ter’s database file named
ComboBox.accdb.
Property Value
Control Source Department (this is from the table Contacts)
Row Source Select DepartmentID and Department from tblDepartment
Row Source Type Table/Query
Bound Column 1
Limit To List Yes
306
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 306
Property Value
Allow Value List Edits No
List Items Edit Form No value (leave blank)
Column Count 2
Column Width 0”;2”
Two of the properties that are crucial to selecting the value that will be stored in the database are Bound
Column
and Column Width. Setting Bound Column to 1 means the data for the field that will be stored
in the database is in the first column of the
Row Source. Setting the Column Width of the bound col-
umn to
0 means that the bound column will not be displayed in the combo box.
If you are familiar with coding combo boxes, you probably noticed the new Access 2007 Combo Box
properties

Allow Value List Edits and List Items Edit Form. These new properties enable you
to let users maintain their own set of valid values for the field without requiring you to write code. The
next two sections examine those properties.
As in prior versions of Access, if the
Limit To List property is set to No, the user is allowed to type
whatever he wants as many times as he wants. The combo box only helps the user select previously
defined values without restricting him to only those values, and it allows users to enter the same value
multiple times and with myriad variations. When
Limit To List is set to Yes, the user can select only
the values in the list. However, if the user enters a value that isn’t in the list, it can trigger a response
depending on whether you’ve captured bad data entry using the
On Not In List event and/or based
on the settings for the new
Allow Value List Edits property along with the List Items Edit
Form
property. All this is by way of saying you should be aware that the On Not In List event occurs
before the settings of the new properties are even considered.
It may seem as though we are hammering on just a few properties, but slight variations in how they are
combined can have a big impact on results.
Allow Value List Edits
The Allow Value List Edits property provides a simple way for you to allow the user to change the
contents of the list without requiring the user to go into Design view to change the row source or you
(the developer) to write code to change the row source. Keep in mind that this property is intended for
combo boxes where the
Row Source Type property is set to Value List — meaning that it is not
designed to work with other row source types, so don’t be tempted to misapply this feature.
If
Allow Value List Edits is set to No and the user enters a value that isn’t in the list, the user will
see the standard Access message (see Figure 10-9) advising him to select an item from the list.
Figure 10-9

307
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 307
If Allow Value List Edits is set to Yes and the user enters a value that isn’t in the list, the user will
see a message box that asks if he wants to edit the items in the list, as shown in Figure 10-10a. You’ll also
notice the extra question at the bottom of the message box, “Was this information helpful?” That mes-
sage will appear if the user has enabled “Help Improve Access.” Responding to this query will clear that
message without closing the message box, as shown in Figure 10-10b.
Figure 10-10
If the user chooses Yes, to edit the list, the default Access
Edit Items List form displays (see
Figure 10-11). This default form is clearly designed to capture only a simple list of values, and it is
intended to be used only when the combo box
Row Source Type is set to Value List, Inherit
Value List
is set to Yes, and both the the Bound Column and Column Count are set to 1. Keep in
mind that this form provides no means for you to control the values that the user might enter, so terms
may be entered multiple times with different spellings. In Figure 10-11, you can see that Dance has been
added to the list from the combo box labeled “
Value List With Edit:” even though “Dancing” was
already in the list. If you want more control over what the user enters for the list or if you want to pro-
vide a multicolumn list, you should set the
Row Source Type to be Table/Query and use the List
Items Edit Form
property to specify a custom form. We will discuss that approach shortly.
Figure 10-11
One thing to note about using this approach to allow value list edits is that the
Row Source property
of the combo box is actually updated, which in turn causes Access to respond as though the design of
the form has changed. Because of this, when the user closes the form that contains the (updated) combo

box, Access will display a message box to ask if the user wants to “…save changes to the design of the
form [form name]”. This is not a good message for users to see, so you may want to use the
DoCmd.Save
308
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 308
acForm, Me.Name statement to save the form in the Form_AfterUpdate event. (This is obviously not a
desired scenario, and you can hope for a resolution in an early Access update.)
List Items Edit Form
As mentioned earlier, the big advantages of the new Allow Value List Edits property is that you
don’t have to write code in the
On NotIn List event to capture the new value, and it provides a
method that allows the user to include the values he wants in the list. But please remember (and this is
worth repeating) that the new
Allow Value List Edits property is intended only for combo boxes
and list boxes with a
Row Source Type of Value List. If you are using the Row Source Type of
Table/Query
, you can use the new List Items Edit Form property to display an alternate form that
allows the user to edit the list.
First you must first create a form that will maintain the data that is displayed from the
row source of
the combo box. Then you set the
List Items Edit Form property to name of that form. You could get
a little fancy with your form by programmatically taking the invalid value that the user entered in the
combo box and loading it into the appropriate field on your edit form. That kind of defeats the concept
of using this new feature to avoid writing code, but then this book is about code and creating solutions
that save time and prevent errors. Coming up is one example of using code to optimize use of a couple
of the new “no code required” features.
Because users can use the Navigation pane, it might be beneficial to programmatically restrict the capa-

bility to open your edit forms, such as by using the
Is Loaded event (see discussion later in this chap-
ter). After all, you don’t want users to get an error message if they open a form from the Navigation
pane. (In the database for this section, EditValueList.accdb, the form frmMayorUpdate includes a
method to prevent the form from being opened from the Navigation pane.)
This example uses a form called
frmContactMayor with combo box cboCity, which uses a look-up
table to capture the city for the contact, storing the data in the field named
City in tblContacts. For
this exercise, assume that the application needs to capture the mayor of every city. Basically, this means
that if a user enters a new city, you want the application, through a form, to require him to enter the
name of that city’s mayor. To accomplish this, the example uses a table of mayors called
tblMayors and
a
frmMayorUpdate form to update that table. You also want to think about creating an index on
city/state and requiring that to have a unique value.
This example is provided in the chapter download file, EditValueList.accdb. To get the process started,
you’ll create (or modify) the combo box,
cboCity, to have the key property values shown in the follow-
ing table.
When allowing value list edits by using the default Access Edit Items List form,
it is critical that the
Row Source Type is Value List and Inherit Value List is
set to Yes. This allows Access to properly handle the update. With other property
configurations, users may get a warning and be asked if they want to save changes
to the design of the form. After all, a change to a value list behind a control is a
change to the form’s design.
309
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 309

Property Value
Control Source City
Row Source tblMayors
Row Source Type Table/Query
Bound Column 2
Limit To List Yes
Allow Value List Edits Yes
List Items Edit Form frmMayorUpdate
Column Count 2
Column Widths 0”;2”
To ensure that the mayor’s name is provided, the form should use one of the other techniques you’ve
learned in this book. For instance, you could use the
Form_BeforeUpdate event to see if the mayor’s
name field has a value. The example uses
Form_Open to check for the value.
Additionally, set the
frmMayorUpdate form Pop Up property to Yes, and set the Modal property to Yes,
and set Cycle to
Current Record (found on the Other tab of the property sheet). You do this so that the
Form_Open code in the form frmMayorUpdate always executes. One reason for making this a modal form
is to force the user to close the form before he can enter additional data in the form
frmContactMayor (or
before he can work with other objects, for that matter). If the user is allowed to leave
frmMayorUpdate
open while continuing to enter data on frmContactMayor, the next invalid entry in cboCity won’t fire
the
Form_Open event in frmMayorUpdate.
At this point, you’re about ready to put the following code in the
Open event for frmMayorUpdate.
First you establish a constant to be used for the “calling form.” That makes this snippet more portable.

Instead of replacing the name of the calling form throughout the code, you merely change the value for
the constant,
cFormUsage.
Const cFormUsage = “frmContactMayor”
Private Sub Form_Open(Cancel As Integer)
Dim strText As String
Dim rs As Recordset
‘ Don’t let this form be opened from the Navigator
If Not CurrentProject.AllForms(cFormUsage).IsLoaded Then
MsgBox “This form cannot be opened from the Navigation Pane.”, _
vbInformation + vbOKOnly, “Invalid form usage”
Cancel = True
Exit Sub
End If
strText = Forms(cFormUsage)!cboCity.Text
If strText = “” Then
‘ If the City is empty, the user may have opened the form from the navigator
310
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 310
‘ while the other form is opened (thus it passed the above test)
MsgBox “This form is intended to add Cities for the ‘“ & i
Forms(cFormUsage).Caption & “‘ form.”, _
vbInformation + vbOKOnly, “Invalid form usage”
Cancel = True
Exit Sub
End If
‘ If you use the following syntax to insert the new value,
‘ make sure that the user hasn’t entered an apostrophy (‘) in his text.
‘ Of course there are many ways to add the record

DoCmd.SetWarnings False
DoCmd.RunSQL “INSERT INTO tblMayors (City) VALUES (‘“ & strText & “‘)“
Me.Requery
DoCmd.SetWarnings True
‘ Now point to the row just added and set the filter so the user can’t scroll
Set rs = Me.RecordsetClone
rs.FindFirst “City = ‘“ & strText & “‘“
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Filter = “[ID] = “ & Me.ID
Me.FilterOn = True
End If
Me.Mayor.SetFocus
End Sub
After the user indicates that she wants to edit the items in the list, the process will open frmMayorUpdate
because it is the form specified as the value for the List Items Edit Form. The Form_Open event looks
at the
cboCity field in frmContactMayor and stores the text of that field in strText. The code then
inserts the City that doesn’t have a value associated with the Mayor’s name into
tblMayors. With the
record inserted, the code makes a copy of the recordset of the form, uses the
FindFirst method to locate
that record, and moves to it using the
Bookmark property. Finally it sets the focus on the Mayor name
field, so that it is easy for the user to enter the required data.
Of course, you could accomplish that with a lot less code, as shown in the following snippet:
Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToNew ‘ insert a new record
Me.City = Forms(“frmContactMayor”)!cboCity.Text
Me.txtMayor.SetFocus

End Sub
This code simply goes to a new record on the form and copies the text from cboCity to the City
field. An advantage of this method is that you can then define the Mayor name field in
tblMayors as
required and let Access determine if the data entered by the user is valid. Then you don’t need the
Form_BeforeUpdate check to make sure the user entered something in the Mayor name field.
Now that you’ve worked through this example, take a moment to think about related business rules.
This scenario has some obvious issues that would have to be addressed, such as the potential to have
311
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 311
multiple cities with the same name, the opportunity for people to use different spellings of the same city,
and the need to be able to rename a city or mayor. But it does provide a fairly elegant process to allow
users to add values.
If you prefer to write code (and most developers do), there is another way to allow the users to update
the list of valid values available in a combo box. It provides the means for you to validate the value and
saves you from having to create another form. It’s the
Not In List event, which you look at next.
Not In List()
The Not In List event is triggered when the Limit To List property is set to Yes and the user
enters data that is not in the list. It occurs independently of the settings for
Allow Value List Edits
and List Items Edit Form properties, so you can use it to control how your application responds
when invalid data is entered in a combo box.
Because combo boxes are usually based on a look-up table, the following example offers the user a
chance to add a value that is not in the list. To provide a friendlier dialogue with the user, it also demon-
strates how to create a custom message box such as the one shown in Figure 10-12. As you can see, the
user tried to use “Entertainment” as the main category. Because it isn’t in the list, he’s asked if he’d like
to add it.
Figure 10-12

As a refresher, you can use the
Limit To List property to control what happens when a user enters
data into a control. If the value is set to No, it places no restrictions on what is entered. If the value is set
to Yes, several things can be triggered, including the following:
❑ The user is limited to what is in the list.
❑ If other data is entered, users are asked to choose from the list.
❑ Entering other data can trigger the
NotInList event.
Because combo boxes are typically based on a look-up table, the following example provides code to
offer the user a chance to add a value that is not in the list. It also creates a custom message box.
Private Sub cboMainCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_Handler
Dim intAnswer as Integer
intAnswer = MsgBox(“”“” & NewData & “”“ is not an approved category. “ & vbcrlf _
312
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 312
& “Do you want to add it now?”, _
vbYesNo + vbQuestion, ”Invalid Category”)
Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL “INSERT INTO tlkpCategoryNotInList (Category) “ & _
“Select “”“ & NewData & “”“;”
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
Msgbox “Please select an item from the list.”, _
vbExclamation + vbOkOnly, “Invalid Entry”
Response = acDataErrContinue

End Select
Exit_Procedure:
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & “, “ & Err.Description
Resume Exit_Procedure
Resume
End Sub
The NotInList event comes with two parameters:
❑
NewData As String: Holds the value that is not found in your list.
❑
Response As Integer: Provides three intrinsic constants:
❑
acDataErrContinue: Suppresses the standard error message.
❑
acDataErrAdded: Suppresses the standard error message, and refreshes the entries in
the combo box.
❑
acDataErrDisplay: Displays the standard error message.
The
NotInList event property is literally asking, “This value is not in your table, so what should I do?”
The example starts by telling Access to display a message box notifying the user that the name is not in
the list. You use the
vbYesNo constant to provide buttons to get the user’s answer and the vbQuestion
constant to display the Question icon in the message box.
The user can choose Yes or No. If Yes (
vbYes) is selected, the code adds the new value using the INSERT
INTO

SQL command that appends NewData to the specified lookup table. Because it is an append query,
the standard append query warnings are triggered.
Docmd.SetWarnings False turns off these warn-
ings, and later you use
Docmd.SetWarnings True to turn them back on.
Next, you need to set the
Response, one of the intrinsic constants for the message box. If the user has
responded Yes,
acDataErrAdded automatically refreshes the combo box. If the user chooses No, Response
is set to equal acDataContinue, which allows the user to continue without adding the item to the list.
Either of those constants (responses) will suppress the default Access message. The point here is that by
creating custom messages boxes you proivde a more informative and professional user interface.
313
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 313
Field List
One of the values for Row Source Type that hasn’t been mentioned yet is Field List. Setting the Row
Source Type
to Field List makes the values displayed in the combo box be the actual names of the
fields (rather than the data contained in the field) found in the table or query specified in the row source.
The
Field List value is most commonly used when you’re going to allow the user to select field
names to build a custom filter. Of course, Access 2007 already provides some powerful new ways to fil-
ter a form, so they may be a better alternative.
But if your form has several fields, a drop-down field finder might help users quickly get to the fields
that they are looking for. To create such a tool, first add an unbound combo box to a form. In the combo
box properties, set the
Row Source Type to Field List. Then, in Row Source, enter the table or
query that is used for your form. In the
AfterUpdate event of the combo box type the following code:

Private Sub cboSearchFields_AfterUpdate()
Dim strFieldChoice as String
strFieldChoice = Me.cboSearchFields.Value
DoCmd.GotoControl strFieldChoice
End Sub
The user can select the field from the combo box and be taken straight to that field. This code assumes
that the
Name property of the controls on your form are the same as the names of the fields in your Row
Source
. However, that probably isn’t the case because you’ve implemented good naming conventions
so the controls have prefixes such as
txt; in which case you’ll have to interpret the names. The following
Select Case statement is an example of how to do that:
Private Sub cboSearchFields_AfterUpdate()
Dim strFieldChoice as String
strFieldChoice = Me.cboSearchFields.Value
Select Case strFieldChoice
Case “CustomerFirstName”
strFieldChoice = “txtCustomerFName”
Case “CustomerLastName”
strFieldChoice = “txtCustomerLName”
End Select
DoCmd.GotoControl strFieldChoice
End sub
The user will see the easy-to-interpret term CustomerFirstName but the code in the Select Case
statement switches the value to equal the actual Name property of the control: txtCustomerFName.
Synchronizing Two Combo Boxes Using AfterUpdate()
It has become rather popular to synchronize combo boxes (often called cascading combo boxes). The
value selected from the first combo box updates the second combo box so that it contains only records
related to the item selected in the first combo box. In the following example (

ComboBox.accdb in the
314
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 314
chapter download), the row source of the first combo box (cboMainCategory) is set to tlkpCategory
and the second combo box (cboSubCategory) needs the row source to display the values from
tlkpSubCategory where the foreign key for each subcategory matches the values of the
MainCategoryIDs. This is a common function when there is a one-to-many relationship between two
tables. In other words, each main category can have many subcategories (as one sales rep has many cus-
tomers) but only certain subcategories are valid for a given main category.
One way to synchronize the combo boxes is to add the following snippet of code to the
AfterUpdate
event of the first combo box (cboMainCategory):
Private Sub cboMainCategory_AfterUpdate()
‘ bind data to the second combo box based on the value selected
If IsNull(Me.cboMainCategory) Then
Me.cboSubCategory.RowSource = “”
Else
Me.cboSubCategory.RowSource = _
“SELECT SubCategoryID, SubCategoryName “ _
& “FROM tlkpSubCategory “ _
& “WHERE MainCategoryID = “ & Me.cboMainCategory
End If
End Sub
While the row source of cboSubCategory is changed dynamically, the initial value of
cboSubCategory can be set to equal nothing. This ensures that the user cannot select a value from the
second combo box unless there is a value selected in the first combo box. If the user can scroll through
records on the form, you’ll also want to use this code in the form’s
On Current event to make sure that
the row source of the second combo box is reset every time the record changes. For that, we used the fol-

lowing snippet:
Private Sub Form_Current()
‘ bind data to the second combo box based on the value selected
‘ leave second combo box empty if there is nothing in the first combo box
If IsNull(Me.cboMainCategory) Then
Me.cboSubCategory.RowSource = “”
Else
Me.cboSubCategory.RowSource = _
“SELECT SubCategoryID, SubCategoryName “ _
& “FROM tlkpSubCategory “ _
& “WHERE MainCategoryID = “ & Me.cboMainCategory
End If
End Sub
Of course, there are a myriad other enhancements to add, such as having the drop-down lists display auto-
matically. But for now, we’re focused on the functionality of synchronizing combo boxes. Figure 10-13
shows the property configuration for the second combo box,
cboSubCategory.
Although only the value of the subcategory is displayed, there are two fields listed in the
Row
Source
. And if you open the Row Source query builder, you will see that all three fields of the table,
tlkpSubCategory, are included in the query. You can understand why it is critical for the query to
include the foreign key from
tlkpCategory because that is the field that links the two combo boxes and
provides the filter for the second combo box list. Comparing the SQL statement for the row source to the
315
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 315
query behind it is a good way to learn what makes this work so that you can apply the principles else-
where. You can also use this example to demonstrate that the value for the

Column Count can be based
on the Select statement in the Row Source (2 fields) or on the underlying query (3 fields). The critical
part is to ensure that the bound columns and column widths match the field configuration.
Figure 10-13
An alternative method for handling synchronized combo boxes is to code the row source of the second
combo box to point to the value in the first combo box. If the name of the form with the combo boxes is
frmMain, for example, instead of writing the preceding code, you could just make the Row Source of
cboSubCategory the following:
Select SubCategoryID, SubCategoryName From tlkpSubCategories Where i
MainCategoryID = Forms!frmMain!cboMainCategory
One of the easiest ways to create that type of Select statement is to use the Query Builder for the Row
Source
. After you have the correct properties, the trick is to make sure that the cboSubCategory field is
refreshed with the new set of valid values. A simple
Me.cboSubCategory.Requery statement will
work. For this example, you would put that code in the
cboMainCategory_AfterUpdate event and
also in the
Form_Current event.
The downside to this alternative method is that if you rename
frmMain, you have to remember that you
hard-coded the name in the
cboSubCategory row source and that will have to be updated to use the
form’s new name.
Regardless of the method, you can use cascading combo boxes for a multitude of purposes. This exam-
ple was simple, and it can easily be modified and incorporated into complex scenarios. Remember that
you can add additional rows to the row source and use them to auto-fill text boxes or as links to other
objects. You can also display additional fields in the combo box to assist users in selecting the correct
record. Keep in mind that the
List Width can be wider than the control width, so there is ample room

to briefly display data.
Combo boxes are a great way to help speed up the data entry processes for your user. In Access 2007,
Allow Value List Edits and List Items Edit Form enable you to eliminate some of the code that
we used to capture data-entry errors. Of course, you can still use the
Not In List event to trap user
input and provide special handling. The combo box is definitely worth spending some time learning
about so that you can leverage its versatility and benefits.
316
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 316
BeforeUpdate Event
The BeforeUpdate events can be used to validate user input, and as such, they are used most often on
the form or record rather than the field. During data entry, users are typically allowed to fill in all the
information for one record before they are prompted to resolve erroneous or missing. But that’s not
always the case, so we’ll explain both approaches.
The choice between using the
Form_BeforeUpdate event or the field_BeforeUpdate event is often
based on the type of validation that’s needed. In general, you use the
field_BeforeUpdate event for
required data or when the field value does not depend on other values that are entered on the form. The
following code example shows a test for
FieldOne, a required field.
Private Sub FieldOne_BeforeUpdate(Cancel as Integer)
If Trim(FieldOne.Value & “”) = “” Then
MsgBox “You must provide data for field ‘FieldOne’.”, _
vbOKOnly, “Required Field”
Cancel = True
End If
End Sub
The Cancel = True statement prevents the user from moving from FieldOne to another field until a

value is entered in
FieldOne. By concatenating the empty string to the value of FieldOne, and using
the
Trim string function, and then testing to see if the result is equal to the empty string, you take care of
the situation in which Access returns a Null value when the user hasn’t entered any data in the field as
well as the situation where the user may have simply pressed the spacebar.
You’ll want to use the
Form_BeforeUpdate event if your form is structured in such a manner that valid
values for one field depend on the value entered in another field. The following code snippet shows a
case where the value in
FieldTwo is required only when a value is entered in FieldOne. You’ll recog-
nize that this code does not address any aspects of preventing input unless a criterion is met. That is a
different business rule — one that is often handled by enabling and disabling controls.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(Me.FieldOne)) Or (Me.FieldOne.Value = “”) Then
‘ No action required
Else
If (IsNull(Me.FieldTwo)) or (Me.FieldTwo.Value = “”) Then
MsgBox “You must provide data for field ‘FieldTwo’, “ & _
“if a value is entered in FieldOne”, _
vbOKOnly, “Required Field”
Me.FieldTwo.SetFocus
Cancel = True
Exit Sub
End If
End If
End Sub
Because the user may have been on a field other than FieldTwo when she attempted to leave or save the
record,
SetFocus is used to move the cursor to the field that has the error. The Cancel = True state-

ment prevents the record from being saved. Otherwise, the user may get multiple error messages and be
317
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 317
able to address only the last message displayed.The Exit Sub statement isn’t essential, but it’s a good
coding technique to include the
Exit Sub statement at any point where you are reporting an error. And,
in many cases, you may come back to this code to add another validation check.
So, your choice of
Form_BeforeUpdate versus field_BeforeUpdate depends on the type of validation
performed. Use the
field_BeforeUpdate when you want to give the user immediate feedback about
invalid data. Use the
Form_BeforeUpdate when you have to perform cross-field validation checks.
The next example uses the
On Click event of a command button. First you need a simple form for data
entry. On the Property Sheet, set the form’s properties as shown in the following table.
Property Value
Allow Data-Entry Yes
Allow Edits No
Allow Deletions No
Make sure that the command button is the last control in the tab order, just before navigating to the next
new record. Now add the following snippet of code to the command button’s
On Click event:
Private Sub cmdCloseForm_Click()
Dim intAnswer As Integer
intAnswer = MsgBox(“Do you want to save this record?”, _
vbYesNo + vbQuestion, “Save or Cancel”
Select Case intAnswer
Case vbYes ‘ run through the validation

If (IsNull(Me.FieldOne)) or (Me.FieldOne.Value = “”) Then
MsgBox “You must provide data for field “FieldOne”.”, _
vbOKOnly, “Required Field”
‘ direct user to empty field and exit sub
Me.FieldOne.SetFocus
Exit Sub
End If
If (IsNull(Me.FieldTwo.Value)) or (Me.FieldTwo.Value = “”) Then
MsgBox “You must provide data for field ‘FieldTwo’.”, _
vbOKOnly, “Required Field”
Me.FieldTwo.SetFocus
Exit Sub
End If
Case vbNo ‘ give user a way out.
Me.Undo
End Select
DoCmd.Close acForm, Me.Name
End sub
318
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 318
This is similar to the Form_BeforeUpdate example. In this case, when the Close button is clicked,
the user is asked if he wants to save the record. If he selects Yes, the code runs through the steps in the
Select Case process and stops the form’s Close operation only if a field is empty. In that case, the
focus is set to the field in error and the code exits the procedure (
Exit Sub). If the user selects No, the
changes to the record are undone using
Me.Undo so that no changes are saved. If the process makes it
successfully through the
Select Case checks or if the changes are undone, the form closes as expected.

Keep in mind that if a method other than this
Close button is used to close the form, this validation
process will not occur. Of course, you could institute processes to cover contingencies.
One more point about testing for data in the field: The examples use
IsNull and a test for the empty
string and trimming and concatenating the empty string. Because Access trims spaces off of data entered
on the form, you’re not required to test for a single space entered in a field. The code used in the
field_
BeforeUpdate
example is just a technique used to simplify validations as well as ensure that Access
behaves the way you expect it to.
Saving E-mail Addresses Using Textbox AfterUpdate Event
Access 2007 is smarter about the way it stores website and e-mail addresses. Data in a hyperlink field is
automatically evaluated and tagged. The value is tagged with
http for websites or mailto for e-mail
addresses.
In previous versions, the Hyperlink data type stored e-mail addresses as Web addresses (
http://

, for example), so when you clicked the link, it opened Internet Explorer and
tried to find the website — not at all helpful. (One way to fix this is to right-click the link, select Edit
Hyperlink, and change
http to mailto by selecting e-mail Address in the dialog box. Be sure to copy
the e-mail address first because you have to add it again.)
However, you want the user to add an e-mail address and have it correctly stored so that he can later
click the link and open a new email message. The following code ensures that the initial input is stored
in the correct format. You can try it with a simple form to add customers and e-mail addresses; add this
code to the
AfterUpdate event of the EmailAddress field, txtEmailAddress.
Private Sub txtEmailAddress_AfterUpdate()

If Not IsNull(Me.EmailAddress) Then
Me.EmailAddress = Replace(Me.EmailAddress, “http://“, “mailto:”)
End if
End Sub
The first line checks to see if there is an e-mail address. If data is in the e-mail address field, it uses the
Replace function to replace http with mailto. The code makes this change on-the-fly without the user
even knowing.
This works great for sending e-mail to only one person. What if you need to send one e-mail to many
people? Assume there is a Contacts table and that e-mail addresses are stored in a field with a Text data
type rather than as a hyperlink. On your form, you can add a list box named
lstContacts, as shown in
Figure 10-14.
319
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 319
Figure 10-14
The list box
RowSource would equal tblContacts, so the column count and column width need to be
set to display the first name, last name, and e-mail address for each record. You’ll need to set the list box
property named
Multi Select (on the Property Sheet’s Other tab), which has three options:
❑
None: Select one item at a time from the list.
❑
Simple: Select blocks of items from the list by selecting one item and then holding down the
Shift key while highlighting the other items.
❑
Extended: Select multiple items by holding down the Ctrl key and then randomly selecting
items.
For this example, use

Extended.
Next, you need a command button named
cmdSendEMail. Add the following code to its On Click
event.
Private Sub cmdSendEMail_Click()
Dim emName As String, varItem As Variant
Dim emailSubject as String
Dim emailBody as String
emailSubject = “Confirmation Email”
‘note the carriage return line feeds to insert a blank line between
‘the greeting and the message body. Also, there is no need for the
‘ & at the beginning of the second and third lines because we used a
‘ comma after the greeting – just before the close quote on the first line.
emailBody = “Dear Customer, “ & vbCrLf & vbCrLf & _
“We are sending this email to let you know that your request has been “ & _
“confirmed. Please allow 2-3 weeks for shipping.”
On Error GoTo cmdSendEMail_Click_error
‘if there are no selected items (Contacts) in the list, exit
If Me!lstContacts.ItemsSelected.Count = 0 Then
Exit Sub
For Each varItem In Me!lstContacts.ItemsSelected
‘char(34) adds quotes to Me!lstContacts.
‘Then add a comma to separate the selected items
320
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 320
emName = emName & Chr(34) & Me!lstContacts.Column(4, varItem) & Chr(34) & “,”
Next varItem
‘remove the extra comma at the end
emName = Left$( emName, Len(emName) - 1)

‘send the message
DoCmd.SendObject acSendNoObject, , , emName, , , emailSubject, emailBody, _
True, False
cmdSendEMail_Click_error:
If Err.Number = 2501 Then
MsgBox “You just cancelled the Email. You’ll have to start over.”, _
vbCritical, “Alert!”
ElseIf Err.Number > 0 then
Msgbox “Error sending email.” & vbCrlf & Err.Description, _
vbCritical, “Send Error”
End If
End Sub
This code starts by declaring the e-mail name (emName), subject of the e-mail (emailSubject), and the
e-mail body (
emailBody) as String, and a variable to use the items in the list as Variant (varItem). Next,
it initializes the subject and body variables with string text. The first
If statement checks the count of the
number of selected items in the list, and if the count is zero, exits the routine.
The
For Each loop processes each of the items the user has selected. Because Multi Select is set to
Extended, the user can use the Ctrl or Shift keys with the mouse click to select items in sequence or
randomly.
With
tblContacts as the list box’s Row Source, each field defined in tblContacts becomes a column
in the list box. You can use the
Column property to access the value of each column in the list box. (This
applies to multicolumn combo boxes as well.)
As with most arrays in VBA, the index for the
Column property starts at zero; thus the first field in
tblContacts is the 0 column in the list box. In this example, the e-mail address is the fifth field

defined in
tblContacts, so in code this would be referred to as .Column(4).
When building
emName, a comma separator is added for each selected item in the list box. That leaves
you with an extra comma that you have to strip off after all of the e-mail addresses have been collected.
With all the items collected, use
DoCmd.SendObject with the following arguments to create the e-mail:
DoCmd.SendObject acSendNoObject, , , emName, , , emailSubject, emailBody, _
True, False
Selecting acSendNoObject means that the e-mail will not have an attachment because there is no object
(form/report) to send. After that, the commas are the place markers for the following nine arguments:
[ObjectName], [OutputFormat], [To], [Cc], [Bcc], [Subject], [Message], _
[EditMessage], [TemplateFile]
321
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 321
As in the example, [ObjectName] and [OutputFormat] can be left blank; [To] will be emName (the
selected items from the list box);
[Cc] and [Bcc] can be left blank; [Subject] will be the
emailSubject variable; [Message] will be emailBody; [EditMessage] is True just in case you need
to edit the message; and
[TemplateFile] is False.
Your users now have the option to send an e-mail to one contact or many contacts by simply selecting
the recipient(s) from the list box. Figure 10-15 shows an e-mail created by this example.
Figure 10-15
You’ll recall that this example uses an e-mail address from a text field. That is an important distinction
because if you use it with a hyperlink field type, the address line will look something like the following:
TO: #mailto:#
Most mail clients interpret this as one long string, and won’t treat it as a valid e-mail address.
Output to PDF

A common challenge for Access developers has been to have a way to send data or output to a PDF file
format. Sure, you could do it, but it wasn’t easy. Previous versions required the developer to obtain some
third-party software (CutePDF, for example) and then to build creative workarounds.
Now Access 2007 has streamlined the process by providing the bridge between Access and PDF utilities,
particularly if you download Microsoft’s free Add-in “Microsoft Save as PDF or XPS.” Instead of work-
ing with complicated code, you’ll have a
Save As PDF button on the Ribbon. Along with PDF, you
probably discerned that this add-in also offers the option to save in XML Paper Specification (XPS) file
format. To take advantage of this, you’ll need to download and install the file
SaveAsPDFandXPS.exe.
A link to the web is provided in the Access Help files. You can find it by typing XPS or PDF in the Search
text box or by going to Microsoft.com and searching for
SaveAsPDFandXPS.exe.
322
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 322
People will still need a PDF viewer to open PDF files. But viewers are commonly available as free down-
loads, such as the Adobe Reader. Windows Vista is required to view XPS files, but here again, a viewer
may become available.
To work with PDF and XPS file formats programmatically, Access has two new constants. They are
named in the following table.
File Type Constant
PDF acFormatPDF
XPS acFormatXPS
To see all of the constants, open the Object Browser and search for acFormat.
Both
DoCmd.SendObject and DoCmd.OutputTo use these constants in their arguments to define the for-
mat. The following code snippets show three different ways to use the PDF file format. There are minor
differences in the functionality. For example, you’ll notice that using
OutputTo allows you to specify the

export quality.
DoCmd.SendObject acSendReport, “MyFormName”, acFormatPDF, [To], [Cc], [Bcc], _
[Subject], [MessageText]
Docmd.OutputTo acOutputReport, _
“MyReportName”, acFormatPDF, , False, , , acExportQualityScreen
Docmd.OutputTo acOutputReport, _
“MyReportName”, acFormatPDF, , False, , , acExportQualityPrint
OpenArgs
What if you want to check for conditions or set certain properties when a form is opening? You can use
the
OpenArgs property of the form to pass parameters to the form when you open it. The OpenForm
method’s seventh (last) parameter supplies the value for OpenArgs, as shown in the following statement:
DoCmd.OpenForm “FormName”, , , , , , “OpenArgs value”
In this case, the value of the OpenArgs property would be “OpenArgs value”.
Because the
Open event triggers whenever a form opens, you can include the OpenArgs parameter on
the
OpenForm method to provide information that can be used even before the user sees the form.
An excellent application of this is to ensure that the form is opened by code and only under specific con-
ditions. For example, you may have a form that should be opened only by certain users. Because users
can open the form from the Navigation pane, it’s possible for any user to open a form — unless you
323
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 323
have implemented programmatic restriction. One approach is to use the following code in the form’s
OnOpen event to prevent it from being opened from the Navigation pane.
Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs() <> “Valid User” Then
MsgBox “You are not authorized to use this form!”, _
vbExclamation + vbOKOnly, “Invalid Access”

Cancel = True
End If
End Sub
If a user opens this form from the Navigation pane, she will see the message, You are not author-
ized to use this form!
The Cancel = True statement prevents the form from opening.
For a user to gain access to this form you must have code in your application that, after the user has
been approved, allows the form to open. For example, a form named
frmRestricted would need your
code to execute the following statement (be sure to specify other parameters as appropriate):
DoCmd.OpenForm “frmRestricted”, , , , , , “Valid User”
Another time to use OpenArgs might be when you automatically populate information in the form
you’re opening. Suppose you have a combo box that requires the user to select a valid value. If the user
enters an undefined value, you may want to pop up a form to collect information about the value just
entered — you might want to pass the text that the user entered to the form that you’re opening so it is
populated in the appropriate field on the pop-up form. Clearly you can utilize the
OpenArgs property
for a variety of purposes.
You may prefer the new
List Items Edit Form property, discussed earlier in the chapter, over
writing your own code to pop up a form to collect a new value for the combo box.
Be aware that once the form is opened, the value of
OpenArgs does not change on subsequent execu-
tions of the
OpenForm method. For that reason, you may want to check to see if the form is open before
you execute
OpenForm. The form’s IsLoaded property can tell you if a form is loaded.
IsLoaded()
There are numerous situations in which you might want to know if a form is open, or loaded. The fol-
lowing scenario will help illustrate when and why to use the

IsLoaded property. In this situation, a user
may have several forms open, including
frmEvent, which lists the names of everyone scheduled to par-
ticipate in an event. Say that one of the participants cancels, so the user opens a participant form and
clicks a command button (
cmdCancel) to remove that participant from the event. Because the event
form is already open, the data that it displays cannot be updated without some action being taken. One
approach is to include code behind
cmdCancel on the participant form to see if the event schedule form
is open by using the event
IsLoaded. If the form, frmEvent, is not open, the regular cancellation
process continues. If
frmEvent is open, the appropriate actions — such as closing the event schedule
form or requerying the event form after canceling the appointment — should also be included in the
code behind
cmdCancel. The snippet below is an example of code that could be used in this scenario.
324
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 324
If frmEvent.IsLoaded Then
frmEvent.Requery ‘add additional actions
End If
Consider another example: Perhaps you want users to be able to open a form in a specific way, such as
only from a specified form, and definitely not from the Navigation pane. In that case, you may also first
want to determine if the form is already open. The following example uses the
IsLoaded property to see
if a particular form is open before it continues code execution. In the code,
CurrentProject refers to
the open database and for illustration purposes,
frmEvent is used again:

Private Sub cmdCancel_Click()
If Not CurrentProject.AllForms(“frmEvent”).IsLoaded Then
Msgbox “Cannot cancel while ‘frmEvent’ form is open.”, _
vbInformation + VBOkOnly, “Cancel Invalid”
Exit Sub
End If
End Sub
As you can see, the IsLoaded property is a convenient and useful way to determine if a form is open.
On Timer ()
The On Timer event property can identify a process that can be triggered at timed intervals. It is used in
conjunction with the
TimerInterval property, which stipulates the frequency of the event. When speci-
fying the value for the
TimerInterval property, the time is entered in milliseconds (1/1000 of a sec-
ond), so entering
5000 will trigger the event at 5-second intervals.
You may be wondering when you might use a timer. How about to close an application’s splash screen
(a form that displays for a few seconds as the application opens and then is automatically closed) or to
close a message that you only want to display for a few seconds? For instance, you could display your
company logo and contact number during the few seconds that it takes for an application. Of course,
you don’t want to leave the splash screen open for too long — four seconds seems like a reasonable time.
Let’s assume that the application has an AutoExec macro that runs when the database opens and that
one of the actions includes a macro to open the splash screen.
To set the timer for any form, open the form’s Property Sheet to the Event tab. Scroll down to
Timer
Interval
. The property default is 0; for our example, we would change it to 4000, the equivalent of 4 sec-
onds. Then in the
OnTimer property, select Event Procedure and open the IDE to add the following code.
Private Sub Form_Timer()

DoCmd.Close acForm, Me.Name
End Sub
When the splash screen (our form) opens, the Timer Interval starts counting down. When the timer
reaches the specified interval time, 4 seconds, the code runs. In this event,
DoCmd.Close will close the
splash screen. This scenario has worked well in the past, but it can cause problems if the database is
325
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 325
opened in disabled mode by Access 2007. If the VBA is disabled, the timer event code never runs and the
splash screen is not closed. To avoid risking that situation, you can close a splash screen by using a
macro for the
OnTimer event. (One of the reasons that I’m mentioning this is because you may work
with MDB files that use VBA to close a splash screen. If so, you need to either replace the code or ensure
that the databases are opened fully enabled, from a trusted location and/or are digitally signed.)
To create the macro to close the splash screen, set the
OnTimer property to a macro by clicking the ellip-
sis and selecting the
Macro Builder. In the Action column, select Close; the lower pane displays the
Action Arguments. For the Object Type, select Form; from the Object Name list, select the form that
is used as the splash screen; and for
Save, select No. From the Close group on the menu bar, select Save
As
, on the first line enter a name for the macro, and then click OK.
Another example of using the
OnTimer event is in a multi-user environment where the users are record-
ing meetings and setting scheduled times for the clients of the Sales staff. Obviously, it is crucial to know
when sales people are available and to not book them twice for the same time period. Because several
people have the potential to be booking appointments at the same time, the form that displays and
records the appointments needs to be requeried constantly. For this situation, the form’s

Timer
Interval
is set to 6 seconds (6000) and the form’s Timer event contains the following code:
Private Sub Form_Timer()
Me.Requery
End Sub
That allows the form to update on its own rather than requiring the user to click an update button.
Because you need to get additions and deletions, the form uses
Requery instead of Refresh, which
would retrieve only changes to existing records. As shown in Figure 10-16, the main form consistently
displays the appointment time blocks, and the subform uses a continuous form to show the number of
available openings for each wave for every scheduled date. The timer event runs a query to refresh the
data for the subform. When an appointment is booked, the number for the respective date and wave is
decreased. And, thanks to the
Timer event, the display will be updated for any user that has the sched-
ule form open. And, to see the details for any day, they merely double-click the record selector to open
the appointment schedule to review or change an appointment.
Figure 10-16
326
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 326
Keep in mind that the timer event can create a significant performance hit. And, if users are manually
entering data, there are likely better alternatives — 2 seconds wouldn’t be adequate for data entry.
However, in situations where you are merely displaying information, such as the number of appoint-
ments or openings in a time slot, then a short timer interval can be an effective mechanism to udpate
data in open forms.
Late Binding
Another timing issue that can have significant impact on performance is whether the form is using early
or late binding. Basically, this refers to when the record (or recordset) is created for a form or other
object. Late binding typically involves filtering, which reduces the size of the recordset and also allows

the developer greater control of the record source.
When you add fields to your form, the text boxes that are created become bound text boxes. That is, the
table or query fields are bound to those text boxes and when the form opens, the data from your table or
query binds to the controls on your form to display data. If the form has only a few controls or is a light-
weight form, you might not notice a performance hit when the form loads. But loading all the possible
records behind a complex form that has numerous controls, combo boxes, subforms, and complicated
criteria can create a drastic slowdown.
One option is to load the controls on demand or after receiving criteria to filter the data rather than
when the form opens. The following code snippet uses the
Form_Open event to set the RowSource and
SourceObjects of a combo box, list box, and subform to an empty string (“”). Obviously, if the intent
is for these to be unbound objects, it is better to save the form without record sources for those controls.
But a form may inadvertently have been saved with values stored for the controls, so the following
example demonstrates how to remove the values. Speaking of saving the form, a technique commonly
used with subforms is to save the
SourceObject as an empty string to prevent the subform from
attempting to acquire data as the form opens.
Private Sub Form_Open()
Me.cboMyComboBox.RowSource = “”
‘Or
Me.lstMyListbox.RowSource = “”
‘Or
Me.fsubMySubform.SourceObject = “”
End sub
After the form opens, you will need a way to fill the RowSource. Common techniques are to initiate the
process from a command button or by selecting a value from a combo box or list box.
Another method is to use the Option Group control to load data to other controls. The Option Group
contains controls that include radio buttons, check boxes, and toggle buttons. Each radio button, check
box, or toggle button (depending on the type of Option Box controls you chose) can be assigned a value
that is set whenever that control is selected. That numeric value can be used in a

Select Case state-
ment to populate the list box or subform.
In the next example, you’ll populate the list box with a list of forms and reports in your database by
selecting a numeric value from the Option Group. You can use the lists for several purposes, even as the
basis for a switchboard (menu form), as shown in Figure 10-17. To get started, you need a table to hold
327
Chapter 10: Using VBA to Enhance Forms
47033c10.qxd:WroxProgRef 3/30/07 12:26 AM Page 327