Returning a record

I have a database that looks somewhat like this:

Name - Item1 - Item2 -Item3

Test -sd -ds -jk
Test -st -dd -jl
Test -ss -dh -jj
test1 -ds -jk -kl

Etc.
Test is enetered in the table multiple times. I want to show the name column in a listbox. I already have code to do that and I remove the doubles so that Test only shows up once. Here is the problem. I want to click on test and in another listbox or textboxes have all three of the other values appear for all the Test entries.I.E.

Listbox1 Listbox2

Test -------------> sd - ds - jk
st - dd - jl
ss - dh - jj

I know it is confusing but oh well.

Upon Clicking on Test
Do this 1. Search the database based on 'Test'
2. You get a recordset of the items returned
3. Use the recordset to populate the list box

For example

select * from table where Name = 'Test'

with yourRecordSetName
while not .EOF
listbox2.additem .fields(0) & " " & fields(1) etcc..
end with


In any case, hope I was of help

I think that is a good way to do it. But when I click the item in the list it doesn't do anything.

Code:

---

Private Sub listry_Click()
Dim I As Integer
Set rs2 = db1.OpenRecordset("SELECT * " & "FROM [entered] where [RiderNumber] = '" & listry.Selected(I) & "'", dbOpenDynaset)
With rs2
While Not .EOF
listry1.AddItem rs2![ClassName]  & " - " & rs2![ClassNumber]
Wend
End With
End Sub

---

And let us know what u find

Just a note:
Why don't u use ADO?

Alright, I got it to add something but the only way I could get it to do that is using the code below. This code gets stuck in an infinite loop as anyone can see. But wvwn when this worked it only added one of the values for the name or number.

Code:

---

Dim rs3 As Recordset
For I = 0 To listry.ListCount - 1
If listry.Selected(I) Then
Set rs3 = db1.OpenRecordset("SELECT * " & "FROM [entered] WHERE [RiderNumber] = '" & listry.List(I) & "'", dbOpenDynaset)
While Not rs3.EOF
listry1.AddItem rs3![ClassName]
Wend
End If
Next

---

As for ADO, I am a begginer programmer and did not know about ADO I saw how to use DAO first. If it would make this easier I will recode the program but if it makes no difference then I will just leave it.

Hi,
You need to add a MoveNext call in your loop otherwise it will infinitely loop around the first record (as you found!)

Code:

---

Dim rs3 As Recordset
For I = 0 To listry.ListCount - 1
If listry.Selected(I) Then
Set rs3 = db1.OpenRecordset("SELECT * " & "FROM [entered] WHERE [RiderNumber] = '" & listry.List(I) & "'", dbOpenDynaset)
While Not rs3.EOF
listry1.AddItem rs3![ClassName]
rs3.MoveNext 'goto next record
Wend
End If
Next

---

Hope this helps

Shaun

:D Code works great. Amazing what a .movenext can do. Thanks a million!;)