Cascading Combo Boxes and perhaps a stupid question

[Lol999]

Hi, I'm working on a form and have several combo boxes on it for choosing which data to enter. I am trying to alter the rowsource for a combobox using vba in the AfterUpdate event of the first combobox.
Clear as mud so far?
This is the code I am using and nothing is happening. I have tried using the .Value method for the combobox but nothing worked there either.
The data in the rowsource for Cbo_PPE_Type is in the form of a list, so I don't know if that has repercussions.
Also, and this is perhaps a daft one, do I set the RowSource for combobox Cbo_Item to nothing as its default value as it will be altered, or not in this case, by the code I am trying to implement.
Cheers, Lol

Private Sub Cbo_PPE_Type_AfterUpdate()
Dim strSQL As String
Select Case Me.Cbo_PPE_Type.Text
            Case "Clothing"
            strSQL = "Select Clothing_Description From Tbl_Clothing;"
            Case "Helmet"
            Me.Cbo_Item.Visible = False
            Case "Mask"
            strSQL = "Select Mask_Type from Tbl_Mask;"
            Me.Cbo_Item.RowSource = strSQL
            Me.Cbo_Item.Requery
            
End Select
            
      
End Sub

 

[Lol999]

I hear what you say. I don't like having users select from a non-descriptive ID number, I prefer where possible to allow them to select from actual meaningful data i.e. fleece, shirt,socks etc

I considered putting the descriptions in a table of their own instead of inputting them in a list for the combobox.
I have added a bit more code to change the rowsource of another combobox, but nothing is showing.

Code is as below:

Private Sub Cbo_PPE_Type_AfterUpdate()

Dim strSQL As String
Dim strSQL2 As String

            Select Case Me.Cbo_PPE_Type.Value

                          Case "Clothing"
                          strSQL = "Select Clothing_Description From Tbl_Clothing;"
                          
                                Case "Helmet"
                                 Me.Cbo_Item.Visible = False
                                strSQL2 = "Select Helmet_Colour From Tbl_Helmet;"
                                
                            Case "Mask"
                            strSQL = "Select Mask_Type from Tbl_Mask;"
                            strSQL2 = "Select Mask_Size from Tbl_Mask;"
            
            End Select
            
            Me.Cbo_Item.RowSource = strSQL
            Me.Cbo_Item.Requery
            
            Me.Cbo_Size.RowSource = strSQL2
            Me.Cbo_Size.Requery
End Sub

Cheers, Lol

 

[Cronk]

Tony,
Re your last 2 posts, if the row source of the combo is say

select ItemID, Item from tblItems

with the first column being the bound column and hidden, then
Me.Cbo_PPE_Type.Value will give the value of ItemID
whereas Me.Cbo_PPE_Type.Value.text gives the displayed value.

Yes, the best practice way is including the index in the combo row source with the code being
select case me.Me.Cbo_PPE_Type
case 1
'Clothing
etc

 

[Lol999]

Now that explains a lot to me about how combo boxes work.
Thanks!

 

[Lol999]

Okay, having done much reading on the internet I'm going to abandon this line of approach.
I need to get some expert help on normalising data for this one so I 'll post where appropriate.

Thanks for help so far, Lol.

 

[The_Doc_Man]

Then let me give you encouragement. The ability to recognize the need to normalize better is a good and proper step towards better designs. Good luck and if you have questions, you know where we are.