Bookmark FindFirst broken after 450 records

  • Thread starter Thread starter Robin S.
  • Start date Start date
R

Robin S.

In Access 2003.

I have a form, and a subsiquent pop-up form. Here's the code on the
parent form (called directly from a command button click event):

Private Sub SizeWeightForm()
'Open the size/weight form
DoCmd.OpenForm "frmSizeWeight"
'Move to current record
Forms.frmSizeWeight.RecordsetClone.FindFirst "[PRODUCT_ID] = '" &
Me.PRODUCT_ID & "'"
Forms.frmSizeWeight.Bookmark = Me.RecordsetClone.Bookmark
End Sub

This code has worked for just over 450 records. Now it won't run for
any subsiquent records, although it continues to work for the original
450. PRODUCT_ID is indexed and unique.

I get:

Run-time error '3159'
Not a valid bookmark

Any thoughts as to why this code would stop working after a certain
number of records? Nothing seems to have changed and I haven't updated
Office recently.

The .mdb file is just under 100MB in size.

Any suggestions are much appriciated.

Regards,

Robin
 
Robin,

Try it like:

Private Sub SizeWeightForm()

Dim frm as form
Dim rs as dao.recordset

'Open the size/weight form
DoCmd.OpenForm "frmSizeWeight"
Set frm = Forms("frmSizeWeight")

'Move to current record
Set rs = frm.recordsetclone

rs..FindFirst "[PRODUCT_ID] = '" & Me.PRODUCT_ID & "'"
if rs.nomatch then
msgbox 'No match for [Product_ID] = '" & NZ(me.Product_ID, "Null") &
"'"
Else
frm.Bookmark = rs.Bookmark
endif

rs.close
set rs = nothing

End Sub

HTH
Dale
 
Robin,

Try it like:

Private Sub SizeWeightForm()

Dim frm as form
Dim rs as dao.recordset

'Open the size/weight form
DoCmd.OpenForm "frmSizeWeight"
Set frm = Forms("frmSizeWeight")

'Move to current record
Set rs = frm.recordsetclone

rs..FindFirst "[PRODUCT_ID] = '" & Me.PRODUCT_ID & "'"
if rs.nomatch then
msgbox 'No match for [Product_ID] = '" & NZ(me.Product_ID, "Null") &
"'"
Else
frm.Bookmark = rs.Bookmark
endif

rs.close
set rs = nothing

End Sub

HTH
Dale
Click to expand...

Dale,

That worked perfectly. Any idea as to what broke my code? It seems
that my way wasn't as robust as yours, but was there a specific issue
that I should watch for next time?

Thanks very much for your help.

Regards,

Robin
 
Robin,

You didn't test for rs.NoMatch. If the Find does not find a matching
record, and you try to set the bookmarks, it will generate an error.
Whenever I use rs.FindFirst to find a record, I always test for

If rs.nomatch then
msgbox "some message here "
' which usually involves going to the subroutines exit procedure.unless
that is the next step after the EndIf.
else
'some action here
endif

Glad to help

Dale


Robin S. said:
Robin,

Try it like:

Private Sub SizeWeightForm()

Dim frm as form
Dim rs as dao.recordset

'Open the size/weight form
DoCmd.OpenForm "frmSizeWeight"
Set frm = Forms("frmSizeWeight")

'Move to current record
Set rs = frm.recordsetclone

rs..FindFirst "[PRODUCT_ID] = '" & Me.PRODUCT_ID & "'"
if rs.nomatch then
msgbox 'No match for [Product_ID] = '" & NZ(me.Product_ID,
"Null") &
"'"
Else
frm.Bookmark = rs.Bookmark
endif

rs.close
set rs = nothing

End Sub

HTH
Dale
Click to expand...

Dale,

That worked perfectly. Any idea as to what broke my code? It seems
that my way wasn't as robust as yours, but was there a specific issue
that I should watch for next time?

Thanks very much for your help.

Regards,

Robin
Click to expand...
 
Back
Top