ACC: How to Open a Form to the Last Viewed Record

ID: Q190515


The information in this article applies to:
  • Microsoft Access versions 7.0, 97


SUMMARY

This article shows you how to open a form and have it automatically load the most recently viewed record.


MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
The following example has three key parts. First, you create a hidden table that saves the record's key value between sessions. Second, you write a procedure for the form's Unload event to save the ID of the current record. Third, you write a procedure for the form's Load event to find that record again.

  1. Open the sample database Northwind.mdb, and create a table named tblStorage to save the CustomerID value between sessions. Include the following fields and values:


  2. 
          Field Name    Data Type    Description
          ----------    ---------    -----------
          Variable        Text       The name of the variable.
                                     Make it a primary key.
          Value           Text       Holds the value to be returned.
          Description     Text       What this variable is for. 
  3. Set the following properties for the tblStorage fields. (Accept the default value for all other properties):


  4. 
          Variable
          --------
          FieldSize = 30
    
          Value
          -----
          FieldSize = 70
    
          Description
          -----------
          FieldSize = 255 
  5. If you want to hide the table from users, after saving and closing the table, view its properties (right-click the table name, and then click Properties) and click to select the Hidden attribute.


  6. Create a form named MyCustomers based on the Customers table.


  7. Set the new form's UnLoad property to the following event procedure:


  8. 
          ' The code below finds or creates a record in tblStorage where the
          ' field Variable contains "CustomerIDLast", and stores the current
          ' CustomerID in the field called Value.
    
    
          Private Sub Form_Unload(Cancel As Integer)
              ' Declare database object and recordset object.
              Dim db As Database, rst As Recordset
    
              ' If the current record has no customer ID, do nothing.
              If IsNull(Me![CustomerID]) Then Exit Sub
    
              Set db = CurrentDb
              Set rst = db.OpenRecordset("tblStorage")
              rst.Index = "PrimaryKey"
              rst.Seek "=", "CustomerIDLast"
    
              ' If not found, create the entry.
              If rst.NoMatch Then
                 rst.AddNew
                 rst![Variable] = "CustomerIDLast"
                 rst![Value] = Me![CustomerID]
                 rst![Description] = "ID of last edited customer record," _
                                     & Me.Name & "."
    
                 rst.Update ' Update the recordset.
    
              Else          ' Else save the customer ID of the current record.
    
                 rst.Edit
                 rst![Value] = Me![CustomerID]
                 rst.Update  ' Update the recordset.
              End If
    
              rst.Close      ' Close the recordset.
          End Sub 
  9. To find the record again, you need to use the form's Load event. The code in the Load event should perform the following steps:
    
        a. Locate the record in tblStorage where the Variable field contains
           the "CustomerIDLast" string. If it is not there, do nothing.
    
        b. If located, get the last stored CustomerID from the Value field.
    
        c. Create a RecordsetClone of the records in the form.
    
        d. Find the matching CustomerID in the clone set.
    
        e. Move to the record by setting the form's BookMark. 
    To set this up, set the form's Load property to the following event procedure:


  10. 
          Private Sub Form_Load()
    
               ' Declare database object and recordset objects.
               Dim db As Database, rst As Recordset, rstFrm As Recordset
    
               ' Set the database object to the current database.
               Set db = CurrentDb
    
               ' Open the table tblStorage.
               Set rst = db.OpenRecordset("tblStorage")
    
               ' Set the index for the seek.
               rst.Index = "PrimaryKey"
    
               ' Look for the CustomerIDLast record.
               rst.Seek "=", "CustomerIDLast"
    
               ' If a match, get the customer ID and take the form to that
               ' record. If no match, do nothing.
               If Not rst.NoMatch Then
                   If Not IsNull(rst![Value]) Then
    
                   ' Create clone of the form's record set.
                   Set rstFrm = Me.RecordsetClone
                   ' Find the matching record.
    
                   rstFrm.FindFirst "[CustomerID] = '" & rst![Value] & "'"
    
                   If Not rstFrm.NoMatch Then
                       ' Have the form go to that matching record.
                       Me.Bookmark = rstFrm.Bookmark
                   End If
    
                       rstFrm.Close ' Close the recordset rstFrm.
                   End If
               End If
               rst.Close ' Close the recordset rst.
          End Sub 
  11. Save and close the MyCustomers form, and then reopen the form in Form view.


  12. Go to any record other than the first one.


  13. Close the form and reopen it. Note that the form opens to the record you were on when you closed the form.



REFERENCES

For more information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: remember store global return

Keywords : kbdta AccCon FmsEvnt FmsHowto KbVBA
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: July 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.