Full Version: Return To Current Record +1 After Requery?
UtterAccess Forums > Microsoft® Access > Access Forms
Garindan
Hi all, I have a form which contains a subform as continuous forms. A button on the subform does an action and requeries the subform, so obviously it jumps back to the top of the recordset. I know this is a common problem and I have tried to use the bookmark method to get around it as follows
!--c1-->
CODE
Private Sub btnRemoveOrder_Click()
    If MsgBox("This will remove the Order from form without invoicing. Continue?", vbOKCancel, "Warning") = vbOK Then
        Me![Invoiced] = -1
        
        ' Requery the subform
        Dim strBookmark As String
        strBookmark = Me.Bookmark
        Me.Requery
        Me.Bookmark = strBookmark
    End If
    
End Sub

The trouble is... in this case the button marks a checkbox on the record as 'Yes', updates and rqueries the subform/query which removes the record from the subform (as intended), but I think this stops the bookmark from working? Because upon requery the bookmarked record is no longer there. I get 'error: not a valid bookmark'.
Strangely, after the first error it then works, although not as well as I would like as it moves the next record (after the removed one) to the top of the subform. The subform displays 6 records at once so if you remove the fifth one down, after the requery the sixth record will now be at the top and the others which were on screen are off screen. This is a bit confusing!
Any idea of a better way I can do this? Many thanks in advance!!
Jeff B.
"the next record" has no inherent meaning in Access. Access tables store "buckets o' data", not ordered lists like Excel.
YOU have to tell Access which order to sort/show the records, so YOU will have to tell Access which one is "next".
Garindan
Ok but I still don't know how to do it <":confused:" border="0" alt="confused.gif" />
Jeff B.
I don't understand what you're trying to accomplish ... or why.
dmhzx
A couple of thoughts:
It seems to me that what you want to do is perfectly reasonable, but extremely difficult!
Having deleted a record in the subform, you want the same records displayed as before, but without the deleted one. Whatever was at the top must still be at the top, unless that was the deleted one
1) After the user says "yes", and you' ve set invoiced to -1, how about something like moving to the next record, then collecting the bookmark, then doing the requery, and going to the bookmark
It seems to me the Access must at this point know what the 'next' record is, otherwise it wouldn't have a command that says 'movenext'.
2) I Don't know off hand how to get the subform to have a record selected that isn't at the top after the bookmark find. Unless after the requery you keep 'movenext' ing until you hit the right bookmark??
3) Or moving to the next record as suggested in 1), storing the uniqe key value and doind a Findfirst with it after the re-query.
Not sure if iths helps you, but as I said earlier, no matter what view of the data you have at a given time, Access is perfectly well aware of what the next record is. It knows when it's on the first one, and it knwos when its on the last one (MoveFisrt and MoveLast).
Garindan
dmhzx. larry - both hit the nail on the head with what I want to do, and thanks for your help!
arry, yes I originally thought something like the record number +1 should work! I tested your code and it works without error, however it actually goes to the record after the 'one after the deleted one', i.e. +2! I guess using this method the original record number of the 'deleted record' simply becomes the record number of the next record (before deletion) so it goes to the one after that.
So I just tried
CODE
If MsgBox("This will remove the Order from form without invoicing. Continue?", vbOKCancel, "Warning") = vbOK Then
        Me![Invoiced] = -1
        
        ' Requery the subform
        Dim CrId As Integer
        CrId = Me.CurrentRecord
        Me.Requery
        DoCmd.GoToRecord , , acGoTo, CrId
    End If

instead and it works fine! No error now with this method <
However, I still loose track of where I was because the record which is CrID jumps to the top! It's better than it was but I'm sure there must be many times when you'd want the records to stay in the same position?
Thanks a lot!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.