Data in Recordset to fill a List Box?

[Joao]

I want to get some data from a table to fill up a List Box... I am using the
following code...

while....
ObjLista.AddItem rstSQL.Fields(0), 1
....
wend

but I get an "Object required" error... anyone?

 

[Klatuu]

There is no real reason to do it that way.
First, a list box is just a control, so you would address it as a control on
the form.
Me.MyListBoxName

Also, it would be better and faster to create a query to return the data and
make that the row source of the list box.

 

[Joao]

Thanks, you mean like this?

SQLString = "SELECT TOP 10 ****** FROM ******* WHERE ***** = " & **** & "
ORDER BY ******* DESC"
Set rstSQL = BD.OpenRecordset( _
SQLString, dbOpenDynaset)

Me.ObjLista.RowSource = rstSQL

? I get a mismatch error....

 

[Klatuu]

You don't need to open the recordset. All you need is to put the SQL
statement in the list box's Row Source property in the the properties dialog
box.
In Jet SQL, you also need the ; at the end of the statement. In the row
source box, you don't need the quotes except to deliniate values for text
fields.

SELECT TOP 10 ****** FROM ******* WHERE ***** = **** & ORDER BY ******* DESC;

 

[Douglas J. Steele]

No.

SQLString = "SELECT TOP 10 ****** FROM ******* " & _
"WHERE ***** = " & **** & " ORDER BY ******* DESC"

Me.ObjLista.RowSource = SQLString

 

[Joao]

Thanks guys, but using that, Douglas, I get the SELECT TOP10... string into
the List box...

 

[Douglas J. Steele]

Make sure the RowSourceType is set to Table/Query, not Value List.

 

[Joao]

I am closing on it...

SQLString = "SELECT ***** FROM Processos WHERE ***** = " & ***** & " ORDER
BY ****** DESC;"

Set rstSQL = BD.OpenRecordset( _
SQLString, dbOpenDynaset)

num = 0
t = rstSQL.RecordCount
While t > 0
With rstSQL
Me.ObjLista.AddItem .Fields(0), num
num = num + 1
t = t - 1
End With
Wend

But I am getting only the first number in the query multplied by the number
of records in the recordcount

 

[Joao]

Thanks for your help, I finally made it, using this: (Must refine the code
though)

SQLString = "SELECT *** FROM Processos WHERE *** = " & *** & " ORDER BY ***
DESC;"

Set rstSQL = BD.OpenRecordset( _
SQLString, dbOpenDynaset)

z = 0
t = rstSQL.RecordCount - 1
While t > 0
Me.ObjLista.AddItem rstSQL.Fields(0), z
rstSQL.MoveNext
z = z + 1
t = t - 1
Wend