Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-Select List Box (Access 2002)

    What's the best method to capture values in a multi-select list box and store them in a table? I'd like to store either the actual value or possibly the index number of the value (preferred).

    Thanks in advance!

    Rich

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

    Re: Multi-Select List Box (Access 2002)

    A multi-select list box is not the ideal tool to collect information to store in a table.

    What do you want to accomplish? Should each selected item be stored in a separate record, or do you want to store them in a field in one record, concatenated?

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Newark, New Jersey, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-Select List Box (Access 2002)

    Yeah... I wondered about that, but thought it would be an easy method for collecting the data.

    Essentially what I'd like to do is have a list of all the software applications we have (SAP, Excel, Access, etc) and have the user select which applications they use for collecting data. I was trying to avoid having a field in the table for each application... but if I have to go that route, so be it.

    Any thoughts of a better way to approach this?

    Thanks!
    Rich

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

    Re: Multi-Select List Box (Access 2002)

    What you describe is a many-to-many relationship between users and applications: one user can use several application, and one application can be used by several users. Unless you have just two or three applications, and you are sure there will never be others, having a field for each application is too rigid. The "standard" approach is to have
    <UL><LI>A users table, say tblUsers with a primary key UserID (and no fields for applications),
    <LI>An applications table, say tblApplications with a primary key ApplicationID, and
    <LI>An intermediary table tblUserApplications in which each record represents a unique combination of user and application. This table will contain fields UserID and ApplicationID (linked to tblUser and tblApplications, respectively); together they form the primary key of this table; beside these, tblUserApplications can also contain information specific to the combination of user and application, for instance frequency of use or proficiency.[/list]The easiest way to present this to the user is to put a continuous subform on the Users form. The subform is based on tblUserApplications (or on a query based on tblUserApplications) and is linked to the main form by UserID. Use a combo box for ApplicationID on this subform, so that the user can select applications from a list (based on tblApplications)

Posting Permissions

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