• In addition to using the data control to move through database records,
we can write Visual Basic code to accomplish the same, and other, tasks. This
is referred to as
programmatic control. In fact, many times the
data control
Visible property is set to
False
and all data manipulations are performed in code. We can also use programmatic
control to find certain records.
• There are four methods
used for moving in a database. These methods replicate the capabilities of the
four arrow buttons on the data control:
MoveFirst - Move to the first record in the
table.
MoveLast - Move to the last record in the table.
MoveNext - Move to the next record (with respect to the current
record) in the table.
MovePrevious - Move to the previous record (with respect to
the current record) in the table.
• When moving about the database programmatically, we
need to test the BOF (beginning of file) and EOF (end
of file) properties. The BOF property is True when the current record
is positioned before any data. The EOF property is True when the current record
has been positioned past the end of the data. If either property is True, the
current record is invalid. If both properties are True, then there is no data
in the database table at all.
• These properties, and the programmatic control methods,
operate on the Recordset property of the data control. Hence,
to move to the first record in a table attached to a data control named dtaExample,
the syntax is:
dtaExample.Recordset.MoveFirst
• There is a method used for searching a database:
Find - Find a record that meets the specified
search criteria.
This method also operates on the Recordset property and has three arguments
we will be concerned with. To use Find with a data control named dtaExample:
dtaExample.Recordset.Find Criteria,NumberSkipped,SearchDirection
• The search Criteria is a string expression
like a WHERE clause in SQL. We won’t go into much detail
on such criteria here. Simply put, the criteria describes what particular records
it wants to look at. For example, using our book database, if we want to look
at books with titles (the Title field) beginning with S, we would
use:
Criteria = “Title >= ‘S’”
Note the use of single quotes around the search letter. Single
quotes are used to enclose strings in Criteria statements. Three logical operators
can be used: equals (=), greater than (>), and less than (<).
• The NumberSkipped argument tells how
many records to skip before beginning the Find. This can be used to exclude the
current record by setting NumberSkipped to 1.
• The SearchDirection argument has two
possible values: adSearchForward or adSearchBackward.
Note, in conjunction with the four Move methods, the SearchDirection
argument can be used to provide a variety of search types (search from the top,
search from the bottom, etc.)
• If a search fails to find a record that matches the
criteria, the Recordset’s EOF or BOF property
is set to True (depending on search direction). Another property
used in searches is the Bookmark property. This allows you to
save the current record pointer in case you want to return to that position later.
The example illustrates its use.
