Keeping the Current Record the Same After Using Refresh

See AlsoVMZ7S4

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.

Summary:

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.

More Information:

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

       Data1.Refresh

       'Restore the current record by using the stored field value

       'to find

       Data1.RecordSet.FindFirst "Au_ID = " & CurrRec

       text1.Visible = True

    End Sub

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.