Select info from DB to Listbox using DAO

[cvaden]

SQL like:

SELECT app_title AS variable WHERE Cd_Title = cboCdTitle.Text

Then add every thing that matches and add it to a list box.

Unfortunately, it only adds the very first apptitle, many times?!

---->CODE CUT<----
Set db = OpenDatabase("E:\projects\CD DB\db1.mdb")

SQLString = "SELECT COUNT(App_Name) As count FROM sheet1 WHERE Cd_Title='" & cboCdTitle.Text & "'"

Set rs = db.OpenRecordset(SQLString)
count = rs.Fields("count")

SQLString = "SELECT App_Name As apptitle FROM sheet1 WHERE Cd_Title='" & cboCdTitle.Text & "'"
Set rs = db.OpenRecordset(SQLString)
appname = rs.Fields("apptitle")

For I = 1 To count
lstApp.AddItem appname
Next I

--->END CODE CUT<---

Thanks in advance!

[MartinLiss]

Do this instead. You don't need to do any SQL to get the count.
=======================================
Set db = OpenDatabase("E:\projects\CD DB\db1.mdb")

SQLString = "SELECT App_Name As apptitle FROM sheet1 WHERE Cd_Title='" & cboCdTitle.Text & "'"
Set rs = db.OpenRecordset(SQLString)
appname = rs.Fields("apptitle")

Do Until rs.EOF
lstApp.AddItem rs!appname ' If this gives an error then change "!" to "."
rs.MoveNext
Loop

------------------
Marty
Can you buy an entire chess set in a pawn shop?


[This message has been edited by MartinLiss (edited 01-22-2000).]

[cvaden]

Thanks for your help guys, but Marty...even if I change the exclamation to a period it still says item not found in this collection!

I tried rs!appname, rs.appname, and
rs![appname]. But unfortunately, it only gives me any return to the listbox when I use just 'appname' by itself.

Unfortunately same problem.

[Clunietp]

try rs.Fields(0).value

[cvaden]

That did it...Turns out I was unable to get online...so I fiddled with it and got it to work....

I used....

rs.Fields("apptitle")

Thanks for everyone's help!

[Clunietp]

If you have multiple records with the same name, you can use:
SELECT DISTINCTROW App_Name....