ReQuery but stay on selected record - Please Help!

[Gina Whipp]

Hi All,

I tried searching the newsgroups but so far everything I have tried isn't
working for what I want. I have a form with a subform. After checking a
box on the subform...

Private Sub chkShipped_Click()
On Error Resume Next

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL "UPDATE tblLifts SET tblLifts.lBillOfLadingID =
[Forms]![frmBillOfLading]![cboBillOfLadingID], tblLifts.lStatusID = 1 WHERE
(((tblLifts.lLiftID)=[Forms]![frmBillOfLading]![sfrOpenCoilRuns].[Form]![txtLiftID]));"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings True
Me.Requery
Form_sfrBoLDetail.Requery

End Sub

....the subform returns to the first record. I would like it to either go to
the record (1st choice) or at least stay at the last record. I don't want
it to go to the first record and/or the top record. Is there a way to make
the screen stay in the same place that it has been
updated at?


Thanks in advance for any help you can give!
Gina Whipp

 

[Wayne Morgan]

You need to save the value of the unique ID field for the record you want to
move to after the requery in a variable. You can then use that value to move
to the desired record after the requery.

Example:
Dim lngSaveID As Long
lngSaveID = Me.txtUniqueID
'Your other stuff here
Me.Requery
Me.Recordset.FindFirst "[UniqueIDField]=" & lngSaveID

 

[Gina Whipp]

Wayne,

THANK YOU!!! I now see why my code wasn't working, I wasn't using a unique
ID when setting my value!

Thanks,
Gina Whipp

You need to save the value of the unique ID field for the record you want
to move to after the requery in a variable. You can then use that value to
move to the desired record after the requery.

Example:
Dim lngSaveID As Long
lngSaveID = Me.txtUniqueID
'Your other stuff here
Me.Requery
Me.Recordset.FindFirst "[UniqueIDField]=" & lngSaveID

--
Wayne Morgan
MS Access MVP

Hi All,

I tried searching the newsgroups but so far everything I have tried isn't
working for what I want. I have a form with a subform. After checking a
box on the subform...

Private Sub chkShipped_Click()
On Error Resume Next

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL "UPDATE tblLifts SET tblLifts.lBillOfLadingID =
[Forms]![frmBillOfLading]![cboBillOfLadingID], tblLifts.lStatusID = 1
WHERE
(((tblLifts.lLiftID)=[Forms]![frmBillOfLading]![sfrOpenCoilRuns].[Form]![txtLiftID]));"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings True
Me.Requery
Form_sfrBoLDetail.Requery

End Sub

...the subform returns to the first record. I would like it to either go
to the record (1st choice) or at least stay at the last record. I don't
want it to go to the first record and/or the top record. Is there a way
to make the screen stay in the same place that it has been
updated at?


Thanks in advance for any help you can give!
Gina Whipp