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

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 DBList named DBList1
6. On Form1 put 3 Textboxes named Text1 - 3.

      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 both 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 Shippers.

Do this just for the first Data Control but not for Data2.

Now connect DBList1 to Data1 and the Textboxes to Data2.

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 them to Data2.

Now we want to display in DBList1 all the CompanyNames of the Shippers. Note that since we have already set the Table to Shippers 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 Shippers.

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

If you run the program at this point you will notice that the listbox contains all the Shippers Company Names but choosing one will not display anything in the Textboxes.
Now we want to display the three fields of the Shippers table in the textboxes. However, since we have not entered a RecordSource in Data2, there is no convenient dropdown list from which to choose the Datafield property in the Textboxes.

The solution is to type in:
ShipperID in the Datafield property of Text1,
CompanyName in the Datafield property of Text2, and
Phone in the Datafield property of Text3.

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 Shippers" & _
                   "WHERE CompanyName = '" & DBList1.Text & "'"
    ISGData2.Refresh
End Sub
There are 2 references to ISGData above. Change these to the name of your data control.
The main point to get is that when a choice is made from DBList1, then the Data2.SQL property (which was left blank) 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. As you see SQL statements are nearly self-documenting.

When we fill in this SQL property in Data2, then the Textboxes have all the information they need to fill in the text, and does so when we .Refresh