Multiple columns in a combo box

[Guest]

How do you display multiple columns in a combo box and use only one of the
columns as the control source for a field in a table?

 

[Steve Schapel]

Walt,

In the design view of your form, select the combobox, and look at its
Properties. What you are seeking is controlled by a combination of
these properties:
Row Source
Control Source
Column Count
Bound Column
Other properties that you will probably want to attend to are...
Column Widths
List Width

So, for example, if your drop-down list of items will be based on a
query MyQuery that has 3 fields, and you want the selection to relate to
the MyField field in the form's record source table, based on the first
column of MyQuery, but you want all columns of the query to show in the
drop-down list of items. the properties would be like this (for example)...

Row Source: MyQuery
Control Source: MyField
Column Count: 3
Bound Column: 1
Column Widths: 1.2;3;2.5 (or whatever widths are appropriate)
List Width: 6.9

This assumes you mean the multiple columns to show in the drop-down list
of items. If you mean the multiple columns to show in the combobox
control itself... well, that's a different question.

 

[Guest]

Steve:

I did not make myself clear, so here goes. I have a form called "Committee
Update Form", which is based on three tables - "Membership Table", "Member
Committee Table" and "NWC Committees".

Problem 1: There is a Combo Box named "Member to Find". When the drop-down
arrow is lcicked three columns appear - MemberID, FirstName, LastName.
However after the selectin of a Member is made and the drop-down list closes
only the Member ID is visible. How do you cause all three columns to be
visible after the drop-down closes?

Presently the properties are:
Control Source for the Combo Box is MemberID in the Member Committee Table
Row Source Type is Table/Query
Row Source: [Membership Table].MemberID, [Membership Table].FirstName,
[Membership Table].LastNme ORDER BY LastName;
Column Count: 3
Column Widths: .375", 1.25", 1.25"
Bound Column: 1
List Width: 1.9688"

Problem 2:

There is a second Combo Box named "Committees" which is supposed to return
the committee name to the table "Member Committee Table" The properties are:
Control Source: "Committee" to the Member Committee Table
Row Source Type: Table/Query
RowSource: [NWC Committees].CommID, [NWC Committees].Committee FROM [NWC
Committees];

How do I get the Combo Box to return the committee name and not the CommID
to the table?

 

[Steve Schapel]

Walt,

Problem 1:
... How do you cause all three columns to be
visible after the drop-down closes?

You can't. It is not in the nature of comboboxes. You can put a couple
of unbound textboxes on the form, though, and set their Control Source
properties to...
=[Member To Find],[Column](1)
=[Member To Find],[Column](2)

Problem 2:
How do I get the Combo Box to return the committee name and not the CommID
to the table?

Set the Bound Column property to 2.

 

[Guest]

Steve:

Thanks, Problem 1 is solved. However I still have a problem with 2.

There are two combo boxes. The one I referred to earlier "Committees" and a
second one "Subcommittees". I have the properties of Subcommittees set so
that it displays only the subcommittees of the committee displayed in
"Committees".
Row Source Type: Table/Query
Row Source: SELECT [Sub-committee Table].SubcommID, [Sub-committee
Table].Subcommittee, [Sub-committee Table].CommID FROM [Sub-committee Table]
WHERE ((([Sub-committee Table].CommID)=Forms![Committee Update
Form]!Committees)) ORDER BY [Sub-committee Table].Subcommittee;

When I change the Bound Column of the Committees Combo Box to 2 the combo
box displays the CommID and not the Committee name.

What am I doing wrong?

Walt

Walt,

Problem 1:
... How do you cause all three columns to be
visible after the drop-down closes?

You can't. It is not in the nature of comboboxes. You can put a couple
of unbound textboxes on the form, though, and set their Control Source
properties to...
=[Member To Find],[Column](1)
=[Member To Find],[Column](2)

Problem 2:
How do I get the Combo Box to return the committee name and not the CommID
to the table?

Set the Bound Column property to 2.

 

[Steve Schapel]

Walt,

Your Member Committee Table table has a field for Committee which your
Committeess combobox is bound to. You say this field is supposed to
contain the name of the committee. And then, the second combobox is
supposed to refer to the value of the first combobox in its criteria,
but you want the criteria to apply to the sub-committee's CommID field.
So you are trying to use the committee name as the criteria for the
CommID field, which won't work.

I guess the way this would normally be done is that the Committee field
in the Committee Table table would be the ID, not the committee name,
and the Committees combobox would return the CommID value, and then the
row source of the second combobox would be correct as you currently have it.