Using rs.Seek to Find a Record?

[gt123]

Hi there,

I'd like to add a find record option to my application, and also have a combo box that I would like to load a record relating to the combo box selection. To this end, can anyone explain how I use rs.Seek to find a record within a recordset?

Thank you, GT

[Dnereb]

Example from MS help file abour .seek:
it uses the example database Northwind.

This example demonstrates the Seek method by allowing the user to search for a product based on an ID number.

Sub SeekX()

    Dim dbsNorthwind As Database
    Dim rstProducts As Recordset
    Dim intFirst As Integer
    Dim intLast As Integer
    Dim strMessage As String
    Dim strSeek As String
    Dim varBookmark As Variant

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' You must open a table-type Recordset to use an index, 
    ' and hence the Seek method.
    Set rstProducts = _
        dbsNorthwind.OpenRecordset("Products", dbOpenTable)

    With rstProducts
        ' Set the index.
        .Index = "PrimaryKey"

        ' Get the lowest and highest product IDs.
        .MoveLast
        intLast = !ProductID
        .MoveFirst
        intFirst = !ProductID

        Do While True
            ' Display current record information and ask user 
            ' for ID number.
            strMessage = "Product ID: " & !ProductID & vbCr & _
                "Name: " & !ProductName & vbCr & vbCr & _
                "Enter a product ID between " & intFirst & _
                " and " & intLast & "."
            strSeek = InputBox(strMessage)

            If strSeek = "" Then Exit Do

            ' Store current bookmark in case the Seek fails.
            varBookmark = .Bookmark

            .Seek "=", Val(strSeek)

            ' Return to the current record if the Seek fails.
            If .NoMatch Then
                MsgBox "ID not found!"
                .Bookmark = varBookmark
            End If
        Loop

        .Close
    End With

    dbsNorthwind.Close

End Sub

[gt123]

Thanks for posting, but I don't really know where to begin with that as my application appears quite different.

My DB connection is made using the following code:

'Sets a new DB connection
        Set cn = New ADODB.Connection
                cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & App.Path & "\vc.mdb"
        cn.Open
        
'Sets a new DB recordset
        Set rs = New ADODB.Recordset
            rs.Open "Files", cn, adOpenKeyset, adLockPessimistic, adCmdTable

My database fields are filled using the following code:

Private Sub FillFields()
    
    'This fills the text boxes with data from the recordset
        If Not (rs.BOF = True Or rs.EOF = True) Then
            txtCode.Text = rs.Fields("Code")
            txtLocation.Text = rs.Fields("Location") & ""
            txtTitle.Text = rs.Fields("Title") & ""
            txtFormat.Text = rs.Fields("Format") & ""
            txtLength.Text = rs.Fields("Length") & ""
            txtGenre.Text = rs.Fields("Category") & ""
            
        'Loads the title code to be used to load images and videos
            mediacode = rs.Fields("Code")
        Else
            MsgBox "You have reached the first or last record.", vbExclamation, "Cannot Move"
        End If
            
End Sub

I also have a combo box that lists all of the movie titles using the following code:

'Loop adds the titles to the combo box
        Do Until rs.EOF = True
            cmbSelector.AddItem rs.Fields("Title")
            rs.MoveNext
        Loop

And finally, the user can select a title from the combo box to load the related record using this code:

Private Sub cmbSelector_Click()
    Dim strSQL As String
    
    'This SQL specifies the data to be loaded
        strSQL = "SELECT * FROM Files WHERE Title = '" + cmbSelector.List(cmbSelector.ListIndex) + "'"
    
    'Closes any open recordset
        rs.Close
    
    'Uses the SQL to load the data into the recordset (rs)
        rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
 
    'Puts the data (from the recordset) into the controls
        FillFields
 
End Sub

The problem with this method is that once the user selects a title from the combo box, the 'next' and 'previous' record buttons do not function because there is now only one record. Someone in my other thread said I needed to change my combo box selection to use the seek method instead of loading in just 1 record.

Can anyone help?

[Dnereb]

So now you have to decide on a major issue.

Do You want to use SQL... giving me more to code but locking the database less
and in general create a faste application.
Or do you want to use a dynamic recordset locking up more but easier to code.

If you opt for the first (my preference) your code could be improved to use an readonly forwardonly recordset to load the first record and the listbox.
these are much faster.
Close it afterwards. And put The listindex in to a varaible at form level

Private CurrentRecordIndex as Long

Rewrite your Fillfields to accept a (Optional) string argument and use the SQL system to find your record.

If you want the next record you can add 1 to this variable, Set the listindex to this value. give the text of the listbox as argument to your fillfields and done.

This way you can minimize data trafic.
Another thing is you need to index these fields to be able to use .seek

Makes me wonder do you use a bound or unbound form? (ADODC = bound)

[gt123]

I'm all for which ever method you think is best, I just don't want the code to go way over my head. As for the unbound / bound question, I have no idea what the difference is. I'm very new to this as I only went through Beacon's ADO tutorial two days ago.

Thanks for your help.

[Dnereb]

Okay let's keep it simple.
Bound form's it isn't the way "Pro's" work but to gewt a grip, it is easier to use.
Some basic questions

First things first.
- Are you in VB 6.0 or ACCESS VBA

- Do you only want to search for things or do you want to be able to chang, add or delete as well?

Bound means your dastabox is directly linked to the data in the database and if you change a value it's automaticly changed in the database as well.
downside is record locking and errorhandling is done by the database not you.
Unbound means you have to take care of everything.