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