Query result to listBox

[mike009]

hi

i have a simple query that will select from 2 tables
how can i display the result in list box

what i did is

Private Sub SearchB_Click()
Dim qry As String

qry ="Select Student.name , Student.address , Course.name , Course.proff 
        From Student , Course Where Student.id = Course.studentid"

mylistBox.RowSource = qry

End Sub

but in the listbox i see only the firs column in the select statement
how can i display all columns
thx

 

[Sergeant]

Look at the format tab for your listbox and see what your column count is...change accordingly.

 

[mike009]

>>Look at the format tab for your listbox and see what your column count is...change accordingly.

column count should be dynamic not static depands of the select statement....
?????

 

[Bodisathva]

you could establish a recordset from the query string first, which would allow you to access the .fields collection, then set the source for the listbox to the recordset.

 

[mike009]

thx Bodisathva
can you give me an example plz
thx

 

[Sergeant]

>>Look at the format tab for your listbox and see what your column count is...change accordingly.

column count should be dynamic not static depands of the select statement....
?????

It's not for me, unless I use the wizard to create the listbox. Did you check the column count?

 

[clerics]

Private Sub SearchB_Click()
Dim qry As String

qry ="Select Student.name , Student.address , Course.name , Course.proff 
        From Student , Course Where Student.id = Course.studentid"

mylistBox.RowSource = qry
[COLOR="Red"]mylistBox.ColumnCount = 4[/COLOR] [COLOR="SeaGreen"]<--- add this code here[/COLOR]
End Sub

Just 1 more row like this ...it ll be ok !

 

[Bodisathva]

thx Bodisathva
can you give me an example plz
thx

sorry mike...out for the weekend.

you can take cleric's example, but modify the number of columns to reference the .fields collection from the aforementioned recordset...

Private Sub SearchB_Click()
     Dim qry As String
     Dim rec As Recordset

     qry ="Select Student.name, _
           Student.address, _
           Course.name, _
           Course.proff _
       From Student, _
            Course _
      Where Student.id = Course.studentid"

     'assume no edits, otherwise use dbOpenDynaset
     Set rec = CurrentDb.OpenRecordset(qry,dbOpenSnapshot)

     mylistBox.ColumnCount = rec.fields.count
     mylistBox.RowSource = rec.OpenRecordset
 
End Sub

of course, you can also modify column widths based upon # of columns and width of the list box as well...