Multi Selection Combo Box For Data Entry

[Alex E.]

Hi, I am creating a contacts database in ACCESS 2003. Currently, I have 4 tables: Contact Details, Call Log, Event Schedule, Event Attendance.

The problem is with one field in the Contact Details table. On the data entry form, the field (a combo box) is called Contact Type. 1 person (contact) can belong to several contact types. For instance, the person (contact) can be board member, a donor and an expert (in how to assist victims of domestic violence).

The only solution I can think of is an extra table Contact Type plus a Junction Table.
The contact type table would be more or less static (sort of reference). I know how to set up a junction table and link it to the Contacts Details table and a Contact Type table.

But then, how do I get more than 1 selection from the combo box into the junction table ?

Writing VBA code is out of my league.

Any suggestions ?

 

[pbaldy]

A combo box is single select only. You could use a multiselect listbox:

http://www.baldyweb.com/MultiselectAppend.htm

 

[Alex E.]

Hi,
I did a quick Google search for list boxes. It appears, it requires a heavy dosis of VBA programing to make use of list boxes - soming I cannot do (and I do not have the time to spend months learning how to program).

Here is a clipping from a another forum that neatly sums it up:

Q: Is it possible to select multiple items from a list or combo box?
If yes then how?

A: Not from a combo box, but you can from a list box. There's a property on the list box called Multi Select. Change that to Simple. You will obviously need code somewhere to carry out the instructions. Depends on what you want to do.

 

[pbaldy]

Did you look at the link? I provided the code and a sample db.

 

[Alex E.]

Hi,
I did look at the link. It’s all programming code. Which more or less confirms what I discovered with the Google Search. When the List box is set to accept more than one choice, the value is Nil. One has to write an entire program to query those choices and convert the choices into something that can be used in Access … then do some more programming and on it goes.

What I tried to explain in the initial posting is this: one of my data entry fields on the data entry form is part of a many-to-many relationship; i.e. a junction table that joins the Contact Detail table and the (static) Contact Type table. The data entry for this particular field should end up in the junction table; i.e. in the two Foreign Key fields:

Link Table (PK – Link Table ID):
Contact ID (FK)
Contact Type ID (FK)
01
Board Member
01
Donor
01
Expert

I never thought that such a simple data entry task can become a seemingly unsolvable problem for me.

 

[Alex E.]

Sorry, the table I had created disintegrated when positing it. Trying again:

Link Table:
PK = Link Table ID

Column # 1 = Contact ID (FK)
data row 1: 01
data row 2: 01

Column # 2 = Contact Type ID (Fk)
data row 1: Board Member
data row 2: Donor

 

[pbaldy]

I wouldn't call a few lines of code "an entire program", but if you don't wish to use a multiselect listbox and the tiny bit of code it requires, your option is to provide your user with a form/subform where they can add a record in the subform for each type.

 

[Alex E.]

Hi, thank you for your help. I ended up deleting the two tables and putting three repeating fields into the contacts table. Not perfect but, at least that is something I can understand.

No offense meant, but 1 or 100 lines of code is all the same to me - just like a foreign language. I had stated at the very beginning that I do not know VBA code.

 

[pbaldy]

No offense taken. No offense intended, but you could have started to learn how to code, since I gave it all to you. All you had to do was cut, paste and adjust the names to match your own. The path you've chosen (de-normalizing the database) will probably lead to other problems down the line.