Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: How to retrieve a specific value from a multiple value listbox.

    I have a List Box (lstbox1) with a with the following options “Option 1”; “Option 2”; “Option 3” where the user can check multiple selections. When the value is saved in the table, it saves the data in a comma separated fashion. What I want to do is force validation for another field if they selected “Option 2”.

    For example if they selected all three options the value stored in a table would look like this “Option 1, Option 2, Option 3”. How can I determine if they selected option 2 on an After Update event?

    Any advice would be appreciated. ~R~

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    bad design don't do it.

    however if you must persist:-
    make the control unbound
    set the values as required in the forms on current event
    set the default values in the add new event
    in the forms before update event decode the values from the list box and set to the underlying table value

    I'd suggest you store the data as a bit field

    ie no option = 0
    option 1 = 1
    option 2 = 2
    option 3 = 4
    option 4 = 8
    so
    Option 1+ 2 = 3
    option 2 + 4 = 10
    Option 1+ 2 + 3 + 4 = 15
    use the bitwise comparators in a query
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    4
    Much thanks for the response. I considered doing just this, the only problem is the real value list is extremely large. Were talking about 30+ options they can select multiples from. That’s a pretty large number of combinations to calculate all of the different combinations they could select and there is more than one option I need to see if they selected. The example I put up was just a scaled down version to address the concept. ~R~

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so redesign it
    push the options into a sub table
    even contemplating storing the values in as a CSV is in my books madness, and with potentially 30 different and complementary options absolutely raving


    storing values as a CSV means the data isn't (easily) searchable. well thats OK if you know your app is never ever going to to need to search on those options.
    however you are building in by design a significant limitation on how the users access their data.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    4
    I can tally the results easy enough in a query which is the only need the customer has. Pushing the values into another table still doesn’t resolve the issue on how to see if a specific option was selected on that record and force them to enter data into another field on the form itself. I was thinking if there was a way to somehow search the strong to see if a particular block of text was present, then move onto the validation code. I may end up going back and making individual radio buttons instead.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so store the data relevant to that option in the sub table

    radio buttons would be the wrong design stylistic in my view. A radio button is a mutually exclusive choice (ie its one of n choices) so use the option button if you must go down the route you are thinking
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why is it a bad design
    well what happens if the user decides they need 32 options... requires a re design of the application, which is increased time and cost, increased complexity and more time and more chance of going wrong

    ok so the customer is going to tell you 'we will never exceed 30 options', guess what customers lie.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2013
    Posts
    4
    I meant check boxes. Either way the key thing I have to have in this is that the reports will tabulate things correctly. I am going to play around with the separate table populating an unbound box. I found an example of one that seems to work fairly close to what I’m looking to do. Much thanks again!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have a separate table, and embed that as a sub form to the main data form then most of your design issues go away. no unbound controls. extensible

    you could embed some logic that defined whether there should be additional information for a specific option type and if so what type of data it should be. if you are smart enough on the design you could extend that even further to some form of rules tabels that specifies min/max, defautl values and so on.

    you cna alwasy compress the data back into a CSV for reporting if required
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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