Record specific lookups

[Gerdagan]

Hi all,

Firslty apologies for posting this if it has already been posted on the forum, but I really couldn't find anything in search that was what I wanted.

Well here is my query.

I have a list of items of clothing in a table. I use a form to enter data into a number of tables (visible in one master form), the source for which is a query.

So for example (basic), my new record form is set up like this:

Item
User assigned
Size

What I want to do is to have a drow down box (lookup) for the Size field in my new record form, that will only show the available sizes specific to the item of clothing (i.e. t-shirt sizes S, M and L, and shoes sizes 7, 8, 9...etc) previously selected in the item field.

I've been racking my brain trying to figure out how is best to do this, and to be honest my head is just mashed. So I wondered if someone could help me.

I hope I made sense.

Thanks in advance guys.

 

[pbaldy]

Presuming the table structure supports it, sounds like cascading combos:

http://www.baldyweb.com/CascadingCombo.htm

 

[Gerdagan]

Hey pbaldy,

Thanks for the post. It looks like I am on the wright track with this now. I've done some more research and found the cascade method best for me, and that is to source the row source of the changeable combo list from a different table. However, I am only getting one lot of options when I select an item, for some reason the coding isn't working and will only provide the options for the first case, but gives this option for all items even if they are not identified in the first case.

My code is below...I hope someone can help. I am learning on my feet here so my knowledge isn't great.

Private Sub Description_AfterUpdate()
On Error Resume Next
Select Case Description.Value
Case "Boots (Dielectric wellies)" Or "Boots (hiker)" Or "Boots (wellies)" Or "Safety boots" Or "Safety boots (Rigger)" Or "Thermal socks" Or "Glove gauntlets (HV)" Or "Glove gauntlets (LV)" Or "Gloves (HV operational)" Or "Gloves (LV operational)"
Size.RowSource = "T-2-5-1_PPE_list_size_shoe"
Case "Balaclava" Or "Ear defenders (helmet)" Or "Ear defenders (standard)" Or "Glove bag" Or "Hat (winter)" Or "Holdall" Or "Safety helmet" Or "Safety spectables (clear no tint)" Or "Safety spectacles (tinted)" Or "Visor (face)" Or "Visor (helmet)" Or "Visor attachment (helmet)"
Size.RowSource = "T-2-5-2_PPE_list_size_standard"
Case "Coat (Gortex)" Or "Coat (Gortex)" Or "Coat (winter heavy)" Or "Fleece (arc resistent)" Or "Fleece (standard)" Or "High visibility vest" Or "High visibility vest (long sleeve)" Or "High visibility vest (orange)" Or "Polo shirt" Or "Sweat shirt" Or "T-shirt" Or "Gloves (handling)" Or "Gloves (hide non lined)" Or "Gloves (linesman)" Or "Gloves (yellow with grip)"
Size.RowSource = "T-2-5-3_PPE_list_size_clothing"
Case "Overalls"
Size.RowSource = "T-2-5-4_PPE_list_size_overalls"
Case "Trousers" Or "Trousers (combat style)" Or "Trousers (combat style)"
Size.RowSource = "T-2-5-5_PPE_list_size_trousers"
End Select
End Sub

 

[missinglinq]

Your syntax is incorrect for your Cases.

Case "Boots (Dielectric wellies)" Or "Boots (hiker)"...

should be

Case "Boots (Dielectric wellies)", "Boots (hiker)"

with Commas between the expressions (values) not the Or operator.

Linq ;0)>

 

[Gerdagan]

Thanks missinglinq,

I did try commas but I did " , " instead of ", ".

Amazing what that little space can do!

Thank you.