Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple select list box (2003)

    First, let me warn you. My code capabilities are limited to copying someone else's work and modifying it to work for me. Up until now, that has worked for me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    My database is designed to track our members attendance at our annual House of Delegates meeting. The first step is to enter the biographical information about the attendee. Next, I click on the "Register" button to open a registration form. The registration form already has the member's name entered. I choose the event and begin the registration process. I would like to create two multiselect list boxes for two fields. One is the "Position" field and the other is the "Ribbon" field. At any meeting one member can hold many positions. The same is true for the ribbons we must put on the name badge to identify the different roles.

    I get the fact that I set up the list box with with Simple chosen and that I need to create a subform with a table to store the multiple selections, BUT (and it's a big one!) this is where my understanding of the process ends. I need to know how to set up the subforms so that they are linked to the correct RegistrationID (primary key on the Registration table). I already have a tables set up for Positions (PositionID and Position) and Ribbons (RibbonID and Ribbon). I have been cheating by using 6 different fields (Position1, Position2, etc.) to get around my ignorance of multi-select list boxes, but I think it is time to learn the right way.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple select list box (2003)

    Do these values change at every meeting? Or are they standard assignments?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Multiple select list box (2003)

    You normally don't use multi-select list boxes for this, but continuous subforms.

    You have a many-to-many relationship between the attendees table and the positions table: one attendee can have several positions and several attendees can have the same position. This means that you need an intermediary table to hold all attendee-position combinations. This table will contain the fields AttendeeID (linked to the primary key in the attendees table) and PositionID (linked to the primary key in the positions table). The combination of these two fields will be the primary key in the intermediary table. If you need to store information that is specific to an attendee-position combination, you can add extra fields to the intermediary table.
    Create a continuous form based on the intermediary table, with a combo box bound to the PositionID field. The Row Source of this combo box is the positions table; its Column Count is 2 and Column Widths are 0";1", so that the combo box will store PositionID but display Position.
    Place this form as a subform on the main form that is bound to the attendees table. Access will probably set the Link Master Fields and Link Child Fields properties of the subform automatically, but if not, set them both to AttendeeID manually. This will take care of setting the AttendeeID in the subform, no code is needed.

    The situation for Ribbon is similar.

    You will find a simple example of forms and subforms used to implement a many-to-many relationship attached to <post#=364203>post 364203</post#>.

  4. #4
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple select list box (2003)

    In response to Jeremy's question, the assignments change on an annual basis. The positions, therefore, would be more linked to the RegistrationID than the AttendeeID. Is my assumption correct?

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

    Re: Multiple select list box (2003)

    That's correct, so the intermediary table would contain RegistrationID and PositionID (and the other one RegistrationID and RibbonID).

  6. #6
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple select list box (2003)

    Thanks for the directions. I'm out of time today, but I'll give this a shot tomorrow when I get into work. I'll let you know what happens!

Posting Permissions

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