Cascading Combo Box relationship problem (1 Viewer)

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

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?

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?

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.

While you're at it, you might want to make some other changes.
1. ALWAYS give controls a meaningful name. Are you going to remember what combo7 is next month? What about anyone who has to take over the app when you're gone. Changing the name of the control after you have created procedures is a PITA so it is always best to do it BEFORE you use the control. When you change the control name, the existing procedures will be orphaned so you'll need ti find them and change their names to reconnect them to the control. You will also need to fix any references.
2. Speaking of how you reference controls - the best method is Me.ControlName. That is most efficient since the compiler knows immediately (based on the use of "Me") what module defines the variable name and since the .Value property is the default, you might as well omit it and make the name shorter. Using Me. also gives you intellisense and provides a visual clue that anything starting with "Me." is on the class module's form or report.
3. As has already been pointed out, sequence numbers in object names is a symptom of a design flaw. Why do some of the table names include a "1"?
4. Naming all your autonumbers ID just makes it harder for people to see the relationships between tables. General usage is to name the ID to correspond to the table name so some of yours would be: EvidenceID, ProjectID, ConsultantID, etc. That way it is easy to map the FK's to the PK's without having to open up the relationship diagram.
5. You seem to have a repeating group in tblEvidence. If you need multiple instances of Field related to Evidence, this is a many-to-many relationship and must be implemented by creating a relation table to connect tblEvidence to tblField. Junction tables contain two foreign keys. One to one side of the m-m and the second to the other side of the m-m. Junction tables may also contain what is called intersection data. That is data that is related specifically to the relationship. So Student-Class would include a grade and a date if there are multiple grads that need to be stored.
6. And last but certainly not least, Always define Referential Integrity. This ensures that you don't end up with orphan records and also allows you to specify Cascade Delete in certain hierarchical relationships.