Question on Going to a Record

  • Thread starter Thread starter John Lane
  • Start date Start date
J

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.
 
John said:
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.
Click to expand...


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
 
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.
 
John said:
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.
Click to expand...


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
 
Back
Top