I have a Datasheet view that contains 2 combo boxes: Category and Type. When the user selects a Category, they should get different options for Type.
Meaning, there is a preset list of Types, which are bound to different Categories. So, if they select Category 1, they should only see Types A, B and C. If they select Category 2, they see Types X, Y and Z.
My problem is, when I requery the Type dropdown, it requeries ALL the Type dropdowns in the entire grid. This is no good because, the records that have Category 1 will be able to see Types X, Y and Z, which should only be available for records that have Category 2.
My research thus far proves that what I'm doing is impossible because, when you change a RowSource, you're changing the RowSource for ALL instances in the gird. So naturally, my first question is: Is this true?
But, even if it is, how come, in the following code, Me.Type = "" blanks out the Type box for just THAT record, but the Me.Type.RowSource updates the RowSource for ALL records? How come Me.Type = "" is able to segregate the combo box on THAT record, but RowSource isn't? That doesn't make sense... am I missing something?
Code:
Private Sub cboCategory_Change()
Me.Type = ""
Me.Type.RowSource = "SELECT dbo_RequirementType.Descr FROM dbo_RequirementType WHERE dbo_RequirementType.Category = '" & Me.cboCategory & "'"
Me.Type.Requery
End Sub