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 professional exe.
2. On Form1 put two data controls named Data1 and Data2.
3. On Form1 put a DBCombo named DBCombo1
4. 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

So go to the Properties Window and select Data1. Find the property called DatabaseName. When you click the tab you will be allowed to browse to find a database. Choose C:\Program Files\DevStudio\VB\Nwind.mdb or your location of the Northwind database.

Now do the same for Data2

Access includes all its tables in a single file. Since you have choosen a DatabaseName, the Data Control will now be able to provide you with a list of all the tables in it.

For Data1 find the RecordSource property and click on the tab. A dropdown list of all the tables in the database will appear.

Choose Categories.

Do NOT do this for Data2.

Now connect DBCombo1 to Data1 and DBList1 to Data2

To do this find the RowSource property of DBCombo1. Click on the tab and choose the Data1 control from the dropdown list. (VB knows the names of the data controls you placed on the form.)

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

Now we want to display in DBCombo1 all the categoryNames of the caterories. 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 Data2, 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! 
Private Sub DBCombo1_Change()

Data2.RecordSource = "SELECT * FROM Products " & _
        "WHERE CategoryID = " & _
        "(SELECT CategoryID FROM Categories " & _
        "WHERE CategoryName = '" & DBCombo1.Text & "')"

Data2.Refresh

End Sub
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 Data2.RecordSource property (which was not set in the properties windows) 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 RecordSource property in Data2, then DBList1 has all the information it need to populate itself, and does so when we .Refresh