VB3 Keeping the Current Record the Same After Using Refresh

- Standard and Professional Editions of Microsoft Visual Basic for

  Windows, version 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.