Access Bookmark problem

[chobo321321]

Hi, I am having some weird problems when using bookmarks. All I want to do is display 1 record at a time in a listbox each time a user clicks a button.

At the moment I save the bookmark value to a global variable, and when the user makes another click is should move to that record. Right now it gives me a "Not a valid Bookmark" error when I click on the button for the second time. First time is alright, but second time it comes up with that error. Any help is appreciated.

Here's an example of my code

Option Compare Database
Dim varbookmark As Variant

Private Sub cmdadd_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set cnn = CurrentProject.Connection
strSQL = "SELECT * " & _
         "FROM tblHotel"

rst.Open strSQL, cnn, adOpenStatic

With lstHotel
    .ColumnCount = 4
    .ColumnWidths = "1in;"
End With

Do While rst.EOF <> True
    If varbookmark = "" Then
        lstHotel.AddItem rst!Name
        rst.MoveNext
        varbookmark = rst.Bookmark
        Exit Sub
    Else
        rst.Bookmark = varbookmark
        lstHotel.AddItem rst!Name
        rst.MoveNext
        varbookmark = rst.Bookmark
        Exit Sub
    End If
Loop

End Sub

 

[chobo321321]

To simplfy things the following line causes the error.

" rst.Bookmark = varbookmark"

When I debug it the rst.bookmark contains the first record position in the rescordset while the global variable contains the saved record position. When I try to assign the new position to rst.bookmark it gives me a "not a valid bookmark" error. Logically the code makes sense, itmust be some stupid little detail. There must be someone who has some info on this.

** This problem only occurs when I exit the sub procedure, and then click on the button again (which re-enters the procedure). Is there a way to store a bookmark globally, and use it in a procedure next time? Becuause everytime I try I get a "not a valid bookmark" error. I'm starting suspect it's because a new recordset is being created on each "on click" event.