One table (Categories) has
CategoryName and CategoryID.

Another table (Products) has
ProductName and CategoryID.

By picking a CategoryName in a Combobox
display all the ProductNames in a Listbox.


by Rick Meyer

1. Start a new standard exe.
2. In Project->Components add Microsoft Databound List Controls
3. In Project->Components add the ADO Control
4. On Form1 put two ADO Data Controls.
5. On Form1 put a DBCombo named DBCombo1
6. On Form1 put a DBList named DBList1

      Form1 should look like the picture above.

Installed with Visual Basic is a demo Northwind database. It should be in your VB directory called Nwind.mdb (mdb is the extension used for an Access database). If you have VB 5.0 then it is at C:\Program Files\DevStudio\VB\Nwind.mdb

To connect to a database using an ADO Data Control the connection string must be set in the properties of the control. Click [here] to refer to my transition page to see how to do this. The Provider will be the Microsoft.Jet and the Data Source is nwind.mdb mentioned above.

Since you have already provided the connection string, a drop down list of the tables in the database should appear when you click on the RecordSource (or Table). Choose Categories. Do this just for the first Data Control not for Data2.
Now connect DBCombo1 to the first Data Control. To do this find the RowSource property of DBCombo1. Click on the tab and choose the first Data Control from the dropdown list.

Follow that up by finding the RowSource property on the DBList1 Control and setting it to the second Data Control.

Now we want to display in DBCombo1 all the categoryNames of the categories. Note that since we have already set the RecordSource to categories for Data1, and connected DBCombo1 to Data1, VB is able to provide you with a dropdown list of all the fields in the table named Categories.

You will find this dropdown list in the DBCombo1 property called Listfield. So click the tab and choose CategoryName.

Also set the DBCombo1.Text property to "" (delete what is there)

If you run the program at this point you will notice that the combobox contains all the product category names but choosing one will not display anything in the listbox. We want to display ProductNames from the Products table in the listbox so we want to enter ProductName in the Listfield property.

However since we have not entered a RecordSource in the seconf Data Control, there is no convenient dropdown list from which to choose ProductName. The solution is to type in ProductName in the Listfield property of DBList1.

Now the properties are set and the code is necessary to make it happen. Amazingly very little code is needed in the DBCombo1_Change event. Copy and paste the following code in the code window and you are all done! 
Option Explicit

Private Sub DBCombo1_Change()
    ISGData2.SQL = "SELECT * FROM Products " & _
        "WHERE CategoryID = " & _
        "(SELECT CategoryID FROM Categories " & _
        "WHERE CategoryName = '" & DBCombo1.Text & "')"

    ISGData2.Refresh
End Sub
There are 2 references to ISGData above. Change these to the name of your data control.
Note that the lines above have been broken with the underscore continuation character for easier reading. But the main point to get is that when a choice is made from DBCombo1, then the ISGData2.SQL property is completed in such a manner as to return just the fields of the records that we want.

This is accomplished by and is your introduction to SQL. A standard SQL statement is a string of characters that most database engines can interpret and act upon. It includes keywords of SELECT, FROM, and WHERE to return the resultant records.

What we have here is called a nested SQL statement since there is also an SQL statement within the main statement (added with parenthesis). As you see SQL statements are nearly self-documenting. The trick that we are doing with the nesting is to tie together the common fields of the two separate tables.

When we fill in this SQL property in ISGData2, then DBList1 has all the information it needs to populate itself, and does so when we .Refresh