[RESOLVED] Querying

[Plant]

Here's what I'm trying to do:

1. An Access table exists with two fields. OrderID and ItemID
2. The user enters, in a VB6 form, the OrderID they wish to find information for and clicks a command button.
3. The data from the ItemID field for each instance of the entered OrderID are added to a listbox.

Using a data control.

It should be straightforward to carry out, but I'm not sure how to perform the match between OrderID and ItemID in VB? Would I have to use SQL?

Cheers

[salvelinus]

Don't know about the data control, since I don't use it, but the SQL is simple:

"SELECT tblOrderItem.ItemID " & _
"FROM tblOrderItem " & _
"WHERE tblOrderItem.OrderID = " & Val(Me.txtUserOrderId)

where OrderID is a numeric value. You may want to replace Val() with CInt() or some other casting function, depending on your data. If OrderID is a text value, forget casting and surround the value in the WHERE clause with single quotes.

[Plant]

Thanks, but I'd really rather avoid using SQL if I can as I'm short on time and I've never used SQL before.

I know I've limited myself by using a data control but I didn't really have any other option due to system software and permission restrictions where I started work on the program.

[salvelinus]

I believe you're likely using SQL whether you realize it or not. If you look at the properties of the data control, it will probably have a Data tab, and in there will SQL based on whatever you did when creating the control.
There's not a lot you can do in VB (or any programming language I know) without knowing some SQL.
SQL is a relational database language that is used almost everywhere.

[Plant]

Yeah, I've replaced my data control for an ADO and implemented your SQL in the following code:

Private Sub Form_Load()
 
Set Database = OpenDatabase("database.mdb")
Set rsOrderJunction = Database.OpenRecordset("tblOrderJunction", dbOpenDynaset)
Set rsAlbum = Database.OpenRecordset("tblAlbum", dbOpenDynaset)
Dim AlbumID As String
Dim sSQL As String
 
If Not rsOrderJunction.EOF Then rsOrderJunction.MoveFirst
Do While Not rsOrderJunction.EOF
sSQL = "SELECT tblOrderJunction.OrderID " & _
"FROM tblOrderJunction " & _
"WHERE tblOrderJunction.OrderID = " & Val(Me.txtOrderID)
AlbumID = rsOrderJunction.Fields.Item("AlbumID").Value
 
rsAlbum.FindFirst "AlbumID=" & Str(AlbumID)
lstOrderAlbums.AddItem rsAlbum!Title
 
rsOrderJunction.MoveNext
Loop
 
 
 
End Sub

Look alright?

[salvelinus]

Well, no, although since I use mostly DAO, can't be sure of your ADO code.

Since this is in your Form_Load() event, make sure you've declared your recordsets & db somewhere accessible, like a code module.

I would never use a name like Database for a variable or object name. There's a good likelihood it's a reserved word, and even if not, will cause confusion to others reading your code. Generally use "db" myself, but that's personal preference.

[Plant]

Okay, ignore that last post.

Once I have the query in place, how do I implement it, adding the results to my listbox? I thought I'd cracked it, but it seems to add every item on order to the listbox, rather than just the ones associated with that order, so it's obviously not reading from the query.

[salvelinus]

Your Loop keeps executing the same code. Set your recordset before you enter the loop. Right now you're just opening the table with your recordset object.
I'm not sure of the ADO code, but in DAO you'd want something like this:

sSql = "yada yada"
Set rs = db.OpenRecordset(sSql)
If Not (rs.BOF and rs.EOF) Then
   Do While Not rs.EOF
   '   code
   '   rs.MoveNext
   Loop
End If

Also make sure your listbox properties Data Tab isn't set to something already.
Right now you have the recordset open the whole table. You set sSql each time through the loop, but never use it. You also don't want to set it each time. Set it for the recordset first.

[Plant]

Ah, thanks a lot, I think I'm getting the hang of this now. I'm still using ADO with the following code:

Private Sub cmdGoOrder_Click()
Set dbDatabase = OpenDatabase("C:\database.mdb")
Dim sSQL As String
Dim OrderID As String
Set rsOrder = dbDatabase.OpenRecordset("tblOrder", dbOpenDynaset)
Set rsAlbum = dbDatabase.OpenRecordset("tblAlbum", dbOpenDynaset)
 
rsOrder.FindFirst "OrderID=" & txtOrderID.Text
txtCustomerID.Text = rsOrder!CustomerID
chkPaid.Value = rsOrder!OrderPaid
chkSent.Value = rsOrder!OrderDispatched
 
lstOrderAlbums.Clear
lblCurrentOrder.Caption = txtOrderID.Text
 
sSQL = "SELECT tblOrderJunction.AlbumID " & _
        "FROM tblOrderJunction " & _
        "WHERE tblOrderJunction.OrderID = " & rsOrder!OrderID
 
Set rsOrderJunction = dbDatabase.OpenRecordset(sSQL)
 
If Not rsOrderJunction.EOF Then rsOrderJunction.MoveFirst
    Do While Not rsOrderJunction.EOF
    rsAlbum.FindFirst "AlbumID=" & rsOrderJunction!AlbumID
    lstOrderAlbums.AddItem rsAlbum!Title
    rsOrderJunction.MoveNext
    Loop
    
Set rsOrderJunction = Nothing
 
 
End Sub

Which seems to do the job. Cheers.

[si_the_geek]

I'm still using ADO

No you aren't - that is DAO code.

Using DAO from VB6 has always been a bad idea, and is worse now that it is obsolete (much worse than unsupported).

You would be better off switching to ADO instead - see the ADO Tutorial link in my signature for example code.

[Plant]

Sorry, what I meant was I'm using an ADODC.

I don't really have any need to switch the code at the moment as the bulk of my program is working as it should now, but I'll keep your tutorial for future reference, cheers.

[salvelinus]

Si is correct that if you're working with VB6, or anything not Access VBA, ADO is definitely the better way to go. DAO works fine within Access vba, at least through Access 2K, and since that's what I work with 95% of the time, I'll usually post DAO code in threads where it seems appropriate.
It's good that the DAO code is working for you, but if you're working in VB6 and have problems with other stuff, seriously consider Si's advice & move to ADO.
FWIW: I did learn ADO, & some .Net, but really don't use either enough to keep current & give advice. I'm at a small company where everything was written in DAO, and no one else here knows ADO, etc., so that's what I use.
We did just get SQL Server, so maybe things will change. Our main app was written in Delphi, though.