|
Combo Boxes – 104: The Code behind the Magic
I recall a time from my childhood when my buddies
and I were playing with a golf ball; rolling it around,
bouncing it, playing catch – basic kid stuff. After a
while we started arguing about what made the ball
so hard and why it bounced so high. After a heated
debate (“debate” may be a misleading term for what
went on within this gang – arguing and name calling
may be more accurate), we decided to end the
dispute by opening the ball to see what was inside.
We got a sharp knife and started cutting away. It
wasn't an easy job. When we first split the cover and
looked inside, we found tightly wound rubber bands.
Being curious kids, observing the rubber band layer
wasn't enough, so we hacked and hacked at the
dimpled cover until it was almost off. One of the kids
grabbed two opposing sides of the cover and pulled
and pulled, until all of a sudden the rubber bands
popped out at us. We all jumped a foot in the air!
Although surprised, we were not deterred. We
decided to see what was under all those rubber
bands. We started unraveling and tearing off layer
after layer until finally we got to the center: a small
rubbery ball. Our curiosity still not satisfied, we cut
the ball open and liquid oozed out.
Although our approach wasn't particularly scientific,
we did learn a fair amount about the
construction of a golf ball.
Last month we used a Wizard to create a combo box
that finds records on a form. The advantages of
using a Wizard to create this combo box are many;
but one disadvantage is that the Wizard creates the
code behind the scenes. Only the user who makes an
extra effort to examine this code will understand
what the code is doing and how the combo box
works. Like kids opening a golf ball to learn more
about it, we'll learn more as we explore this Wizard-
created
code to understand the code behind the
combo box.
| The Preliminaries |
 |
