Member Avatar for Yoshidex

Hey there, I'm learning more and more every time I post and/or read contributions by other people, but I still have a long way to go. I have this problem now: I designed a Find command and it works fine, but I want all of the results to be written into a Listbox, so the user can select which one is the record it wants and I only manage to input one record, so if there are a lot of people named "John" it will only return the first one it finds. Can anyone help me, pls? The code below is the one I'm using:

Private Sub cmdFind_Click()
Dim strSearch As String
strSearch = CStr("%" & findbox.Text & "%")

Dim strList As String
Dim n As Integer

lstPacientes.Clear

With adoPrimaryRS
.MoveFirst

'Searches for any record that has the typed string on the Table;
.Find "Nombre LIKE '" & strSearch & "'", 0, adSearchForward
n = .RecordCount
Do
strList = .Fields("Nombre")
'Adds items for each string found;
lstPacientes.AddItem strList
Loop Until n

End With

End Sub

Thanks in advance for all the help you can supply me with. :cheesy:

  • 2 Contributors
  • 2 Replies
  • 2K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Member #53442

Dani AI

Generated

you are right that .Find only positions the recordset on the first match. To list all matches you need to loop and ask .Find to continue searching from the current row. was on the right track; the key is to either move past the current row or use the SkipRows argument so you do not keep finding the same record again.

Here is a compact pattern you can drop in. It does a contains search, adds every match to the listbox, and shows a friendly message if nothing is found.

With adoPrimaryRS
    Dim crit As String
    If Len(Trim$(findbox.Text)) = 0 Then Exit Sub

    lstPacientes.Clear
    .MoveFirst

    ' Use LIKE with wildcards at both ends for "contains"
    crit = "Nombre LIKE '*" & Replace(findbox.Text, "'", "''") & "*'"

    .Find crit                   ' first match
    Do While Not .EOF
        lstPacientes.AddItem .Fields("Nombre").Value
        .Find crit, 1, adSearchForward   ' skip current row, find next
    Loop

    If lstPacientes.ListCount = 0 Then
        MsgBox "No records matched your criteria."
    End If
End With

Notes that save head-scratching later:

  • .Find searches from the current position and returns only one row per call. Use SkipRows:=1 (as above) to advance the search, or call .MoveNext before the next .Find. See Microsoft’s Find docs and example. Find method (ADO) Find method example (VB)
  • Do not drive the loop with .RecordCount; with forward-only cursors it can be -1. Prefer looping until .EOF. If you need a reliable count, open the recordset with a client-side static cursor. RecordCount property (ADO)
  • Alternative: filter the existing recordset and iterate the filtered view, then clear the filter when done. Filter property (ADO)

Recommended Answers

All 2 Replies

Member Avatar for Yoshidex

Hi there, I just wanted to inform that I changed the code so that null characters don't display an error message.

Private Sub cmdFind_Click()

Dim strSearch As String
strSearch = CStr("%" & findbox.Text & "%")

Dim strList As String

With adoPrimaryRS
If findbox.Text <> "" Then
    lstPacientes.Clear
    .MoveFirst
    'Searches for any record that has the typed string on the Table;
    .Find "Nombre LIKE '" & strSearch & "'", 0, adSearchForward
    strList = .Fields("Nombre")
    'Adds items for each string found;
    lstPacientes.AddItem strList
End If

End With

End Sub

While doing this I figured out I need an error message if the record is not found, any ideas on how to do this? 'Cause I can' come up with the solution. My first question is still there, how can I input all of the results of the .Find in the Listbox?
Thx for all your help. ;)

Member Avatar for Member #53442

Hi Yoshidex,

I am not totally familiar with the workings of ADO but I do know that you need to put a loop into your code that will loop through all the found records and add them one by one to the listbox.

I also included a check after the first ".Find" operation that verifies if we are already at the end of the recordset, i.e. no records were found, in that case I display a message telling the user that no records were found.

I changed the code you posted to include this loop

Private Sub cmdFind_Click()

Dim strSearch As String
strSearch = CStr("%" & findbox.Text & "%")

Dim strList As String

With adoPrimaryRS
If findbox.Text <> "" Then
   lstPacientes.Clear
   .MoveFirst
   'Searches for any record that has the typed string on the Table;
   .Find "Nombre LIKE '" & strSearch & "'", 0, adSearchForward
   If .EOF Then
      'no records found
      MsgBox "No records matched your criteria."
   Else
      While Not .EOF
         Found = True
         strList = .Fields("Nombre")
         'Adds items for each string found;
         lstPacientes.AddItem strList
         .Find "Nombre LIKE '" & strSearch & "'", 0, adSearchForward
      Wend
   End If
End If

End With

End Sub

The two things I am not sure of are:
- if simply repeating the .Find will actually cause the recordset to continue searching from the last record found or start from the beginning
- if the "Not .EOF" condition will become true when the last record is found or not.

Anyway, that's what I came up with and I hope it helps

Happy coding

Yomet

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.