Open form & subforms at last viewed record

[sonny123]

I have a parent form with a sub form
so to refresh the form I am doing this

Private Sub Case_But_Click()

DoCmd.Close acForm, "Caseworker", acSaveNo
' do stuff here updating record set
DoCmd.OpenForm "Caseworker", acNormal

End Sub

fine so far except that when caseworker reopens i get the first form(or the last if i choose)
But i want reopen the record i just closed

i found this & followed it
http://www.access-programmers.co.uk/forums/showthread.php?t=150210&referrerid=90628

and here is what I have

Private Sub Form_Unload(Cancel As Integer)
LastRecordUsed = Me.ID
Debug.Print " Unload " & Me.ID
End Sub

and

Private Sub Form_Open(Cancel As Integer)
Debug.Print "open " & intLastRecordUsed
If intLastRecordUsed = 0 Then
   DoCmd.Maximize
Else
   Me.Filter = "[ID]=" & LastRecordUsed
   Me.FilterOn = True
   DoCmd.Maximize
End If

End Sub

the problem is that i reopen the form i just closed but i can no longer navigate to the other parent form records, presumably because the filter is ON.

i tried setting filter to False but that didnt work (obviously i suppose but you never know till you try!)

so how do i get round this
reopen the form i just closed without setting the filter to true



EDIT:
ooh yes that registry trick in the refered to thread looks clever
any pointers / links on how i would do that ????

 

[pbaldy]

You could adapt this method:

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

 

[pbaldy]

Oh, and for the registry bit look at SaveSetting and GetSetting.

 

[sonny123]

I sort of get the idea (i think)
do it all in the click event i think is what your saying
(clean out the unload and open events)

heres what ive coded to my click event
but its still going to the first "caseworker" record

Private Sub Case_Ref_No_Label_Click()
intLastRecordUsed = Me.ID
DoCmd.Close acForm, "Caseworker", acSaveNo
'do stuff here updating record set
DoCmd.OpenForm "Caseworker"

Dim rs As Object
Set rs = Forms!Caseworker.RecordsetClone
rs.FindFirst "ID = " & intLastRecordUsed
Forms!Caseworker.rs.intLastRecordUsed
Set rs = Nothing

End Sub

it does run but it stll opens the first record not the one i closed

 

[pbaldy]

Double check this line compared to my example:

Forms!Caseworker.rs.intLastRecordUsed
Forms!frmEmployeesDetail.Bookmark = rs.Bookmark

I'd actually expect yours to error.

 

[sonny123]

Yay!!

I think that the var lngBookmark being kinda similar to the word bookmark kind of threw me off course (not to mention a few beers that had been sunk last night )

okay now i have it working

Dim rs As Object
Set rs = Forms!Caseworker.RecordsetClone
rs.FindFirst "ID = " & intLastRecordUsed
[B]Forms!Caseworker.Bookmark = rs.Bookmark[/B]
Set rs = Nothing

thanks Paul

now to check out that registry

 

[pbaldy]

Happy to help.

Beer + Access =