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. In Project->Components add the ADO Control
4. On Form1 put 3 ADO Data Controls.
5. On Form1 put a DBList named DBList1
6. 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

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. Do this for all three Data Controls.

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 Products for the first Data Control.

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.

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 three fields from the Products table and in Text4 - 6 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! 
Private Sub DBList1_Click()
    ISGData2.SQL = "SELECT * FROM Products " & _
        "WHERE ProductName = """ & DBList1.Text & """"
    ISGData2.Refresh
    
    ISGData3.SQL = "SELECT * FROM Suppliers " & _
        "WHERE SupplierID = " & _
        "(SELECT SupplierID FROM Products " & _
        "WHERE ProductName = """ & DBList1.Text & """)"
    ISGData3.Refresh
End Sub
There are 4 references to ISGData above. Change these to the name of your data control.
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