Make Form open to the record it was on when it was last closed

[Bdowns]

I have a navigation form with tabs for different data entry forms. I am trying to make it so that when you switch from one form to another and then back to the original form it opens to the record you were last on rather than re-setting to the first record. The form that is most commonly used (original form in the mentioned scenario) is a form with a subform. It has a one-to-many relationship. As it is, it is too easy to switch forms, come back, and then start logging data on the subform with the wrong id from the main form. Any ideas?

[June7]

By 'switching' do you mean opening and closing and opening? Otherwise, I don't understand how just changing focus between forms will cause 're-setting'. Are you doing a requery of forms?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

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.

1. Create a new Table
2. Add a single Field
3. Name the Field LastRecordViewed
4. Set its Datatype to match your Unique Field
5. Save the Table and name it UtilityTable
   

Now use these Subs in the Code Module of the Form in question:

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

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