Sub form navigation after refresh

[damFlard]

After a refresh of the sub form, I want to go back to the last record edited. This might be a record not fitting on the top of the screen, let's say record no. 2000.
After the refresh the sub form shows the first records, and you need to scroll down.
How to solve this?

 

[damFlard]

Well, I'm running a SQL DELETE statement in VBA, which deletes one record from the sub form. If you don't use a requery, the record will still be visible in that form, though it doesn't exists anymore.

Anyway, I tried the recordsetclone, but I get an error running this line:
Set rst = Forms!frmSearch_Dir.[Subformulier tblDir_split].RecordsetClone

Error 438
Object doesn't support this property or method

No idea what I'm doing wrong...

 

[damFlard]

This might be a good tip, but how do I use it when my button is on form level, and the record to be deleted is on a sub form?
If I execute this it will delete a record on form level, not on sub form level.
It would really help me, if this would work, the scrolling down after running the query isn't very user friendly...

 

[damFlard]

I can understand your opinion about this. Do it simple. I agree on using the basic Access functionality, the best way to avoid complex work-arounds.
But I've created an action menu, with several different kind of functions, on the right of the sub form. If the delete button was on the sub form, it should be part of the form header, making the lay out less logical for users. Some buttons on top, some on the right, might be confusing.

 

[MrG]

A way to repositon on subform

I had the same problem an I also prefer the control on the main form. I found a way to reposition to the last record by doing the following:

In the command button code give focus to subform - Me.subObjectives.SetFocus

subObjectives is my subform name

In the subform have a sub for whichever field gets focus mine is a field called PlanNo

Private Sub PlanNo_GotFocus()
DoCmd.GoToRecord acActiveDataObject, , acLast
End Sub

This may help - I wanted the last field but you could use other variables to reposition elsewhere

Hope this helps