Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Unanswered: Multi Select in List Boxs

    Hello,

    I'm trying to create two forms which populate two tables. Each form has its own datasource table. Now, the problem I have run into lately has been connecting list boxs in my form to my table. I have already created list box controls and entered the values. I've selected Multi-Select - Simple so that users can choose multiple values in the list box. However, when I look at the datasheet view, I find that the fields connected to the list box are either blank or cannot enter more than one value in them. Is there any way that I can make selections in a list box show up in the field in the table and look something like this: "ValueA; Value B; Value C"?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    That's not really how Listboxes work; that is to say, you can't just connect a multi-select listbox to a field in a table. I'd recommend you go through this tutorial, it explains things quite well IMO.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    17
    Thank you, that's really helpful.

  4. #4
    Join Date
    Feb 2009
    Posts
    17
    So I looked at the guide and this seems to be what I want to do:

    I might use a list box in multi select mode to allow a user to make more than one choice for multiple data entry, but I can't think of an example that wouldn't represent poor database design (would the choices all go in the same field... or into different fields... how would it decide?).
    How can I make this work?

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    In that case I usually create another table with a join relationship to the original table; but that usually requires a fair amount of coding (and I'm not sure what level you're at) and usually you start getting into the world of subforms/subreports as well. It's up to you as the database admin what you want to do/how you want to proceed.
    Me.Geek = True

  6. #6
    Join Date
    Feb 2009
    Posts
    17
    Sorry for being unclear.

    My purpose: have a dropdown or list box with multiple options. Users will be able to select one or more options which will populate in the datasheet view. The populated field will then look something like this: "Option A; Option B"

    Is this possible? Or do I need to make individual check boxes and populate multiple fields to make it work? Is there any way to have multiple values in populated in one field?

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Well, you can certainly concatenate the values of the listbox into a textbox by cycling through the ItemsSelected collection of the listbox. Give that a shot and see how you get along. And if you need help, just post back and we'll see what we can do.
    Me.Geek = True

  8. #8
    Join Date
    Feb 2009
    Posts
    17
    Hmm, no that won't work since it only prints but doesn't populate.

    What if I wanted to use one control to populate multiple fields? Is that possible? Then each option in the control would fill in a different field?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "multiple fields" is possibly a design error.
    how many fields?
    ...and what will you do when you have one more than that?

    probably you need a separate 1:many table to hold this variable number of fields. you can easily populate this table from your multiselect and easily join it to the table where you were originally planning to have multiple fields.

    a few posts back you did say The populated field will then look something like this: "Option A; Option B" so you have moved the goalposts, but that's OK.

    in the same post you said will populate in the datasheet view and the missing item there is the WHAT that will do the populating. conventionally WHAT would be a query and whilst you could rig some profoundly ugly SQL to pick up the selected items in the list and feed the datasheet it would be an unfortunate choice.

    redesign the tables so you can handle any number of Option X using a 1:many table (primary key from main table plus item (or primary key of item) in the list.

    code up an INSERT that saves the PK plus the item for each selected item into your new 1:many table

    build a query JOINing the 1:many to the main table and use that query to feed your datasheet.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    It certainly is possible, but as far as I can see, it's still going to require you to loop through the selected items collection and update your fields accordingly.
    Me.Geek = True

  11. #11
    Join Date
    Feb 2009
    Posts
    17
    Quote Originally Posted by izyrider
    "multiple fields" is possibly a design error.
    how many fields?
    ...and what will you do when you have one more than that?
    I want a list box control with multiselect that is functional and populates the table attached to the form. I want users to be able to select more than one option in the list box the have the table show it.

    I understand that populating one field with multiple selections is not only disadvantageous since I can't quantify properly and run reports properly but it also is very difficult.


    Quote Originally Posted by izyrider
    probably you need a separate 1:many table to hold this variable number of fields. you can easily populate this table from your multiselect and easily join it to the table where you were originally planning to have multiple fields.
    This is correct. How do I join the new table (with multiselect options) to the old one?

    Quote Originally Posted by izyrider
    a few posts back you did say The populated field will then look something like this: "Option A; Option B" so you have moved the goalposts, but that's OK.

    in the same post you said will populate in the datasheet view and the missing item there is the WHAT that will do the populating. conventionally WHAT would be a query and whilst you could rig some profoundly ugly SQL to pick up the selected items in the list and feed the datasheet it would be an unfortunate choice.
    I don't mind redesigning the table. How will running the query populate the table?

    Quote Originally Posted by izyrider
    redesign the tables so you can handle any number of Option X using a 1:many table (primary key from main table plus item (or primary key of item) in the list.
    What should I assign the primary key? Does it matter which option?

    Quote Originally Posted by izyrider
    code up an INSERT that saves the PK plus the item for each selected item into your new 1:many table
    I do not know how or where to create the insert code.

    Quote Originally Posted by izyrider
    build a query JOINing the 1:many to the main table and use that query to feed your datasheet.
    how do I make the query join the tables?

    Thank you for your response.

Posting Permissions

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