Open table on current record

[emcf]

I have a form that displays summary information about a record - what can I do to open the relevant table at the same record to save the user opening the table and then scrolling down to the correct record? I've tried using a macro with GoToRecord but I can't seem to make it work.

 

[ColinEssex]

Hi

I guess you are wanting the form to open at the last used record in the table. Here's how to do it

To make a form re-open at the last used record

Make a table called 'TableSys'
add 3 fields called
Varialbe - Text
Value - Text
Description - Text

On load of the form add:-

Private Sub Form_Load()
Dim db As Database, rst As Recordset, rstFrm As Recordset

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("TblSys")

rst.Index = "primaryKey"
rst.Seek "=", "PatIDLast"

If Not rst.NoMatch Then
If Not IsNull(rst![Value]) Then
Set rstFrm = Me.recordsetclone
rstFrm.FindFirst "[ID_No] = " & rst![Value]

If Not rstFrm.NoMatch Then
Me.Bookmark = rstFrm.Bookmark
End If
rstFrm.Close
End If


Then on Unload of the form add:-

Private Sub Form_Unload(Cancel As Integer)
Dim db As Database, rst As Recordset

If IsNull(Me![ID_No]) Then Exit Sub

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("TblSys")

rst.Index = "primaryKey"
rst.Seek "=", "PatIDLast"

If rst.NoMatch Then
rst.AddNew
rst![variable] = "PatIDLast"
rst![Value] = Me![ID_No]
rst![Description] = "Last PatID val,"
rst.Update
Else
rst.Edit
rst![Value] = Me![ID_No]
rst.Update
End If
rst.Close


End Sub

Where it says [ID_No] - thats the primary key number in your table.

Hope this helps

Col