rs.bookmark problem

[cpberg1]

Hey all,
Attempting to sort out a bug in a code event that was set up by a predecessor. Very new to VBA but willing to play around! Access 2003

I have a text box that sorts the student list dynamically as the person is typing.

The first time a student is clicked from the sorted user list it pulls up the wrong record although always the same one. On the second click of the name within the list it pulls up the correct record. Any further usage of the sorting textbox or student list works properly until the form is reloaded.

In an attempt to sort out the issue I've added the Set RS = nothing lines at the end of the events. It didn't solve the issue so I started after the bookmarking lines. I haven't used the bookmark property before so I'm shooting in the dark on that one. If I comment out the bookmarking lines the form works properly however I'm not sure if I'll run into other issues removing the lines. Can bookmark values be carried across to other forms?
Any suggestions for best way to trace down the places that bookmarked STDID is being used?

Thanks for the help!

Private Sub EnterNameTBx_Change()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
       
    
    Dim viewValue As Boolean

        viewValue = Me.ViewAllCheck
        Me.EnterNameTBx.SetFocus

        If viewValue = True Then
                
                strSQL = "SELECT STD21Basic.STDID, [std21lastName] & ', ' & [STD21FirstName] AS STDNAME, STD21Basic.STD21MasterDisplay " & _
                    "FROM STD21Basic " & _
                    "WHERE ((([STD21LastName] & ', ' & [STD21FirstName]) Like '" & Me!EnterNameTBx.Text & "*')) " & _
                    "ORDER BY [std21lastName] & ', ' & [STD21FirstName];"
        
            Else
                strSQL = "SELECT STD21Basic.STDID, [std21lastName] & ', ' & [STD21FirstName] AS STDNAME, STD21Basic.STD21MasterDisplay " & _
                    "FROM STD21Basic " & _
                    "WHERE ((([STD21LastName] & ', ' & [STD21FirstName]) Like '" & Me!EnterNameTBx.Text & "*') And ((STD21Basic.STD21MasterDisplay) = Yes)) " & _
                    "ORDER BY [std21lastName] & ', ' & [STD21FirstName];"
        
        End If
        
            Me.StdList.RowSource = strSQL
                 
            strSQL = "SELECT STDID, STD21LastName FROM STD21Basic " _
                   & "WHERE (((STD21LastName) LIKE '" & Me!EnterNameTBx.Text & "*') AND ((STD21Basic.STD21MasterDisplay)=Yes))" _
                   & "ORDER BY STD21Basic.STD21LastName, STD21Basic.STD21FirstName;"
       
    Set rs = db.OpenRecordset(strSQL)

    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Me!StdList = rs!STDID
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    Me.EnterNameTBx.SetFocus
    If Len(Me.EnterNameTBx) > 0 Then
       Me.EnterNameTBx.SelStart = Len(Me.EnterNameTBx)
    End If
    
    End Sub

Private Sub StdList_Enter()
    ' Find the record that matches the control.
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[STDID] = " & str(Nz(Me![StdList], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Set rs = Nothing
End Sub

Private Sub StdList_AfterUpdate()
     'Find the record that matches the control
    
    Me.RecordSource = "SELECT STD21Basic.* FROM STD21Basic " _
                   & "WHERE (((STD21Basic.STDID) = " & Me.StdList & "));" ' AND ((STD21Basic.STD21MasterDisplay)=Yes));"
    setImagePath
    CurrSTDID = Me.StdList
    
    Forms![StudentMaster_Form]![STD51TCO Subform].Form.TCOUpdate

    
    
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[STDID] = " & str(Nz(Me![StdList], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
     setImagePath
    Set rs = Nothing
End Sub

Private Sub Refresh_Click()
On Error GoTo Err_Refresh_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Refresh_Click:
    Exit Sub

Err_Refresh_Click:
    MsgBox Err.Description
    Resume Exit_Refresh_Click
    
End Sub

 

[Jeanette Cunningham]

When you use FindFirst, you need to use the NoMatch test with it. That will fix up the error with bookmarks.

The NoMatch test tells access to skip the bookmark bit if there aren't any records that fit the criteria.

Replace
If Not rs.EOF
with
If Not rs.NoMatch