Pick a Name in a Listbox
to Display Data in Textboxes
From a Related Table

by Rick Meyer

1. Start a new standard exe.
2. In Project->Components add Microsoft DataBound List Controls
3. On Form1 put 3 data controls named Data1 - 3.
4. On Form1 put a DBList named DBList1
5. On Form1 put 6 Textboxes named Text1 - 6.

      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 and Data3.

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 Products.     Do NOT do this for Data2 or Data3.

Now connect DBList1 to Data1, Textboxes 1 - 3 to Data2, and Textboxes 4 - 6 to Data3.

To do this find the RowSource property of DBList1. 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 DataSource properties on the Textboxes and setting Text1 - 3 to Data2, and Text4 - 6 to Data3.

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

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

If you run the program at this point you will notice that the listbox contains all the ProductNames but choosing one will not display anything in the Textboxes.
Now we want to display in Text1 - 3 the three fields from the Products table and in Text4 - 6 the three fields from the Suppliers table. However, since we have not entered a RecordSource in Data2 or Data3 there is no convenient dropdown list from which to choose the Datafield property in the Textboxes. The solution is to type in the Datafield property of the Textboxes. Do this as follows:

Text1
Text2
Text3 		ProductName
UnitsInStock
UnitPrice 		Text4
Text5
Text6 		CompanyName
ContactName
Phone

Now the properties are set and the code is necessary to make it happen. Amazingly very little code is needed in the DBList1_Click event. Copy and paste the following code in the code window and you are all done!      (Tip: Triple click on the word Private.) 
Private Sub DBList1_Click()
    Data2.RecordSource = "SELECT * FROM Products " & _
        "WHERE ProductName = """ & DBList1.Text & """"
    Data2.Refresh
    
    Data3.RecordSource = "SELECT * FROM Suppliers " & _
        "WHERE SupplierID = " & _
        "(SELECT SupplierID FROM Products " & _
        "WHERE ProductName = """ & DBList1.Text & """)"
    Data3.Refresh
End Sub
The different thing in this SQL concatenation is the odd three quotes (""") where in the other demos I used ("'"). This is done because some of the ProductNames have an embedded single quote (apostrophe) and would terminate any attempt to single quote the DBList1.Text string.

What happens with three quotes is that the first would normally end the string, but since it is followed by another, it acts as an escape character allowing the second as a quote character within the literal string. Then the third actually ends the literal. The """)" is similiar with the first and last beginning and ending the literal and the middle two acting to insert a quote character within the string.

This 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. As you see SQL statements are nearly self-documenting.

The SQL statement for Data2 is a simple SQL statement, while the SQL statement for Data3 is a nested or compound statement needed to select items in a different table related by the SupplierID field. When we fill in this RecordSource property in Data2 and Data3, then the Textboxes have all the information they need to fill in the text, and does so when we .Refresh