Thread: Doubley Dynamic 'Select Case'?
07-13-11, 11:48 #1Registered User
- Join Date
- Jun 2011
- Inside your mind
Unanswered: Doubley Dynamic 'Select Case'?
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 theCode:
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
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.
07-13-11, 14:00 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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