Access form record persistance

[Jagstrom]

How can I have my form open to the last viewed record?

 

[Remou]

You can save various bits and pieces such as this to a small system table. Dlookup the table when you open the form.

 

[Jagstrom]

just a dump and retieval of the record number is all I need, I just have no idea of how to code

 

[Remou]

Use the on close event of the form and an update query to update your small table. Use the load event of the form to dlookup your small table. Use the recordset of the form to find the record id you have just dlookedup. Post back your sketched code when you run into a problem.

 

[Jagstrom]

This is a small db with only 1 user


Built table "Static"
with field "LastRecord"


This opens to correct record:

Private Sub Form_Load()
Dim x As Single
x = DLookup("LastRecord", "Static")
DoCmd.GoToRecord acDataForm, "Leads", acGoTo, x
End Sub

Now, how to dump current record # into "LastRecord" field of "Static" table on form unload event?

 

[Remou]

You can use something like:

strSQL="Update Static Set LastRecord=" & Me.ID
CurrentDB.Execute strSQL,dbFailOnError

I recommend that you use an ID, rather than a record number because the record number will change if you filter or sort, but the ID will not. This means that you should use the recordset and find in the load event, rather than GoToRecord. For example:

[tt]Me.Recordset.FindFirst "ID=" & x[/tt]

 

[Jagstrom]

using the PK would be better but unless its spelled out I'll never get it to work.

 

[Remou]

Ok. What is your primary key field name and what is the data type (text, numeric etc)?

 

[Jagstrom]

LeadID" as long pk autonumber
in "Leads" Table

"Static" table has "LastRecord" field as long for last viewed marker

 

[Remou]

Something like:

Private Sub Form_Load()
' Make sure you have a reference to 
' Microsoft DAO x.x Object Library

Dim rs As DAO.Recordset
Dim lngLeadID As Long

lngLeadID = DLookup("LastRecord", "Static")
Set rs=Me.RecordsetClone

Me.Recordset.FindFirst "LeadID=" & lngLeadID
If rs.NoMatch Then
   MsgBox "Ooops"
Else
   Me.Bookmark=rs.Bookmark
End If
End Sub

Private Form_Unload(Cancel As Integer)
strSQL="Update Static Set LastRecord=" & Me.LeadID
CurrentDB.Execute strSQL,dbFailOnError
End Sub

I hope I have that right, I do not have a copy of Access available just now.

 

[Jagstrom]

Added reference and code.

Seems to unload properly by recording ID in Static table

On the load side somethings not working, Its still shows 1st record

 

[Remou]

Oops. Change this line:


Me.Recordset.FindFirst "LeadID=" & lngLeadID

To

rs.FindFirst "LeadID=" & lngLeadID

 

[Jagstrom]

That rocks,

Thanks a bunch