Opening form on record left off

[ScottBeatty]

I have a form with a 1,000 or so records and I'd like to be able to start where I left off when I save and open up. Is there an access property or VBA code that allows me to start on, for example, 250, instead of returning to 1 every time?

I know how to go to the last record in the database, so I guess my question is there a way to go to a record that I was working on last because my last record I am working on is not necessarily the last record in the database.

-Scott

 

[pbaldy]

You'd have to save the key value of the record you were working on, probably in a local table. You could then adapt this code to use that saved value instead of getting it before the requery:

http://www.baldyweb.com/Requery.htm

note there's a goof in the "if your key field is text" version I have to fix.

 

[billmeye]

Scott, you could save the primary key value of the current record in another table by placing the code in both the On Current (for other than Me.Newrecord) and in the After Insert form event (to capture new records).

Docmd.RunSQL "UPDATE tblSaveKey SET tblSaveKey.SavedKey =" & Me!PrimaryKeyID & ";"

Then, in the On Open code place something like this:

With Me.Recordset
     FindFirst "[PrimaryKeyID]=" & DFirst("[SavedKey]","tblSaveKey")
     If .NoMatch Then Exit Sub
End With

The tblSaveKey will only have 1 record and you will just overwrite it every time.