Using multiple tables
Our ProjectMgt application contains an Employee table and a Department table linked through the employee's department number.
Now, if we create a form for Employee maintenance using the same technique we used in the previous lesson, we can access all fields in the Employee table using one data control, Data1
. This is what the basic form will look like, before we get to put in the usual improvements:
But suppose I want to select the employee's department from a list rather than keying in the department number.
For this we need a new control - it's called the DBList control
. It's not in the standard toolbox, we have to get it.
For that: go to the menu bar -->Project -->Components -->Microsoft Data Bound List Controls 6.0 and put a check in the box then click OK.
Once you've done that two new controls appear in the toolbox.
Now to get the department names to appear in the list.
That means I will have to access the Department table, in addition to the Employee table.
Remember: you need one data control for every table you want to access.
So first, create a second data control, Data2
, on the form. It doesn't matter where we put it, we're going to make it invisible anyway.
Data2 has to have the same Connect property and the same DatabaseName as Data1 but, the RecordSource must specify: Department
Now to get the list right.
First, we delete the department_number TextBox. Then we add a DBList.
Now we specify the DBList1 properties. Careful! This is where most people hit a snag!
The Data properties:
these specify where the data entered will be stored. We are in the Employee table. That's Data1. So, the data entered will go into DataSource: Data1
and the field into which it is going is DataField: e-Dept
The List properties:
these tell the control where to get the information to show in the list. Since we want it from the Department table, we specify RowSource: Data2
. What will appear in the list is the Department name so we choose ListField: d_Name
. Finally, there has to be a link between Data2 and Data1. That is always the field which is the primary key
in the list table and that is the BoundColumn: d_Number
And once everything is cleaned-up, the Data2 control is hidden, we get the final result:
If you haven't found the Visual Basic resource you're looking for,
use our Google Search box for more information.