listbox multiselect

[meme'1992]

Hi,
i have two table: Person and exam. The relation is 1:n.
I want to see in a listbox all the exam of that person.

so I write in vba:

Private Sub btn_find()
  Dim tmpRS As DAO.Recordset
  Set tmpRS = CurrentDb.OpenRecordset("SELECT exam FROM exam WHERE name = '" & Me.txt_name & "' ")
  Dim i As Integer
   For i = 0 To tmpRS.RecordCount
   Me.listbox.AddItem (tmpRS.Fields(i))
  Next i
End Sub

The exam that I insert in the DB are 12 records. But tmpRS.RecordCount is 1. So in the list box I see only one record. Is the SELECT wrong and select only one record?
What's the problem?
Thanks

 

[Minty]

Name is a reserved word, place square brackets around it [name] , or better still change the field name.

Add a MoveLast, MoveFirst to load the whole recordset, then get your record count.

 

[meme'1992]

THanks. I try to insert in the for movenext after the additem, but I see only the second record. It doesn't save the first record

 

[arnelgp]

Use partial search using Like:

Set tmpRS = CurrentDb.OpenRecordset("SELECT exam FROM exam WHERE name = '*" & Me.txt_name & "*' ")

 

[meme'1992]

in this way tmpRS.RecordCount is 0, and doesn't make the for.

 

[arnelgp]

With tmpRS
If not (.bof and .eof) then .movefirst
While not .eof
Me.listbox.AddItem (.Fields(0))
.movenext
Wend
.close
End with
Set tmprs=nothing

 

[arnelgp]

Set tmpRS = CurrentDb.OpenRecordset("SELECT exam FROM exam WHERE name Like '*" & Me.txt_name & "*' ")

 

[meme'1992]

IT WORKS!!
Thank you very much!!!

 

[arnelgp]

you're welcome.

 

[Pat Hartman]

Since this is Access VBA, listboxes can be bound to tables or queries. there is no need to load them with code unless the values don't exist in a table somewhere. No code is required. Just add a RowSource for the listbox.

SELECT exam FROM exam WHERE [name] = Forms!YourForm!txt_name;

 

[meme'1992]

Thanks!
If I want create a listbox with 3 column (nameperson, surname,exam), how can I do?