Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Inside your mind

    Question Unanswered: Doubley Dynamic 'Select Case'?

    Hi hi,

    This query is quite a bit more complicated. I can think of some ways to achieve the desired results, but I'd really appreciate any insights you peeps might have, as I'm sure there must be a simpler solution than some of the approaches I'm mulling over.

    Soooo, let me try and explain what I'm trying to achieve.

    I have a form with an input box, and a combo box.

    The input box accepts only numerical characters and is used to denote the size of a product, we will call it 'sizeInput'.

    The combo box is used to denote the type of product, these are always 2 character codes, and more (codes, not characters) may be added in the future. We will call it 'typeSelect'.

    The two types of data are linked in that, a size can have many types associated with it, but won't have all of them, and a type likewise, so: 13 MU and 13 VC are perfectly valid products, however, 13 ED is not.

    At the moment I have a 'Select Case' statement setup, which dynamically changes what is available for selection in 'typeSelect', depending on the data input in 'sizeInput'. Here's an example of what it looks like at the moment:

    aSize = Me.sizeInput.text
            Select Case aSize
                Case 9
                    Me.typeSelect.Value = ""
                    Me.typeSelect.RowSource = "MU"
                    Me.typeSelect = Me.typeSelect.Column(0, 0)
                Case 13
                    Me.typeSelect.Value = ""
                    Me.typeSelect.RowSource = "MU, VC"
                    Me.typeSelect = Me.typeSelect.Column(0, 0)
                (etc, etc.)

    Now, because this setup is static, it really needs to be changed, so instead of using '9' and '13' in the example above, it picks out the data from a 'Sizes' field in 'aTable'.

    To make things more complex, it also needs to dynamically find any 'Types' associated with each size , and use all those found as the
    Me.typeSelect.RowSource = DYNAMIC TYPE VALUES

    I'm thinking I need a table setup, with all the possible relations. so something like:

     Sizes | Types
       9   |  MU
      13   |  MU
      13   |  VC
      16   |  SA
      18   |  AC
      18   |  SA
      18   |  TC
    Then create a recordset everytime a change is made to the 'sizeInput' to look for whatever is found in Me.sizeInput.text. Then, when (and if) it finds it, create a string by iterating through each of the 'Types' that relate to that size and add the newly created string to Me.typeSelect.RowSource, so it can populate the 'typesSelect' combo box.

    Is that how you would go about it? If so, I'm a little sketchy on how to achieve that with a 'Select Case' statement, if it's possible at all, or whether to simply use 'If' statements...

    As I said, any advice, opinions, etc. would be most welcome!

    Thanks in advance fella's.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    first off if you know your combinations of types and sizes then you shoudl define those. how you design that is up to you. it could be that you have two tables (types & sizes) and an intersection tables TypeSSizes which defiens valid permutations. however that doesn't feel right to me, so you may need to define a sub table for the types which stores sizes for that specific type.

    as to how you use it
    I'd suggest when you add a new record and or when the type changes you requery the list box that holds the sizes, but return only the row(s) that are valid for the specified type. just as effective is to set a filter on the list box when a value is selected or whatever in the combo box.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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