Listbox Choice Displays Table Data (DAO)

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

1. Start a new standard exe.
2. In Project->Components add Microsoft DataBound List Controls
3. On Form1 put two Data Controls named Data1 and Data2.
4. On Form1 put a DBList named DBList1
5. 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

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 Shippers. Do NOT do this 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 RecordSource 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 Text2, and "Phone" in 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()
    Data2.RecordSource = "SELECT * FROM Shippers " & _
            "WHERE CompanyName = '" & DBList1.Text & "'"

    Data2.Refresh
End Sub

Note that two of 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 DBList1, then the Data2.RecordSource 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 RecordSource property in Data2, then the Textboxes have all the information they need to fill in the text, and does so when we .Refresh

Pick a Name in a Listboxto Display Table Data in Textboxes

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