Combo boxes - selecting multiple values

[Bob]

Using Access 2002

I'm new to Access and I'd like to create a table with fields that let users
select multiple values from a drop-down list (presumably by holding down the
Control or Shift keys) that display in the table separated by commas
(semi-colons also acceptable). I'm able to use the Lookup Wizard to
successfully create combo boxes of the Value List type and Table/Query type,
but I can't figure out how to select multiple values (assuming this is
possible). Help is appreciated. Thanks.

Bob

 

[MacDermott]

You can't really effectively select multiple values from a combobox -
the "box" part of the combo is made to display a single selection from
the list.
If you need to collect multiple values, you might want to consider a
listbox, with its multi-select property set to Simple or Extended, depending
on the behavior you want.

I'm concerned about your wanting to display the data in a table (field)
separated by commas or semicolons. This is a violation of the normalization
which makes Access so powerful. Please consider storing them in a separate
table, with a foreign key to identify the record in the main table to which
they refer.

HTH
- Turtle

 

[Bob]

Turtle,
Thanks for your reply. I'm creating a relatively small IT staffing database
where each record represents a consultant. An important part of the
consultant information will be the technologies the consultant is
experienced in. The idea is to be able to search the database for
consultants with one or more desired technologies. My original thinking was
that there would be a Technologies field where multiple technologies
separated by commas would be typed in a table view, and this field would be
keyword searched for desired technologies. Then I realized if the input
technology names were mistyped, they wouldn't turn up in keyword searches
that use the correct spelling. This gave me the idea to use a combo text box
where multiple items could be selected, and if there was a technology that
didn't appear in the list it could be manually typed in the combo box. Based
on your post, it doesn't look like this is an option. I'm currently creating
a form for data input. Following you suggestion, I'm thinking about using a
List box with its multi-select property set to Extended on the input form.
The question I have is how to accommodate input of technologies that aren't
anticipated in the list box. I'm thinking I'll need to create a separate
field for these. I'm pretty much figuring this stuff out as I go along. I
haven't worked with Access much. My original strategy was to keyword search
a single field, but I suppose it won't be a problem to search more than one
field. Any additional suggestions based on my description of what I'm trying
to do are welcome. Thanks again for suggesting the list box and multi-select
property.

Bob

 

[MacDermott]

My vote would be against a separate field; it will make searching
considerably more complex.
Here's my suggestion:
Create a table tblTechnologies, with a single field, TechnologyName.
Build a listbox lbTechnology, with tblTechnology for its RowSource.
Now add a command button to your form. Make its caption something like
"Add Technology".
Put code like this in its OnClick event procedure:
CurrentDB.Execute "INSERT INTO tblTechnologies (TechnologyName)
VALUES " & _
"(" & chr(34) & InputBox("New Technology Name") & chr(34) & ")"
lbTechnology.Requery

This should add the new technology to your listbox.

HTH
- Turtle

 

[Bob]

Turtle,
Thanks again for your continued help. I was concerned that putting names of
technologies in more than one field might complicate searching. Your
solution sounds like a good one - and not too complicated for a novice like
myself. I plan to head in this direction. Much obliged.

Bob