Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Mackay, Queensland, Australia
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Multiple selection (Access 2002)

    I have a main contacts table that has a lookup to a separate table containing a list of items. I created a combo box on my main form that provides a drop down list to choose one of the items. That was all fine but now I need to be able to select more than one item from this table. I have changed the combo to a list box and can see that I probably need Multi Select from the properties, but I don't know which would be best ... Simple or Extended. Also I don't know how to keep a separate value in each record as it applies my selections to every record. I'm assuming I have to write some sort of event procedure to one of the options under the Event tab, but I'm not sure which one would be best and have no idea what to put in the procedure. Any help would be absolutely thrilling.
    Thanks heaps in advance.
    Suzanna

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection (Access 2002)

    Hi Suzanna,

    If you need multiple items per contact, you must change your data design. You now have a many-to-many relationship between contacts and items: one contact can have several items, and one item can be associated with several contacts. To implement this, you need an intermediate table in which each contact-item combination is a separate record. On the form you could display this in a multi-select list box, but a subform is another possibility.

    Take a look at the very simple demo attached to <post#=364,203>post 364,203</post#>. In the demo it's students and classes, in your database it would be contacts and items.

  3. #3
    Lounger
    Join Date
    Mar 2004
    Location
    Mackay, Queensland, Australia
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Multiple selection (Access 2002)

    Many thanks Hans. I had thought I might be able to achieve it with a subform but figured it might be easier for the multiple selection, but your example is clear and easy to follow. However, one more question. Do I really need the intermediary form and a many to many relationship? In my database one contact could be involved in many items, but I don't really need to know which contacts are involved in a particular item ... (although that might be interesting info to have). But it would normally be "one contact - many items" but not "one item - many contacts".
    Cheers
    Suzanna

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection (Access 2002)

    If one item can *never* belong to more than one contact, you don't need the intermediary table. You'd just need a field identifying the contact in the items table.
    But if one item might occasionally belong to more than one contact, you do need the intermediary table, even if it is rare, and you don't need to know which contacts are involved in an item.

  5. #5
    Lounger
    Join Date
    Mar 2004
    Location
    Mackay, Queensland, Australia
    Posts
    26
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Multiple selection (Access 2002)

    Thanks again Hans. It's all working now. :-)
    Cheers . . . Suzanna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •