I have a form with a combo box named cboSearch.  The combobox has 
CustomerID as PK and CustonerName as the second cloumn.  CustomerID is the 
bound field and hidden.

I had the procedure below to look up customer and it was working fine 
until now I am getting an errror saying not a valid bookmark.  I have the 
same procedure on another form which is working fine but I cannot identify 
why it is not working here

Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT CustomerID FROM tblCustomer;", cnn, adOpenKeyset
    
    rst.Find "CustomerID=" & cboSearch
    Me.Bookmark = rst.Bookmark
    cboSearch = Null
    

Hi Howard,

I suspect you have several problems here. First, you can?t compare two
bookmarks in two different recordsets, which is what Me.Bookmark 
rst.Bookmark is doing.  The first recorset is the recordset of the
bound form (I assume its bound); the second is created by the SQL
statement. A bookmark is a 4-byte-Long integer value between 0 and
4294967295 that Access assigns to each record in a recordset. These
values won?t be synchronized across different recordsets. I?d have to
guess, though, that they can overlap at certain points across different
recordsets ? arbitrarily - which is why your code ran at all. To work
with Me.Bookmark in the way you are attempting you would first need to
clone (copy) the form?s underlying recordset:

Set rst = Me.RecordsetClone
?------
Me.Bookmark = rst.Bookmark

Here the recordset referenced by rst is a copy (bookmark values and
all) of the recordset underlying your form (referenced by Me)
which is why the assignment Me.Bookmark = rst.Bookmark can be made.

Second, I?m not sure this kind of thing can be done with bound forms
using ADO. The recordsets of bound forms in .mdb files are always DAO
recordsets. So Set rst = Me.RecordsetClone always returns a DAO
recordset in an .mdb file. (though RecordsetClone returns an ADO
recodset in an .adp file). DAO recordsets can?t be assigned to ADO
recordset object variables, obviously.

The following will work (I?m guessing on the event procedure):

Private Sub cboSearch_AfterUpdate()
    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone

    rst.FindFirst "CustomerID=" & cboSearch
    Me.Bookmark = rst.Bookmark
     cboSearch = Null
End Sub

But since Access (.mdb) knows that the return value of RecordsetClone
is always a DAO recordset, the above can be simplified to:

Sub cboSearch_AfterUpdate()
    Me.RecordsetClone.FindFirst "CustomerID=" & cboSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark
    cboSearch = Null
End Sub

So my best guess is that at some point in the life of your code,
rst.Bookmark contains a value that is not a valid Me.Bookmark value at
the line Me.Bookmark = rst.Bookmark. And if that?s right, I?d expect
your second form to crash eventually, too.  In fact, the real question
then becomes why did your code run at all. Also, I think this may be
one of those places in Access where you may have to use DAO if you are
using a bound form. You can assign bookmark values to variant
variables in ADO, but I don't know if you can use the ADO find method
with the bookmark property in mdb. files.

Best,

Bob







>From: "Howard Stone" <ququmber@h...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] search db
>Date: Sun, 22 Dec 2002 23:59:48
>
>I have a form with a combo box named cboSearch.  The combobox has
>CustomerID as PK and CustonerName as the second cloumn.  CustomerID is the
>bound field and hidden.
>
>I had the procedure below to look up customer and it was working fine
>until now I am getting an errror saying not a valid bookmark.  I have the
>same procedure on another form which is working fine but I cannot identify
>why it is not working here
>
>Dim rst As ADODB.Recordset
>     Dim cnn As ADODB.Connection
>
>     Set cnn = CurrentProject.Connection
>     Set rst = New ADODB.Recordset
>     rst.Open "SELECT CustomerID FROM tblCustomer;", cnn, adOpenKeyset
>
>     rst.Find "CustomerID=" & cboSearch
>     Me.Bookmark = rst.Bookmark
>     cboSearch = Null
>


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963