Populate Listbox with results from a query...

Can some one give me a code example of how to do this please? I have been trying to do this for WEEKS! with no luck. Help appreciated!

This is in Visual Basic 6.0 btw

Here is the query that I am trying to run:

SELECT Items.Item
FROM (Categories INNER JOIN [Sub Categories] ON Categories.CatID = [Sub Categories].CatID) INNER JOIN Items ON [Sub Categories].SubID = Items.SubID
WHERE ((([Sub Categories].[Sub Category])=[]));

For one: I need to be able to pass it a value?
For two: How can I get the results of that query in a list box?

Hi,

First, where is your query defined? In your database (I am assuming your are using access) or are you building the query on the fly?

Let's say you are building your query on the fly...

Dim db as database
dim rs as recordset
dim sSQL as string

Set db = Workspaces(0).OpenDatabase(app.path & "databasename.mdb")

sSQL = "SELECT Items.Item FROM " _
& "(Categories INNER JOIN " _
& "[Sub Categories] ON Categories.CatID = " _
& "[Sub Categories].CatID) INNER JOIN " _
& "Items ON [Sub Categories].SubID = " _
& "Items.SubID WHERE ((([Sub Categories]. " _
& "[Sub Category])=[" & variable & "]));"

'Note if Sub Category is a string you pass it surrounded by quotes:
& "[Sub Category])=['" & variable & "']));"

set rs=db.openrecordset(sSQL, dbopendynaset)

do until rs.eof
listbox.additem rs!field1 & vbtab & rsfield2 'etc...
'vbtab is for spacing
rs.movenext
loop

rs.close
db.close

That's it!!

Preeti



Set ProjectRs = ProjectDb.OpenRecordset(SQLStmt, dbOpenDynaset)

Ok, I know I am gonna make no sense when I say this but here it goes.

First of all I am new to VB (Obviously) :) and I didn't know that you could build a query on the fly(oopss)! That seems a better way to do it. Either way here is my delima.

I have a combo box, and two list boxes.
I can get the values from my database to populate the combo box by just doing a loop by index as well as the first list box. Now for the second list box things get a little tricky.

First problem: I am going to have to run a query based on the string value in the first list box (That's the value that I have to pass the query) How can I store that vaule in a variable?

Second problem: Preeti, I tried your code and ran into a problem with the rs!field, rs!field2 line of code what does that do and why would I have a problem there? Here is what I have so far, can you tell me what I am doing wrong?


Private Sub List1_Click()
List2.Clear
Dim db As Database
Dim rs As Recordset
Dim sSQL As String
Dim currentindex As String

Set db = Workspaces(0).OpenDatabase(App.Path & "/myprojects/test/inventory2.mdb")

currentindex = "Pine" ' But I need it to be something like this currentindex = list1.text or list1.list something that will grab the string that is currently highlighted in list1 listbox.
sSQL = "SELECT Items.Item FROM " _
& "(Categories INNER JOIN " _
& "[Sub Categories] ON Categories.CatID = " _
& "[Sub Categories].CatID) INNER JOIN " _
& "Items ON [Sub Categories].SubID = " _
& "Items.SubID WHERE ((([Sub Categories]. " _
& "[Sub Category])=['" & currentindex & "']));"
'Note if Sub Category is a string you pass it surrounded by quotes:

Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

Do Until rs.EOF
List2.AddItem rs!Field1 ' Don't understand this line of code? Especially the rs!...
'vbtab is for spacing
rs.MoveNext
Loop

rs.Close
db.Close


End Sub

Sorry to be such a dummy :)

[This message has been edited by GiD (edited 07-30-1999).]

[This message has been edited by GiD (edited 07-30-1999).]

Hi,

Sorry, I should have been more specific.

Second Problem:
rs!Field indicates the name of the field of your table. You replace the word "field" with the name of the field that you want to add to the listbox.

ie:

TableName: Temp
FieldName: ID
FieldName: Comment

list2.additem rs!Comment
'etc...

First Problem: You do not need to store the value in a variable, because you already have access to the value. The only reason you would need a variable is because you need this value later on which is probably not your case.Therefore, you can write:

currentindex = list1.text 'and it will work fine.

I hope that I've cleared up the problems.

Preeti

Ok here is my edited code:

List2.Clear
Dim db As Database
Dim rs As Recordset
Dim sSQL As String
Dim currentindex As String

Set db = Workspaces(0).OpenDatabase("c:\inventory2.mdb")

currentindex = List1.Text
sSQL = "SELECT Items.Item FROM " _
& "(Categories INNER JOIN " _
& "[Sub Categories] ON Categories.CatID = " _
& "[Sub Categories].CatID) INNER JOIN " _
& "Items ON [Sub Categories].SubID = " _
& "Items.SubID WHERE (([Sub Categories]. " _
& "[Sub Category]=['" & currentindex & "']))"

Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

Do Until rs.EOF
List2.AddItem rs!("Item")
rs.MoveNext
Loop

rs.Close
db.Close

This is the error I get:

Type-decleration character does not match declared data type

what am I doing wrong?

Preeti, Take a look at the new topic I added Need SERIOUS HELP!!

I stated out the whole project that I am trying to do, starting with the database design. Can you take a stab at it?

If you can't np, but I would really appreciate it if you would :)

When you are accessing the recordset field, there are two ways...

1: rs!fieldname

- OR -

2: rs("fieldname")

You can use one or the other, but not both!!

change rs!("Item") into the format of either option!! :)

And yes I did read your other note, and if this is all you need done, then you can do it yourself - Just stop driving yourself crazy!

Preeti.

PS - Feel Free to email me. I left my email in your other message.

Ok, did that and now I get the error message

This is a long one :)

Invalid use of '.', '!', or '()' in query expression ([Sub Categories]. " _
& "[Sub Category]=['" & currentindex & "']))"

Sorry man it takes too much of writing and explaining.

The Sql statement i wrote does same thing your Sql statement does. Just syntax difference.

If you have aol's Instant messenger my screen name is kishore111. you can send me a message and i can explain it clearly, Which will be more comfortable and cut this off.

I will be on online from 7:30pm to 8:30pm Eastern time.

Looking forward to talk.

You are making the query complex

Change the Sql to the Following :

if currentindex is integer then use
following Sql

sSQL="Select I.Item from Categories C,Sub Categories Sc,Items I where C.Catid=SC.Catid and Sc.Subid=I.SubId and Sc.Sub Category=" & Currentindex

If currentindex is a string then use following SQl

sSQL="Select I.Item from Categories C,Sub Categories Sc,Items I where C.Catid=Sc.Catid and Sc.Subid=I.SubId and Sc.Sub Category='" & Currentindex & "'"

One More Suggestion:
It is good to Avoid spaces within FieldNames.


I hope this Helps

I know that I am sounding REALLY dumb but, did you abbreviate some of that SQL statement? if so could you post the SQL statement in it's entirity please? Thanks :)