ReQuery but stay on selected record - Please Help!

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

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
 
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,

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


Wayne Morgan said:
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


Gina Whipp said:
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
Click to expand...
Click to expand...
 
Back
Top