Invalid Bookmarks after 190 records

[trito]

Hi all,

I'm at my wit's end tring to figure this problem out and I'm hoping someone here can help me out. I created my own next, last, prev, and first button on a form that controls the recordset of a datasheet on a subform. Reason I did this is that I wanted the users to be able to move to the next record no matter which column or order the users sort by (using the sort icon). First and Last works perfectly everytime but Next and Prev has "Invalid Bookmark" errors when I select a record greater than 190. I read there was a similar bug on AC97 but I have AC2002 which the bug has been fixed. I have included my code for your review.

Thanks in advance,

Tri

Private Sub cmdNextRec_Click()
On Error GoTo Err_cmdNextRec_Click

Dim frm As Form 'Reference to PAT_REVIEW
Dim rst As New ADODB.Recordset

Set frm = Forms!MainForm!SubForm.Form

Set rst = frm.RecordsetClone

rst.Sort = Mid(frm.OrderBy, InStr(1, frm.OrderBy, ".") + 1, Len(frm.OrderBy))

rst.Bookmark = frm.Bookmark
rst.MoveNext
If rst.EOF = False Then
frm.Bookmark = rst.Bookmark
End If

rst.Close

Set frm = Nothing
Set rst = Nothing

Exit_cmdNextRec_Click:
Exit Sub

Err_cmdNextRec_Click:
MsgBox Err.Description
Resume Exit_cmdNextRec_Click

End Sub

Private Sub cmdPrevRec_Click()
On Error GoTo Err_cmdPrevRec_Click
Dim frm As Form 'Reference to PAT_REVIEW
Dim rst As New ADODB.Recordset
Set frm = Forms!MainForm!SubForm.Form
Set rst = frm.RecordsetClone
rst.Sort = Mid(frm.OrderBy, InStr(1, frm.OrderBy, ".") + 1, Len(frm.OrderBy))
rst.Bookmark = frm.Bookmark
rst.MovePrevious
If rst.BOF = False Then
frm.Bookmark = rst.Bookmark
End If


rst.Close

Set frm = Nothing
Set rst = Nothing

Exit_cmdPrevRec_Click:
Exit Sub

Err_cmdPrevRec_Click:
MsgBox Err.Description
Resume Exit_cmdPrevRec_Click

End Sub
Private Sub cmdLastRec_Click()
On Error GoTo Err_cmdLastRec_Click
Dim frm As Form 'Reference to PAT_REVIEW
Dim rst As New ADODB.Recordset
Set frm = Forms!MainForm!SubForm.Form
Set rst = frm.RecordsetClone
rst.Sort = Mid(frm.OrderBy, InStr(1, frm.OrderBy, ".") + 1, Len(frm.OrderBy))
rst.MoveLast
frm.Bookmark = rst.Bookmark

rst.Close

Set frm = Nothing
Set rst = Nothing

Exit_cmdLastRec_Click:
Exit Sub

Err_cmdLastRec_Click:
MsgBox Err.Description
Resume Exit_cmdLastRec_Click

End Sub

Private Sub cmdFirstRec_Click()
On Error GoTo Err_cmdFirstRec_Click
Dim frm As Form 'Reference to PAT_REVIEW
Dim rst As New ADODB.Recordset
Set frm = Forms!MainForm!SubForm.Form
Set rst = frm.RecordsetClone
rst.Sort = Mid(frm.OrderBy, InStr(1, frm.OrderBy, ".") + 1, Len(frm.OrderBy))
rst.MoveFirst
frm.Bookmark = rst.Bookmark

rst.Close

Set frm = Nothing
Set rst = Nothing

Exit_cmdFirstRec_Click:
Exit Sub

Err_cmdFirstRec_Click:
MsgBox Err.Description
Resume Exit_cmdFirstRec_Click

End Sub

 

[trito]

Actually I was wrong, it start going down hill at 200 records.

 

[PHV]

Are you playing with an adp (instead of a mdb) ?
The reason I ask is I wonder you may clone a form's recordset to an ADODB.Recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[trito]

Yes I am using adp. With more playing around I think the problem is with the Sort function. After I sort the recordset to match the orderby value of the form, the bookmark for any records after 200 is invalid. I tried replacing:

rst.Bookmark = frm.Bookmark

with

rst.find "ID = " & Forms!MainForm!SubForm!ID

but it still spits out "Invalid Bookmark" @ 201 records.

 

[RoyVidar]

I think ADP's behave somewhat different than mdb's. I have no experience with ADP's, you may get better responses in the ADP forum (forum958). I think ADP's only load a certain amount of records when you open a form, but I don't know at which time it loads the rest, perhaps try using a docmd.gotorecord,,aclast in the form open/load? Then I think there's also a max records property somewhere on the form (data tab of the properties?), check that.

If the recordset supports recordcount, you might try to debug.print rst.recordcount to check how many records are within the recordset.

Roy-Vidar

 

[trito]

OK problem solved...sort of. I cheated and went a different direction. I used the docmd.gotorecord instead. Reason why I didn't try this before was because I didn't think it would go to the next record after I resorted the form but surprising it did. Selecting a field really doesn't do anything to how the routine moves the currentrecord pointer other than giving it something to initialize. Here's the code I used for future reference. Thanks to everybody who gave suggestions.

Cheers,

Tri

Private Sub cmdNextRec_Click()
On Error GoTo Err_cmdNextRec_Click


DoCmd.GoToControl "PATM_subform_Review"
DoCmd.GoToControl "Name"

DoCmd.GoToRecord , , acNext

Call TabCtl0_Change
Exit_cmdNextRec_Click:
Exit Sub

Err_cmdNextRec_Click:
MsgBox Err.Description
Resume Exit_cmdNextRec_Click

End Sub
Private Sub cmdPrevRec_Click()
On Error GoTo Err_cmdPrevRec_Click

DoCmd.GoToControl "PATM_subform_Review"
DoCmd.GoToControl "Name"

DoCmd.GoToRecord , , acPrev

Call TabCtl0_Change

Exit_cmdPrevRec_Click:
Exit Sub

Err_cmdPrevRec_Click:
MsgBox Err.Description
Resume Exit_cmdPrevRec_Click

End Sub

 

[trito]

Oh and for more reference, I figured how to get my first set of code to work if you don't want to lose focus on another control when changing record. Like RoyVidar mentioned, ADO recordsets have a default local chase value of 200. Fortunately this is a property that you can change with the CaseSize method. Here's the sample code:

Private Sub cmdNextRec_Click()
On Error GoTo Err_cmdNextRec_Click
Dim frm As Form
Dim rst As New ADODB.Recordset

Set frm = forms!MainForm!Subform.Form
Set rst = frm.RecordsetClone

rst.CacheSize = 2000 'change cache size to max possible records
rst.Sort = Mid(frm.OrderBy, InStr(1, frm.OrderBy, ".") + 1, Len(frm.OrderBy))

rst.Bookmark = frm.Bookmark
If rst.EOF = False Then rst.MoveNext

frm.Bookmark = rst.Bookmark


Call TabCtl0_Change

Exit_cmdNextRec_Click:
Exit Sub

Err_cmdNextRec_Click:
MsgBox Err.Description
'MsgBox "You are on the last record"
Resume Exit_cmdNextRec_Click

End Sub