Listbox

Can someone please help me with the code to populate a listbox with data in a table

What I want to do is enter a name (txtname.text) and then search for all records in the table which matches that name and then display it in a listbox.

public sub search()

Dim strFind As String

strFind = "surname like '" & txtClientSurname & "*' "
rsRecord.Find strFind

end sub


How do I display all records in the recordset to the listbox from where the user can double click the record he wants.

ANY help please!!

You could try something like this:

Code:

---

While Not rsRecord.Eof
  list1.additem rsRecord.fields("surname")
  list1.itemdata(list1.newindex) = rsRecord.fields("ID")
  rsRecord.MoveNext
Wend

---

Hope this helps,

Just some little amendments, Negative0. I hope you won't mind.

Before the 'while' loop in your code put:

Code:

---

List1.Clear

---

And whatever code you use to display the record, put it in the DoubleClick event of the listbox.

Maybe you have already figured out that much...

Thanks for this code!! What does the 2nd line ( list1.itemdata(list1.newindex) = rsRecord.fields("ID"))
in the while statement do?? If I take it out all surnames in the recordset is displayed on the listbox!!

Does the second line handle the case where I want 2 or 3 columns?? ie

Surname Tel Fax
White 8072766 8070263
Black 5465656 687634

How do I do this??

While Not rsRecord.Eof
list1.additem rsRecord.fields("surname")
list1.itemdata(list1.newindex) = rsRecord.fields("ID")
rsRecord.MoveNext
Wend

TO add the telephone and fax try this:

Code:

---

List1.clear
While Not rsRecord.Eof
  list1.AddItem rsrecord.fields("surname") & Space(20 - Len(rsrecord.fields("SurName"))) & _
rsrecord.fields("Tele") & Space(20 - Len(rsrecord.fields("Tele"))) & rsrecord.fields("FAX")
  list1.itemdata(list1.newindex) = rsRecord.fields("ID")
  rsRecord.MoveNext
Wend

---

The list1.Itemdata line allows you to store the ID from the record with the list box. You can then use this ID to reference the record.

Negative0,

This works great - I now have the 3 columns.

Sorry for being stupid, but I still cannot get this line to work....

list1.itemdata(list1.newindex) = rsRecord.fields("ID")

I get this error on the line:- "ADO could not find the object in the collection corresponding to the name or ordinal requested by the application"

I do not have a field "ID" in my table!!?? Is this a variable which I must set or what.

Thanks for your help.

The ID is an easy way to access the record you have selected, if you do not have one you can omit the line.

NOW I've got you - it took a while, but I've got it!!!

Thanks for the help Negative0.