How to properly use Seek in DAO database

Question

I'm trying to search for currently selected item in my listbox control on my table.

In my listbox control after update event, I have this code

Private Sub lst_MainList_AfterUpdate()
    Dim theDB As DAO.Database
    Dim theProposalsTable As DAO.Recordset

    Set theDB = CurrentDb
    Set theProposalsTable = theDB.OpenRecordset("tbl_PROPOSAL", dbOpenDynaset)

    theSeeker theProposalsTable, Me.lst_PPpg_MainList.Value    
End Sub

Then I have a sub on my Module1 with this code. I got this from an example code @ https://msdn.microsoft.com/en-us/library/office/ff836416.aspx

Sub theSeeker(ByRef rstTemp As Recordset, intSeek As Integer)

   Dim theBookmark As Variant
   Dim theMessage As String

   With rstTemp
      ' Store current record location.
      theBookmark = .Bookmark
      .Seek "=", intSeek

      ' If Seek method fails, notify user and return to the
      ' last current record.
      If .NoMatch Then
         theMessage = "Not found! Returning to current record." & vbCr & vbCr & "NoMatch = " & .NoMatch
         MsgBox theMessage
         .Bookmark = theBookmark
      End If
   End With
End Sub

I am getting Runtime Error 3251 Operation is not supported for this type of object.

When I hit Debug, it highlights .Seek "=", intSeek

Answer 1

In this point from the linked page ...

Locates the record in an indexed table-type Recordset object

... "table-type Recordset" means you must use dbOpenTable instead of dbOpenDynaset with OpenRecordset()

That point is critical. If you can't open the table with dbOpenTable, you can't use Seek. And dbOpenTable can only be used with native Access tables contained in the current database. It can not be used with any kind of linked table.

So if dbOpenTable is compatible with tbl_PROPOSAL, this change will eliminate the first error ...

'Set theProposalsTable = theDB.OpenRecordset("tbl_PROPOSAL", dbOpenDynaset)
Set theProposalsTable = theDB.OpenRecordset("tbl_PROPOSAL", dbOpenTable)

If that does work, the next error will be #3019, "Operation invalid without a current index." That happens because you must set the controlling index before calling Seek ...

With rstTemp
  ' Store current record location.
  theBookmark = .Bookmark
  ' Set the index. 
  .Index = "PrimaryKey" '<- use your index name here
  .Seek "=", intSeek

If you need to list the names of your table's indexes, you can examine its TableDef.Indexes collection. Here is an Immediate window example with a table in my database ...

set db = CurrentDb
for each idx in db.TableDefs("tblFoo").Indexes : ? idx.name : next
id
PrimaryKey