Question on Going to a Record

[John Lane]

I guess I should know this, but.....

I have a continuous subfrom. The user toggles a button, triggering a
programatic change (like setting a flag) on a row on the subform's underlying
table. I want to requery the subformform (I know how to do that) then
reposition the subform so that the changed row is visible. I've looked at
gotorecord but it doesn't seems to know about subforms. Any help would be
appreciated. Thanks.

 

[Marshall Barton]

I have a continuous subfrom. The user toggles a button, triggering a
programatic change (like setting a flag) on a row on the subform's underlying
table. I want to requery the subformform (I know how to do that) then
reposition the subform so that the changed row is visible. I've looked at
gotorecord but it doesn't seems to know about subforms.

Try something like:

With Me.subformcontrol.Form
lngTemp = .pkfield 'assuming pkfield is a number
.Requery
Set rs = .RecordsetClone
rs.FindFirst "pkfield = " & lngTemp
If Not rs.NoMatch Then .Bookmark = rs.Bookmark
Set rs = Nothing
End With

 

[John Lane]

Here's how I did it (admittedly copying from some other entries!):


Forms!frmAccountRegister!subfrmAcctReg.Requery

With Forms!frmAccountRegister!subfrmAcctReg.Form.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[Record ID] = " & LocalRecId
If Not .NoMatch Then
For x = 1 To 9

Forms!frmAccountRegister!subfrmAcctReg.Form.RecordsetClone.MovePrevious
Next
Forms!frmAccountRegister!subfrmAcctReg.Form.Bookmark =
..Bookmark
End If
End If
End With

The x = 1 to 9 thing is to get the record postioned at the bottom of the
subform window.

 

[Marshall Barton]

Here's how I did it (admittedly copying from some other entries!):

Forms!frmAccountRegister!subfrmAcctReg.Requery

With Forms!frmAccountRegister!subfrmAcctReg.Form.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[Record ID] = " & LocalRecId
If Not .NoMatch Then
For x = 1 To 9

Forms!frmAccountRegister!subfrmAcctReg.Form.RecordsetClone.MovePrevious
Next
Forms!frmAccountRegister!subfrmAcctReg.Form.Bookmark =
.Bookmark
End If
End If
End With

The x = 1 to 9 thing is to get the record postioned at the bottom of the
subform window.

You do not need to do MovePrevious 9 times, use .Move -9
instead. After moving backwards, you should move foward
again to make the located record current again. Also, if
the record located by Find first is somewhere in the first 8
records, you will get an error. Instead of the For loop, I
think you need to use something more like:

X = 9
If .RecordCount < 10 Then X = .RecordCount - 1
..Move -x
..Move x