I assume from your description you actually are speaking of closing a Form, then re-opening it later, to the same Record that was last viewed when the Form was closed.
For information to persist between the opening and closing of a Form, it has to be stored in a Table, and to identify a given Record, you must have a Unique Field defined for Table the Form is based on; normally this Field would be the one designated as the Primary Key for the Table.
- Create a new Table
- Add a single Field
- Name the Field LastRecordViewed
- Set its Datatype to match your Unique Field
- Save the Table and name it UtilityTable
Now use these Subs in the Code Module of the Form in question:
Code:
Private Sub Form_Load()
Dim TargetField As Variant
If DCount("*", "UtilityTable") > 0 Then
TargetField = DLookup("LastRecordViewed", "UtilityTable")
UniqueFieldName.SetFocus
DoCmd.FindRecord TargetField
End If
End Sub
Code:
Private Sub Form_Unload(Cancel As Integer)
If DCount("*", "UtilityTable") > 0 Then
CurrentDb.Execute "UPDATE UtilityTable SET UtilityTable.LastRecordViewed = " & Me.UniqueFieldName, dbFailOnError
Else
CurrentDb.Execute "INSERT INTO UtilityTable (LastRecordViewed) VALUES ('" & Me.UniqueFieldName & "')"
End If
End Sub
UniqueFieldName must be just that, a Field that is unique to a given Record.
When this Form is closed, this unique identifier will be written to the UtilityTable.
When the Form is next opened, it will retrieve this unique identifier from UtilityTable and find the Record on the current Form that contains it.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007