Multiple Selection from a Combo Box

[duncanjx]

Hi there

I have a Customer table with the normal name, address and other
related information.
I want to allocate a staff member to the customer. I have set a LookUp
setting for STAFF Field as a field to select from table STAFF Details.
I use Staff Name as the primary Field as well.

What I want to do is to include Staff Location and Phone Number that
are linked to the Staff Field.
At the moment all staff details are in one table.

Ideally, from selecting from a drop down menu for the staff name I
want to bring up the other staff details without creating other STAFF
Location Field or STAFF Phone by a drop down.

I have set up a subform but do not like the look of it.
I want one selection from a combo box to populate all the information
about the staff member.

Any thoughts would be appreciated

Duncan

 

[Allen Browne]

It would not be a good idea to add fields to the Customer table to store
your staff member's contact details there as well as in your staff table.
You can display this information on your form without storing it in the
Customer table.

On your form, use a combo box for selecting the staff person to assign to
the customer. The RowSource property for the combo will be a query like
this:
SELECT [StaffID], [Surname] & ", " + [FirstName] AS FullName,
[Location], [Phone]
FROM Staff
ORDER BY [Surname], [FirstName];
The other combo properties will be something like this:
Column Count 4
Column Widths 0;2";0;0

The combo now has the staff member's location and phone number hidden in the
3rd and 4th columns. You can display the location in a text box with Control
Source of:
=[StaffID].Column(2)
where StaffID is the name of the combo.
(Note that the first column is zero, so Column(2) is the 3rd one.)

 

[Marshall Barton]

I have a Customer table with the normal name, address and other
related information.
I want to allocate a staff member to the customer. I have set a LookUp
setting for STAFF Field as a field to select from table STAFF Details.
I use Staff Name as the primary Field as well.

What I want to do is to include Staff Location and Phone Number that
are linked to the Staff Field.
At the moment all staff details are in one table.

Ideally, from selecting from a drop down menu for the staff name I
want to bring up the other staff details without creating other STAFF
Location Field or STAFF Phone by a drop down.

I have set up a subform but do not like the look of it.
I want one selection from a combo box to populate all the information
about the staff member.

If you include those field in the combo box's row source
query, then you can have text boxes display those fields
from the selected entry by using expressions like:

=combobox.Column(n)

where n is the 0 based number of the field in the query.