Microsoft KB Archive/190515

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

Article ID: 190515

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q190515



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

  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.   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  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. Create a form named MyCustomers based on the Customers table.  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   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: <pre class="fixed_text">   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 </li> Save and close the MyCustomers form, and then reopen the form in Form view.</li> Go to any record other than the first one.</li> Close the form and reopen it. Note that the form opens to the record you were on when you closed the form.</li></ol>

<div class="references_section">