BookMark Bug - HELP

[Binta Patel]

On a a data entry form for "SerialNo", I am checking to
see if the text entered already exists, if it does, the
user is notified and the record is displayed using a
bookmark property (OnExit event) on the Serial number.
The code works fine and displays records for the firt
200 records i.e. chose any serial number from the first
200 record, and anything after 200 i.e. records # 201
gives me error # 3159 - "Run time Error, Not a valid
bookmark". Can someone explain and is there a different
way of displaying the record. Thanks in advance for any
help.

Here's the Code that I am using. As I said this code
seems to work for the first couple records and then does
not work for the records at the end of the table. Error
Occurs in the function GoToBookMarkRecord
(Screen.activeform.bookmark = varbookmark)

'Using this On BeforeUpdate
Option Compare Database
Dim VarBookmark


Public Function FindSerialDup()

Dim Objrs As ADODB.Recordset
Dim objrs1 As ADODB.Recordset
Dim frm As Form_frmGeneralInfo_New
Dim Serial As ADODB.Field
Dim Serial1 As ADODB.Field
Dim txtSerial As TextBox


Set objrs1 = New ADODB.Recordset
objrs1.Open "tblGeneralInfo", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

Set Serial1 = objrs1!SerialNo
Set frm = Forms![frmgeneralinfo_new]
Set txtSerial = frm.SerialNo

If objrs1.RecordCount <> 0 Then

objrs1.MoveFirst

Do While Not objrs1.EOF

If (Serial1 = txtSerial.Value) Then

MsgBox "The serial Number you have entered
already exists in the database", vbOKOnly

If Serial1 = txtSerial Then

VarBookmark = objrs1.Bookmark

End If

If Not IsNull(VarBookmark) Then
DoCmd.CancelEvent
SendKeys "{ESC 2}", False
Exit Function
End If
Else
objrs1.MoveNext
End If

Loop

Else
' MsgBox "There are currently no existing records in
the table", vbOKOnly
'do nothing, do not need to compare because there are
no existing records.
End If


End Function
---------------Using this ONExit------------------
Public Function GoToBookMarkRecord()


If Not IsNull(VarBookmark) And Len(VarBookmark) <> 0
Then
DoCmd.CancelEvent
Screen.ActiveForm.Bookmark = varbookmark
VarBookmark = Null
End If

End Function

 

[Malcolm Cook]

Binta,

first, I would check not(objrs1.RecordCount.EOF) instead of
objrs1.RecordCount <> 0

second, assuming not .EOF, I would use objrs1.find instead of your loop,
after calling MoveFirst

third, I would probably NOT use a global, VarBookmark , to communicate the
result back. Rather, use a function that returns a bookmark. And seperate
you recrodset search logi from your form navigation (the posting of the esc,
which begs other questions, but I wont go there now). But if you continue
to, make sure to clear it before you search the recordset.

fifth, your code as written will probably work if you issue a .movelast
before the .movefirst.

Regards,

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

PS - I meant fourth (he he).

On a a data entry form for "SerialNo", I am checking to
see if the text entered already exists, if it does, the
user is notified and the record is displayed using a
bookmark property (OnExit event) on the Serial number.
The code works fine and displays records for the firt
200 records i.e. chose any serial number from the first
200 record, and anything after 200 i.e. records # 201
gives me error # 3159 - "Run time Error, Not a valid
bookmark". Can someone explain and is there a different
way of displaying the record. Thanks in advance for any
help.

Here's the Code that I am using. As I said this code
seems to work for the first couple records and then does
not work for the records at the end of the table. Error
Occurs in the function GoToBookMarkRecord
(Screen.activeform.bookmark = varbookmark)

'Using this On BeforeUpdate
Option Compare Database
Dim VarBookmark


Public Function FindSerialDup()

Dim Objrs As ADODB.Recordset
Dim objrs1 As ADODB.Recordset
Dim frm As Form_frmGeneralInfo_New
Dim Serial As ADODB.Field
Dim Serial1 As ADODB.Field
Dim txtSerial As TextBox


Set objrs1 = New ADODB.Recordset
objrs1.Open "tblGeneralInfo", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

Set Serial1 = objrs1!SerialNo
Set frm = Forms![frmgeneralinfo_new]
Set txtSerial = frm.SerialNo

If objrs1.RecordCount <> 0 Then

objrs1.MoveFirst

Do While Not objrs1.EOF

If (Serial1 = txtSerial.Value) Then

MsgBox "The serial Number you have entered
already exists in the database", vbOKOnly

If Serial1 = txtSerial Then

VarBookmark = objrs1.Bookmark

End If

If Not IsNull(VarBookmark) Then
DoCmd.CancelEvent
SendKeys "{ESC 2}", False
Exit Function
End If
Else
objrs1.MoveNext
End If

Loop

Else
' MsgBox "There are currently no existing records in
the table", vbOKOnly
'do nothing, do not need to compare because there are
no existing records.
End If


End Function
---------------Using this ONExit------------------
Public Function GoToBookMarkRecord()


If Not IsNull(VarBookmark) And Len(VarBookmark) <> 0
Then
DoCmd.CancelEvent
Screen.ActiveForm.Bookmark = varbookmark
VarBookmark = Null
End If

End Function

 

[Dirk Goldgar]

On a a data entry form for "SerialNo", I am checking to
see if the text entered already exists, if it does, the
user is notified and the record is displayed using a
bookmark property (OnExit event) on the Serial number.
The code works fine and displays records for the firt
200 records i.e. chose any serial number from the first
200 record, and anything after 200 i.e. records # 201
gives me error # 3159 - "Run time Error, Not a valid
bookmark". Can someone explain and is there a different
way of displaying the record. Thanks in advance for any
help.

Did you try the code I suggested in my reply to one of your earlier
messages?

 

[BP]

There was no code there, I just got the message about
multiposting...so if u would be kind enough to send the
code I will give it a shot.

 

[Dirk Goldgar]

There was no code there, I just got the message about
multiposting...so if u would be kind enough to send the
code I will give it a shot.

You looked in the wrong place, which is one of the common results of
multiposting. Here's the text of the message I posted yesterday at
2:11PM EDT:

<quote>
If that code works at all, there must be something you left out of the
post. There's no definition for "Found", which you are referring to in
GoToBookMarkRecord. And if Found is a function of some sort that
returns the varBookmark, that shouldn't work because the form's
recordset isn't a clone of the recordset from which varBookmark was
taken, and so their bookmarks shouldn't be interchangeable.

I take your intention is to check, in the BeforeUpdate event of
txtSerial on form frmGeneralInfo_New to see if the user's entry already
exists, and if it does, to take him to that record. Is that correct?
Is this code in an MDB or an ADP? If it's in an MDB, the following
would be more efficient:

'----- start of code -----
Private Sub SerialNo_BeforeUpdate(Cancel As Integer)

With Me.RecordsetClone

.FindFirst "SerialNo='" & Me.SerialNo & "'"
' Note: the above line assumes SerialNo is a text field.

If Not .NoMatch Then

MsgBox _
"The serial Number you have entered " & _
" already exists in the database", _
vbOKOnly

Cancel = True
Me.SerialNo.Undo
Me.Undo

Me.Bookmark = .Bookmark

End If

End With

End Sub
'----- end of code -----

It would be done very similarly in an ADP, but I'm more familiar with
the DAO methods.

</quote>

 

[Binta Patel]

Thank you very much for your help, the code worked.