Solved

Filter lookup using subdatasheet???

Posted on 2004-03-23
10
705 Views
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
  -Infant
    6/9
    12
    18
    *(dropdown list I want to filter based on the the style I clicked 'infant')
  +Youth
  +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?
0
Comment
Question by:hbaber
  • 5
  • 4
10 Comments
 

Expert Comment

by:robiago
ID: 10664278
could we pls have a little detail on the structure of tables:

ItemsStyle and ItemsSize
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10665066
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.

Nic;o)
 
0
 
LVL 1

Author Comment

by:hbaber
ID: 10668750

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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10668845
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 ?

Nic;o)
0
 
LVL 1

Author Comment

by:hbaber
ID: 10668856
No problem, I can switch to forms.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 54

Expert Comment

by:nico5038
ID: 10668947
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....

Nic;o)
0
 
LVL 1

Author Comment

by:hbaber
ID: 10669266
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.

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10669526
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.

Nic;o)
0
 
LVL 1

Author Comment

by:hbaber
ID: 10670195
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.  
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 10670364
Hmm sounds reasonable to limit the sizes per style/collection, but then I would create a Style/Size relation table.
Now you're creating multiple rows with the same size just with a different "collection".

The size in the itemsize can be taken "straight" from the size table and the Style/Size can be used to "guard" that only "allowed" sizes can be selected.

See my point ?

Nic;o)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Library not Registered 16 50
data analyst 3 50
MS Access 03, TransferText, decimal places 8 47
append to an ms access field 6 26
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now