|
Last month we created a combo box on the form
below:
In this example, the user selects a camper from the
list of all possible values using the drop down list in
the combo box, and the form is populated with the
record corresponding to the user's choice. You can
get the details on how we got this far in the November
issue.
This is really nifty stuff, but how does it all happen?
Let’s closely examine the code generated by the
Wizard to discover the magic.
|
| The Code that makes things Happen |
|
|
To examine the code that runs when the combo box
is clicked:
- Open the form in design view
- Right click the combo box
- Choose properties
- Click on the Event tab
You'll be presented with the following:
The information under the Event tab shows an
exhaustive list of the various events that a combo
box can respond to. For example, based on the list
above, a combo box could respond on exit, on click,
or on key press. In our specific case, we're
interested in the after update event. When we used
the Wizard to help us build this combo box, it created
a small program that runs after the user makes a
selection from the combo box. That selection is the
update event, and the after update event is when
the form is populated with the record of the specific
camper selected. To see the actual code that runs
after update, click on the three dots ( . . . , the
ellipses for all of you English majors out there) in the
far right column.
Clicking on the ellipses will take you to a code
window containing programs that run when various
things happen in your application. The content of
this code window has been the subject of many a
chapter in many a book, so I won't delve into the
complete content of this code window in detail. Our
task today, is to deconstruct the code that makes
the combo box work.
|
| The Nitty-Gritty |
|
|
Ok after you've clicked on the ellipsis you'll see
something that looks like this:
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CamperKey] = " & Me![Combo1]
If Not rs.EOF Then Me.Bookmark =
rs.Bookmark
End Sub
The combo box Wizard created this code and it runs
after the user makes a selection (or update). We'll
look at this line-by-line to truly understand what's
going on.
1. Private Sub Combo_1AfterUpdate()
This line makes some declarations about the lines
that follow. The text Sub in the first line of the code
declares that the code between this line and the End
Sub line is a subroutine, or a series of instructions
that run and finish. The alternative is a function,
which runs a series of commands and then returns a
value to the calling routine. That's beyond what
we're discussing here so I'll leave that for another
day.
The word Private indicates that this routine can be
called only from this particular form. The alternative
is Public, which indicates the routine can be called
from any place within the application. For a form,
you'll almost always want to leave the default as
Private.
The phrase Combo_1 AfterUpdate indicates that this
subroutine will run after the user makes a selection
from the combo box called "Combo_1".
2. ' Find the record that matches the
control.
Notice that this line starts with an apostrophe. The
apostrophe indicates a comment, or a line in the
subroutine that serves as a note to the developer or
user about what the routine is doing. This line is not
perceived as code that will run in the subroutine.
When you get to the point of writing your own code,
I encourage you to comment it liberally. It will really
help you understand what's going on when it comes
time to debug.
We're getting more obscure here, but it's not too
difficult. Dim is short for dimension and it indicates to
Access that it will be using a variable (something
that can change or vary); rs is the name of the
variable. The "as object" is a special type of variable-
once again variable types are something I'll cover at
some time in the future
These first three lines have “set the scene”, so to
speak. The next line is where things start to happen.
4. Set rs = me.Recordset.Clone
The Set rs indicates that the variable declared in line
3 is being set to something – and that something is
me.recordset.clone. Let’s dissect this by looking at
the 3 specific components between the periods.
The me specifies the form that the code is
associated with, in our case the camper detail form.
The recordset indicates the set of records (think of
them as rows in a query or table). The word clone is
the only truly tricky part of this line. Clone in this
case means a copy of the underlying recordset
(group of records in the form.) The use of the clone
is how you manipulate a group of records in Visual
basic, the programming language on which Access is
based. This is a rather complex subject that we'll also
leave for a future discussion.
5. rs.FindFirst "[CamperKey] = " & Me!
[Combo1]
This line is a bit tricky as well. The rs.FindFirst
indicates that we’re examining the recordset clone
we just dumped into our rs variable to find the first
record that meets our criteria. This is the set of
instructions we’ll to use to find the record we're
interested in.
Our criteria, surrounded by double quotes, is the field
CamperKey (the Wizard adds the square brackets,
which are optional in this case). We’re looking for the
camper key that's equal to Me![Combo1]. Let's pull
this apart. The Me again refers to the form. The
exclamation point indicates that on the form it should
find whatever follows the exclamation point, in our
case Combo1, the name of our combo box.
So to put that line into English, we’re looking for the
first record in our copy of the records (or clone) on
our form that equals what the user selected in the
combo box. The combo box uses the CamperKey
which is
what we'll use to find our record. You'll hear even
more about why we use keys rather than something
like last name as we go forward.
6. If Not rs.EOF Then Me.Bookmark =
rs.Bookmark
This is the last line of true code. If you look back at
line 5, we are looking for the first record that
matches the value selected by the user in the combo
box. This line basically says if you don’t get to the
end of the clone recordset without finding the record
that matches the camper the user selected in the
combo box, put a marker (Bookmark) to indicate the
matching record in our copy (clone) of the recordset.
That record should also be marked in the camper
dataset. The form then uses this bookmark to jump
to
the selected record.
This line lets Access know that this subroutine is
finished.
So, that's what happens if a matching record is
found; but what happens if no matching record is
found? The IF statement in line 6 says do this only if
the condition is true. If it's not true, then nothing
happens and your user will continue to see whatever
was on the form before he made the selection..
|
| Conclusion |
|
|
Although at times the code behind an event or an
object like a combo box can seem mysterious, it’s an
interesting exercise to take a look at what the
Wizard generates behind the scenes. As kids, we
made good progress understanding the construction
of a golf ball by tearing it apart. As with that case,
you may be surprised how much you learn if you take
the time to dig into the code. By looking at and
understanding existing code you've taken a major
step to writing your own!
|
| Trap of the Month - Override the Spell checker |
|
Periodically, a database field will contain a certain
combination of valid keystrokes similar enough to an
English word to cause the Office spelling tool to leap
to your rescue and ”fix” your mistake. This
phenomenon is notorious with acronyms. For
example, one of my clients has a certain service they
provide call IHS. When I type this string into a text
box in Access, it’s invariable “fixed” to read HIS. This
is the built in spell-checker at work, and in 99% of
the cases, you actually want it to make corrections
for you (especially if you’re as poor a typist as I am).
But sometimes, as described above, you want the
spelling tool to leave the string alone. To stop the
spelling tool from fixing mistakes in a textbox:
- Open the form in design view
- Open the properties window of the textbox (View
| Properties while in design view)
- Choose the Tab labeled Other and
- Change Allow Auto correct to False
From that point on, the spelling tool will not make
any automatic spelling corrections in the selected
text box.
|
|
Tip of the Month – Rename your Combo Box columns |
|
|
|
If you use a meaningful naming convention for your
tables and fields (a practice I heartily recommend) you’ll likely want to show your users the field
names (e.g. the name of a column) in plain English
(as opposed to a name which may not make sense to
them) when you build combo boxes. This gives your
database more polish and it's easy to do!
The first step is to show the column heads in your
combo boxes. You do this by opening the combo box
in design view, clicking on the format tab, then
changing the column heads property to yes. This
tells Access that you want each column to show the
title of the field you're displaying. At this point if you
do nothing else your users will see your field names.
The second step is to turn your native field names
into English. While still in the combo box design view,
choose the properties sheet and click on data tab.
Under the row source property, click on the ellipsis
(. . . ) to the right. This brings up the query by
example view that you normally see in a query. For
each column head, click in the Field row of the grid
and precede the field name with what you want your
user to see, followed by a colon – just leave the rest
of what was there to right of the colon.
Now when you or your user clicks on the combo box,
they will see an understandable column name---a
name you’ve selected--- at the top of the column.
|
|
