How can I find a specific record in my recordset?

Use the Find method on the Recordset, for example

In the Form Load event

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=yourDb"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select * from tblTable", db, adOpenStatic, adLockOptimistic

When an item is selected in the combobox:

adoPrimaryRS.Find FieldName = "'" & cboTransNum.Text & "'"

This will move within the recordset to the required record without reducing it.

Is there any way to get this to work with DAO(3.6)?

This is how I'm connecting...

Option Explicit

  Private Const mcstrDBName As String = "\Data.mdb"
  Private Const mcstrSQL = "SELECT * FROM tblTable"
  Private mdbs As Database
  Private mrst As Recordset

Form_Load()

  Set mdbs = OpenDatabase(App.Path & mcstrDBName)
  Set mrst = mdbs.OpenRecordset(mcstrSQL)

End Sub

Same idea really just a more cumbersome method using Seek.

Set mdbs = OpenDatabase(App.Path & mcstrDBName)
Set mrst = mdbs.OpenRecordsetmcstrSQL, dbOpenTable)

' You must open a table-type Recordset to use an index
' and the Seek method.

When an item is selected in the combobox:
   
Dim varBookmark As Variant

With mrst
   ' Set the index to whatever key your are searching.
   .Index = "PrimaryKey"
   ' Store current bookmark in case the Seek fails.
   varBookmark = .Bookmark

   .Seek "=",  cboTransNum.Text ' depending on the values you may have to play around with enclosing the text with quotes

   ' Return to the current record if the Seek fails.
   If .NoMatch Then
       MsgBox "Seek Failed"
       .Bookmark = varBookmark
   End If

End With

Hi eslsys,

For some reason it still won't work.

I checked to make sure that the proper values were being passed and everything seems to be in order. The value for my index is pointing to my field that is indexed and set as a primary key in my database. The value set to Seek is the value selected in the combobox.

When I run the procedure, nothing happens to the data on my form but I did notice that my movenext and moveprevious functions no longer work and I get an error message.

I'm really stuck on this one. If you have any more suggestions I would really appreciate it. Thanks.

Hi Jake28

If possible could you post the form code here and also the error message you are getting on movenext and moveprevious.

Also, some details on the Primary key for your table i.e. is it a string value, long integer etc. Finally, can you give me some sample values from your combobox.

Basically I plan to create a table in Access (2000 or 98 ?) and test the form using DAO and we should be able to figure it out from there

Hi eslsys,

Thanks for your response.

The error I keep getting is "Run-Time error '3251':
Operation is not supported for this type of object." when I select any record navigation after using the seek method. Before this, I receive the "seek failed" msgbox as indicated in the code (a match should be found).

The primary key in my Access 2000 table is Text datatype. It is indexed with no duplicates.

The values contained in this text field and that appear in the combobox are a series of 15 digit codes (ie. 060062-67876765).

Unfortunately, the form code is very lengthy with calls to a separate module for navigation controls. I started with some sample code from Planet Source Code (thx Steve Grant)that should provide a scaled down version of what I based my application on. It can be found at www.planet-source-code.com by searching for "DB Template" in the Visual Basic category at the top of the page. Bacically, by creating a combobox (Combo1) on the form and applying the seek method, you can replicate my problem.

The only thing out of the ordinary would be a small procedure to popluate the combobox items when the form loads and the addition of the combobox event.

Option Explicit

Private Const mcstrMod As String = "frmMain"

' The db the form will use.
Private Const mcstrDBName As String = "\DB.mdb"

' The SQL the form will use.
Private Const mcstrSQL = "SELECT tblClans.CL_ID, _ tblClans.CL_Name, " & "tblClans.CL_Year, _ tblClans.CL_Note " & "FROM tblClans;"

Private mdbs As Database
Private mrst As Recordset

Private Sub Form_Load()

  Const cstrProc As String = "Form_Load"
  'On Error GoTo PROC_ERR
 
  Set mdbs = OpenDatabase(App.Path & mcstrDBName)

  Set mrst = mdbs.OpenRecordset(mcstrSQL)
 
  mfEditRecord = False
  mfNewRecord = False
 
  'Clears the contents of cboTransNum
  Combo1.Clear

  'Adds transaction numbers from tblData to cboTransNum
If mrst.RecordCount <> 0 Then
   Do While Not mrst.EOF
       'Adds all items from my primary field.
       Combo1.AddItem mrst("CL_Name")
       mrst.MoveNext
   Loop
   mrst.MoveFirst
End If
 
 
  DisplayCurrentRecord Me, mrst, CheckNavControls(Me, _ mrst, mfEditRecord, mfNewRecord), True
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  Call MsgErr(mcstrMod & "." & cstrProc, Err.Number, _ Err.Description)
  Resume PROC_EXIT
End Sub

*******************************

As for the code for the click event on the combobox I the equivalent of...

Private Sub Combo1_Click()
' You must open a table-type Recordset to use an index
' and the Seek method.
  Set mdbs = OpenDatabase(App.Path & mcstrDBName)
  Set mrst = mdbs.OpenRecordset("tblClans", dbOpenTable)

' When an item is selected in the combobox:
   
Dim varBookmark As Variant

With mrst
  ' Set the index to whatever key your are searching.
    .Index = "CL_Name"
 
  ' Store current bookmark in case the Seek fails.
  varBookmark = .Bookmark

    .Seek "=", "'" & Combo1.Text & "'"
  ' Return to the current record if the Seek fails.
  If .NoMatch Then
      MsgBox "Seek Failed"
      .Bookmark = varBookmark
  End If

End With
End Sub

If you need anything else, let me know.
Thanks again, and good luck!

Ok Jake28, give me a few hours and I'll get back to you

That is it!!!

Thank you very much for your patience on this one. Your help has been exceptional!

No problem Jake28, glad to be of help - hopefully you can return the favour to someone else some day.

Thanks for the points and the grade and best of luck with your project