As you're most likely aware, by issuing an SQL command, you can make your program accomplish many database tasks. As you probably also know, your program must accomplish some of its tasks by reading record-for-record through your databases.
Reading all records in an Access database can be relatively simple. All your program needs to do is select an index, use the MoveFirst, FindFirst, or Seek method to locate a beginning record, then read the data from that record. To read all the records, your program uses the MoveNext or FindNext method to move forward in the database; it then checks the EOF property to determine when it has reached the end of the database.
Unfortunately, if during the loop that reads all of a database's records you need to look up a value in the same table, your program has a problem. Specifically, the record-scanning loop works only because it uses the MoveNext or FindNext method to move to the next record after the current record in the database. If your program upsets the current record by looking for other data, the loop most likely will not read accurately through all the records in your database.
Fortunately, Microsoft has added the BookMark property to Table, Dynaset, and Snapshot objects. By using the BookMark property, your program can note the current record position in your database and return to that position later.
In this article, we'll look at the BookMark property and show you how to use it to save the current record position. We'll also present an example program that uses the BookMark property to help look up data while scanning through a database, without losing the current record position.
Let's take a look at some very simple Visual Basic code that reads through all the records in a database. Listing A contains VB sample record-scanning code.
Listing A: Record-scanning code
Sub Form_DblClick ()
' clear the form
form1.Cls
' open the database
Dim dbBiblio As Database
Set dbBiblio = OpenDatabase("C:\VB\BIBLIO.MDB", True, ÂFalse)
' open the table
Dim tbAuthor As Table
Set tbAuthor = dbBiblio.OpenTable("Authors")
' start at the top
tbAuthor.MoveFirst
' until end of file...
Do While Not tbAuthor.EOF
' print record information
form1.Print tbAuthor!Author
' go to the next record
tbAuthor.MoveNext
Loop
' close table
tbAuthor.Close
' close database
dbBiblio.Close
End Sub
As you can see, the code in Listing A opens the BIBLIO.MDB database (which comes with the VB sample code) and then opens its Author table. Once the code opens the database and the Author table, it reads through all the records in the database, displaying the current author's name. To move to the next record, the code uses the MoveNext method of the Author table. To determine when it has processed all the records, the code checks the Author table's EOF property. Finally, the code closes the Author table and then closes the BIBLIO.MDB database. Figure A shows the output from the program in Listing A.
Figure A This program loops through the data in a database.
Incidentally, if you want to check out this code for yourself, it should work as we've printed it, even on your machine. Just add the code from Listing A to a new project's Form1's _DblClick event. Once you've done so, double-click on the form, and the program will display the authors' names. Of course, you must have the Professional Edition of Visual Basic, and you'll need to specify the appropriate path for the BIBLIO.MDB database in the line that calls the OpenDatabase function.
As we've said, the code in Listing A will work properly, assuming of course that the BIBLIO.MDB table resides in the C:\VB directory (as we've specified in the OpenDatabase function call). However, the code won't work as well if the current record position changes before the code calls the MoveNext method. Let's take another look at the same code, but this time with the addition of one simple function call. Listing B contains our updated record-reading code, which no longer works as you'd expect.
Obviously, the code in Listing B will not display all the author names from the Authors table in the BIBLIO.MDB database. As you can see, the second statement in the Do loop moves to the last record in the database. Because the current record is then the last record in the database, when the program executes the MoveNext method, it reaches the end of the file and displays no more authors' names. Figure B shows the output from our errant program in Listing B, which displays only the author information from the first record in the database.
Listing B: Errant record-reading code
Sub Form_DblClick ()
' clear the form
form1.Cls
' open the database
Dim dbBiblio As Database
Set dbBiblio = OpenDatabase("C:\VB\BIBLIO.MDB", True, ÂFalse)
' open the table
Dim tbAuthor As Table
Set tbAuthor = dbBiblio.OpenTable("Authors")
' start at the top
tbAuthor.MoveFirst
' until end of file...
Do While Not tbAuthor.EOF
' print record information
form1.Print tbAuthor!Author
' go somewhere else in the database
' to look up other information
tbAuthor.MoveLast
' go to the next record
tbAuthor.MoveNext
Loop
' close table
tbAuthor.Close
' close database
dbBiblio.Close
End Sub
Figure B The program no longer works because the current record positioning has been changed.
In more complex Do loop code sections, your programs may call functions that upset the current record. In those cases, your programs will also fail, and you may believe you're forced to recode small or even very large portions of your program.
Fortunately, the Table, Dynaset, and Snapshot objects supply the BookMark property. By using the BookMark property, you can let your programs change the current record positioning in any way they need to and then return to the current record for subsequent processing.
To use the BookMark property, you first declare a Variant (or non-fixed-length String) variable. To save the current record position, assign your Variant variable to the current value of the BookMark property of the Table, Dynaset, or Snapshot object you're reading through. For example, the lines
' the bookmark Dim bmCurrentAuthor As Variant
' save where we are bmCurrentAuthor = tbAuthor.BookMark
declare the bmCurrentAuthor variable of Variant data type and then store in it the current BookMark property value of the tbAuthor table.
To return to a particular record, simply assign the Table, Dynaset, or Snapshot's BookMark property to the value you saved earlier. For example, the lines
' return to the previous current record tbAuthor.BookMark = bmCurrentAuthor
reset the current record position in the Author table to the one saved in the bmCurrentAuthor variable.
Remember, the BookMark property is not a record itself. The Table, Dynaset, and Snapshot objects use the BookMark property only to establish current record positioning. Don't attempt to use BookMark values with the MoveFirst or FindFirst methods—they won't work.
By using the BookMark property, we can repair our errant code in Listing B. In fact, all we need to do is save the current record position at the top of the Do loop, then restore the current position just before calling the MoveNext method to move to the next record. Listing C contains the corrected source code, which still moves to the last record in the database in the middle of the Do loop but uses the BookMark property to establish and reset current record positioning.
Listing C: Corrected record-reading code
Sub Form_DblClick ()
' clear the form
form1.Cls
' open the database
Dim dbBiblio As Database
Set dbBiblio = OpenDatabase("C:\VB\BIBLIO.MDB", True, ÂFalse)
' open the table
Dim tbAuthor As Table
Set tbAuthor = dbBiblio.OpenTable("Authors")
' start at the top
tbAuthor.MoveFirst
' define the bookmark
Dim bmCurrentAuthor As Variant
' until end of file...
Do While Not tbAuthor.EOF
' save where we are
bmCurrentAuthor = tbAuthor.BookMark
' print record information
form1.Print tbAuthor!Author
' go somewhere else in the database
' to look up other information
tbAuthor.MoveLast
' return to the previous current record
tbAuthor.BookMark = bmCurrentAuthor
' go to the next record
tbAuthor.MoveNext
Loop
' close table
tbAuthor.Close
' close database
dbBiblio.Close
End Sub
Looking closely at the code in Listing C, you'll notice that we still move to the end of the database (by issuing the MoveLast method) right in the middle of the record-scanning Do loop. Because we save the current record position in the bmCurrentAuthor variable and then restore that position using the BookMark property, the program produces the proper output. Figure C shows the final output of the program, which you can see looks just like that from our original error-free code.
Figure C Even though the program changes the current record positioning during the record-scanning Do loop, the program still displays the proper output, thanks to the BookMark property.
Of course, your program probably won't just jump to the last record in the database right in the middle of a record-scanning Do loop. What usually happens is that your program calls a function during a Do loop, and that function moves the current record position. When that happens, your program would be quite difficult to repair without the BookMark property. You'd have to save enough actual information about the current record so that you could return to it by using the FindFirst-FindNext or MoveFirst-MoveNext methods.
Fortunately, you can simply store the current positioning information provided by the BookMark property in a Variant variable you declare yourself. To return to that position in the Table, Dynaset, or Snapshot object, simply set your object's BookMark property to the value you saved previously in the Variant variable.
Also, you'll probably want to add some error-checking code to your functions that use the BookMark property to establish and reset current record positioning, especially on multiuser databases. You'll want to do this because your program will generate a run-time error if it tries to return to a record that another user deleted.
The Table, Dynaset, and Snapshot objects provide the BookMark property, which allows you to store and reset the current record position. Because of the BookMark property, your programs are free to change the current record position and return to it later, allowing you to write much more complex database lookup programs.
This article is reproduced from the June 1995 issue of Inside Visual Basic for Windows. Copyright © 1993, The Cobb Group. All rights are reserved. Inside Visual Basic for Windows is an independently produced publication of The Cobb Group. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of The Cobb Group.
To contact The Cobb Group, please call (800) 223-8720 or (502) 491-1900.