?
Solved

How can I find a specific record in my recordset?

Posted on 2003-03-02
10
Medium Priority
?
231 Views
Last Modified: 2013-12-25

I have a combobox on one of my forms that contains a series of transaction numbers. When the user selects one of these numbers I want my program to go to the corresponding record and show the results on my form.

I am able to find the record in the database and show the result but my recordset is reduced to one record (the one I found).

This is my code, activated once an item is selected from the combobox:

Set mrst = mdbs.OpenRecordset("select * from tblTable where FieldName = '" & cboTransNum.Text & "'")


What I want my program to do is go to the record in my recordset (for example record 20 of 100) instead of finding the matching record and limiting my recordset to one record.

This way I can use my existing functions for (update, next record, new record etc.)

Any help would be greatly appreciated.
 
0
Comment
Question by:Jake28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 1

Expert Comment

by:eslsys
ID: 8052521
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.
0
 

Author Comment

by:Jake28
ID: 8052678
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
0
 
LVL 1

Expert Comment

by:eslsys
ID: 8056429
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


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Jake28
ID: 8061973
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.
0
 
LVL 1

Expert Comment

by:eslsys
ID: 8063312
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
0
 

Author Comment

by:Jake28
ID: 8068596
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!
0
 
LVL 1

Expert Comment

by:eslsys
ID: 8072216
Ok Jake28, give me a few hours and I'll get back to you
0
 
LVL 1

Accepted Solution

by:
eslsys earned 240 total points
ID: 8074963
Ok, apologies - I should have seen it earlier but sometimes its easier to see things when you have the code.

The reason for the run time error 3251 is the way you are opening the Recordset, you are originally opening it with a Select SQL statement whereas the Seek method only supports a table type recordset so therefore the Form Load statement

Set mrst = mdbs.OpenRecordset(mcstrSQL)

should read

Set mrst = mdbs.OpenRecordset("tblClans", dbOpenTable)
which populates the RecordSet with the entire table.

or in the Combobox Click Event you could change the original RecordSet by using the following:

mrst.Close
Set mrst = Nothing
Set mrst = mdbs.OpenRecordset("tblClans", dbOpenTable)

But this is not going to achieve the original objective of moving to the selected record as you have now changed the RecordSet anyway !

so ... (new approach number 3) :)

Assuming CL_Name is setup in your table as an index (it would be better to refer to it differently but you would have to change the index in Access) and is unique then the following should work:

Leave the form load section as is and change the the combo1 click event as follows:

Dim strSearch as String
.
.
.
strSearch = "CL_Name = '" & combo1.text & "'"

With rstCustomers
   ' Populate recordset.
   .MoveLast
   ' Find first record satisfying search string. Exit
   ' if no such record exists.
   .FindFirst strSearch
   If .NoMatch Then
      MsgBox "No records found with " & strSearch
   End If
End With

I'm Keeping my fingers crossed !
0
 

Author Comment

by:Jake28
ID: 8076325
That is it!!!

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

Expert Comment

by:eslsys
ID: 8080368
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question