close a form using vba from a command button...

[brucey54]

Hi folks, is it possible to close a form using vba from a command button, then reopen the form at the same record that you closed from?

 

[moke123]

yes, but why?

 

[missinglinq]

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