ACC: How to Open a Form to the Last Viewed Record (190515)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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:

         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.