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

• 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/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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:




      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. 

2. Set the following properties for the tblStorage fields. (Accept the default value for all other properties):




      Variable
      --------
      FieldSize = 30

      Value
      -----
      FieldSize = 70

      Description
      -----------
      FieldSize = 255 

3. 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.



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



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




      ' 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 

6. 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:




      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 

7. Save and close the MyCustomers form, and then reopen the form in Form view.



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



9. 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