Cascading Combo Box relationship problem

[ChunkyChats]

HI,

I have created an assessment tool in Access and its working well. My only problem is that I created a cascading combo box based on tblField. The following code I borrowed from the internet which helps select a sub category based on the initial selection:

Private Sub Combo7_AfterUpdate()
On Error Resume Next
Select Case Combo7.Value
Case "1"
Combo37.RowSource = "tbl2SafetyPolicyObjectives"
Case "2"
Combo37.RowSource = "tbl2SRM"
Case "3"
Combo37.RowSource = "tbl2SafetyAssurance"
Case "4"
Combo37.RowSource = "tbl2Safetypromotion"
Case "5"
Combo37.RowSource = "tbl2AdditionalItems"
End Select
End Sub

My problem is running reports and queries, I think I've not connected the relationship correctly and when I query the results I just get the selection number which isn't linked to the text in the subcategory in MSATField2. There fore reports and queries show MSATField1 great but the sub category is a problem.

Any help gratefully received

Steve

 

[Minty]

Your data structure is wrong. You have multiple tables trying to be stored in one place, which wont't work long term.
All your table2... tables don't need to be separate - you should have them all in one table with an additional field identifying the sub type.

Does that make sense?

 

[ChunkyChats]

Thanks Minty, I thought it might be something like that. I'll have a go. I'm guessing ill have to change the 'case' code to select the identifier in the single table?

 

[Minty]

I would add the case type to the combo as a extra hidden column then simply set the reports filters etc to
Me.cboYourCombo.Column(2) <-- **
**Note column numbering starts at 0 so this would be column 3's value.