Need Help with Combo Boxes

[rich]

Hello Everyone,

I am having a problem using combo boxes and queries. Here are the details:

LkTbl_Company: 2nd column has two possible values (Company 1 or 2)
CompanyID AutoNumber Primary Key
CompanyName Text

LkTbl_Role_Master: This table describes various roles for each company.
RoleID AutoNumber Primary Key
CompanyName Text
RoleName Text

Currently, there is no relationship between the two tables. Not sure if I
need to re-link the tables or not. Not successful when the link was
previously there.

On my form, I have two combo boxes. The first, cboCompany successfully
allows the user to select either of the two possible companies.

The second combo box, (cboEmployeeRole) (this is the problem box) should
display the various roles that apply to either company. After the company is
selected in the first combo box, the second box should display the results of
the following query:

Select Lktbl_Role_Master.CompanyName from Lktbl_Role_Master
Where Lktbl_Role_Master.CompanyName = Form.cboEmployeeRole.

If I execute the query directly, I am prompted for the company and the
correct results are displayed. However, when executed from within the form,
nothing appears in the combobox. It appears that the query runs and the
values are retrieved, just not displayed. If you click the dropdown, it
appears that the box expands with enough space for the results. Company 1
has about 8 values to return, company 2 has 2 values to return. The dropdown
expands to accomodate the number of values.

I'd appreciate any help.


Thanks in advance,
Rich

 

[scubadiver]

You basically want a cascading combo.

The second combo should have a query where the company field has a criteria
equal to the first combo

[forms]![form name]![fieldname1]

after update event in the first combo

fieldname2.requery

 

[Ken Sheridan]

Rich:

You should relate the tables on the CompanyID column rather than CompanyName
column, i.e. you should have a CompanyID rather than CompanyName foreign key
column in the LkTbl_Role_Master table of the same data type (long integer
number, but not an autonumber in the case of the foreign key) as the primary
key of the LkTbl_Company table.

The cboEmployeeRole control should be bound to a RoleID column in the form's
underlying table and its RowSource property for the box should query the
LkTbl_Role_Master table, restricting the results by a reference to the
cboCompany combo box, which is bound to a CompanyID column in the form's
underlying table, like so:

SELECT RoleID, RoleName FROM LkTbl_Role_Master WHERE CompanyID =
Form!cboCompany ORDER BY RoleName;

Note that as both controls are on the same form you can use the Form
property rather than a fully qualified reference to the form. Other
properties of the combo box should be:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, RoleID, column, so only the name
shows.

You need to requery the cboEmployeeRole combo box in both the Current event
procedure of the form and in the AfterUpdate event procedure of the
cboCompany combo box with:

Me.cboEmployeeRole.Requery

I'd add a further comment, however:

By having columns in the form's underlying table for both the CompanyID and
RoleID you are in fact introducing some redundancy as RoleID determines
CompanyID. This leaves the table at risk of inconsistent data as there is
nothing to prevent a role inappropriate to the company in question being
entered into the table by other means than the form. The CompanyID column is
therefore redundant. Its worth saying that this is not an uncommon fault,
but if you want to normalize the table you can still use a form in the same
way by using an unbound combo box for the Company. You'll find a demo which
illustrates various ways of using correlated combo boxes like this while
maintaining a correctly normalized structure at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo deals with a slightly more complex structure as it handles three
levels of data rather than two, the local administrative areas in my
location, but the principle is the same.

Note that correlated combo boxes like this, where numeric surrogate keys are
used, only work well in single form view. You'll find ways of handling this
type of data in continuous forms in the demo, however.

Ken Sheridan
Stafford, England