1. We expand the book database application to allow searching for certain author
names. We’ll use a ‘rolodex’ approach where, by pressing a particular
letter button, books with author last names corresponding to that button appear
on the form.
2. We want a row of buttons starting at ‘A’ and ending at ‘Z’
to appear on the lower part of our form. Drawing each one individually would be
a big pain, so we’ll let Visual Basic do all the work in the Form_Load procedure.
What we’ll do is create one command button (the ‘A’), make it
a control array, and then dynamically create 25 new control array elements at
run-time, filling each with a different letter. We’ll even let the code
decide on proper spacing.
So, add one command button to the previous form. Name it cmdLetter and give
it a Caption of A. Set its Index property to 0 to make it a control array element.
On my form, things at this point look like this:
3. Attach this code to the Form_Load procedure. This code sets up the rolodex
control array and draws the additional 25 letter buttons on the form. (Sorry,
you have to type some code now!)
Private Sub Form_Load()
Dim I As Integer
‘Size buttons
cmdLetter(0).Width = (frmBooks.ScaleWidth - 2* cmdLetter(0).Left) / 26
For I = 1 To 25
Load cmdLetter(I) ' Create new control array element
'Position new letter next to previous one
cmdLetter(I).Left = cmdLetter(I - 1).Left + cmdLetter(0).Width
'Set caption and make visible
cmdLetter(I).Caption = Chr(vbKeyA + I)
cmdLetter(I).Visible = True
Next I
End Sub
At this point, even though all the code is not in place, you could run your
application to check how the letter buttons look. My finished form (at run-time)
looks like this:
Notice how Visual Basic adjusted the button widths to fit nicely on the form.
4. Attach this code to the cmdLetter_Click procedure. In this procedure, we
use a search criteria that finds the first occurrence of an author name that begins
with the selected letter command button. If the search fails, the record displayed
prior to the search is retained (using the Bookmark property).
Private Sub cmdLetter_Click(Index As
Integer)
Dim BookMark1 As Variant 'Mark your place in case no match is found
BookMark1 = dtaTitles.Recordset.Bookmark 'Move to top of table to start search
dtaTitles.Recordset.MoveFirst
dtaTitles.Recordset.Find "Author >= '" + cmdLetter(Index).Caption
+ "'", 0, adSearchForward
If dtaTitles.Recordset.EOF = True Then
dtaTitles.Recordset.Bookmark = BookMark1
End If
txtAuthor.SetFocus
End Sub
Let’s look at the search a little closer. We move to the top of the database
using MoveFirst. Then, the Find is executed (notice the selected letter is surrounded
by single quotes). If EOF is True after the Find, it means we didn’t find
a match to the Criteria and Bookmark is returned to its saved value.
5. Save your application. Test its operation. Note once the program finds the
first occurrence of an author name beginning with the selected letter (or next
highest letter if there is no author with the pressed letter), you can use the
data control navigation buttons (namely the right arrow button) to find other
author names beginning with that letter.
