Listbox

[turfbult]

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!!

[Negative0]

You could try something like this:

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

Hope this helps,

[honeybee]

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

Before the 'while' loop in your code put:


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...

[turfbult]

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

[Negative0]

TO add the telephone and fax try this:
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.

[turfbult]

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.

[Negative0]

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

[turfbult]

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

Thanks for the help Negative0.