We help IT Professionals succeed at work.

Filter lookup using subdatasheet???

hbaber asked
Medium Priority
Last Modified: 2010-08-05
In Access I have the following tables: Items, ItemsStyle, ItemsSize, and Sizes.  
You can have 1 Item with multiple ItemStyles (Infant, Toddler, Youth, Woman, Man) and each ItemStyle can have multiple ItemSizes (Small, Medium, Large, XL,....).  I have this setup so I can see subdatasheets by clicking the + on an item and see the itemstyles and click the + to see the itemsizes. When I click the first record I see
-1,item 1
    *(dropdown list I want to filter based on the the style I clicked 'infant')
  +Toddler  (if I click + to expand toddler I only want to see sizes for toddler in the dropdown lookup and not see infant or youth sizes)

Can I do this?
Watch Question

could we pls have a little detail on the structure of tables:

ItemsStyle and ItemsSize

The + structure will work in tableview and can be set following the instructions when you press the +
The dropdownlist you can achieve by changing the fieldproperty in access to a lookup field (See secon tab in field definition) just make the field to be looked up in a table and your combo will appear.




Take a look at http://www.knology.net/~hb/accesshelp.jpg to see the DB structure and what I am trying to do.

I have the lookup working it just doesn't filter dynamically.  I would guess I need to create a view and use VBA to get the value of what was clicked in order to pass it.  I don't think I can do this with a simple lookup from inside a table/subdatasheet.

Hmm, you want something we normally code as cascading comboboxes.
This is however possible when directed from code. I'm afraid you can't include such a criterium here when working with this linking feature.
Can you switch to using forms ?



No problem, I can switch to forms.

Hmm, looking again to the structure made me wonder or you have multiple ItemStyleID's for the same CollectionID.

Looks to me your SizeID field should be splitted in two fields as it looks to hold the Collection and a "lower" level.
Having the SizeID splitted will allow the original design when you relate the tables by CollectionID....



Here is the database.  http://www.knology.net/~hb/slt.mdb  The SizeID joins to the Size table which has two fields for size and collection.

Hmm, just curious why you make this so "complex".
Looks to me you have items and styles/collections.
The ItemStyle table defines what combinations are possible.
And in the ItemSizes the different sizes can be recorded, but why there's a style/collection field isn't clear to me.
When you want to limit the number of sizes, this can already be done using the ItemSizes that are styles/collections related.



I thought it would make it cleaner and easier to query later by normalizing wha tI could.  My thought was I have an item (shirt) which can be sold in styles (Infants, Youth, Women, Men ....) those styles come in different sizes for each style.  That is why I have 3 tables.  If this is not correct I/we can change it.  No problem.  I want to try to avoid keying/typo errors.

If you want to modify and post something for me to download I will take a look.  
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.