Keeping the Current Record the Same After Using Refresh
This article is reprinted from the Microsoft Knowledge Base. To view the article, maximize your help window. This information applies to Visual Basic for Windows, versions 2.0 and 3.0.
In Visual Basic version 3.0 for Windows, when the Refresh method
updates the recordset for a data control, it recreates the
recordset and resets the current record. This invalidates all
existing bookmarks for that recordset. This behavior is by
design. It is not a Visual Basic bug but rather a design feature
of the data control.
However, this behavior may be undesirable if you want to
refresh the recordset and maintain the current record. This
article explains how to restore the current record after
executing the Refresh method.
Although there is no simple way to retain the current record after
executing the Refresh method, you can restore the current record.
To do so, store unique field data for the current record. Then
use the stored field data to execute the Refresh method followed
by the FindFirst method. The FindFirst method uses the stored
field data to restore the current record.
The following steps demonstrate how to restore the current record
after executing the Refresh method:
1. Start Visual Basic, or from the File menu, choose New Project
(ALT, F, N) if Visual Basic is already running. Form1 is
created by default.
2. Put a data control (Data1) on Form1.
3. Set the DatabaseName property for Data1 to <path name>BIBLIO.MDB
where <path name> represents the full path to the Visual Basic
BIBLIO.MDB sample database.
4. Set the RecordSource property of Data1 to Authors, which is the
name of the table in the BIBLIO.MDB database.
5. Put a Text box (Text1) on Form1
6. Set the DataSource property of Text1 to Data1
7. Set the DataField property of Text1 to Author, which is the name
of the field (column) in the Authors table.
8. Put a command button (Command1) on Form1
9. Change the Caption property of Command1 to Refresh.
10. Add the following code to the Command1_Click event
Sub Command1_Click ()
Dim CurrRec As Variant
'Hide the text box and emulate it by drawing a border
text1.Visible = False
Line (text1.Left, text1.Top)-(text1.Left + text1.Width,
text1.Top + text1.Height), , B
'Store the value of a unique field for the current record
CurrRec = Data1.RecordSet!Au_ID
'Update the RecordSet
'Restore the current record by using the stored field value
Data1.RecordSet.FindFirst "Au_ID = " & CurrRec
text1.Visible = True
11. From the Run menu, choose Start (ALT, R, S) or press the F5 key
to run the program.
12. Using the data control, move to the next record. You should see
"Atre, Shaku" displayed in the text box
13. Using the data control, move further into the file. To do this,
click the right arrow or click the rightmost button -- the one
with the arrow and bar -- to move to the end of the file.
14. Click the Refresh button. The name of the first author in the
recordset is displayed in Text1 for an instant. Then the
current author is redisplayed.