Problem with Listbox

[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:

[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. ;)

[Yomet]

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