Syntax and Format for Combo Boxes using Join or Junction Tables and Correlated Sub-Forms?

Question

Update: Does anyone know how to:

Configure a correlated combo box which pulls data from multiple tables via a junction table, using criteria from another combo box? (Ken, where are ya?)

In my last post/reply, I'd progressed to the point where the 2nd combo box is displaying the correct key values from the join/junction table- I just need to figure out the syntax or code to get the Parent table field text to display instead....(?)

---

In my ongoing climb up a steep learning curve for Access:

1.  I'm struggling to get the right match between a combo box 'selection' as the basis for cascading selections in the next combo box's row source.

• the record set consists of a Parent table for each unique type of data, and a 'Join Table' that defines by Parent table IDs the possible "combinations" for an established heirarchy based on records entered. Below is the Query view where I was attempting to set the cboDivision Row Source, based on a relationship from a junction table.
• The same query works as a stand-alone Query, and returns the correct 'Division' data when I add a specific 'Service' as Criteria. Such as: Show all Divisions for Service "FSS";   Why can't I achieve the same result in a Form by using a previous combo box value? (i.e. the user selected "FSS" in the cboService.)

2. I tried using the ID's of preceding tables as foreign keys in the next table, and it worked fine where the record set being looked at was on 2 or three tables.

• But as I progress further in the overall recordset/data set, the successive tables start getting really cluttered and messy to where you have: PK, fk.tbl2, fk.tbl3, fk.tbl4.... and so forth. The database bogs down when you run queries or try to use this in a form.

• I tried a variation where the form Record Source was based on a Query (which initally worked) but that strategy broke once I needed to retrieve/display data and fields from tables not part of the inital 3 table query.
• It doesn't seem practical (or the right answer) to base the form on a 'Super Query' of every possible data set from tables and relationships.
• After trying several database designs over the last few weeks, I've settled on using Parent tables with Junction tables. The queries and reports I've ran so far have worked fine (somewhat faster too).  This seems to be the most practical and efficient way for me to manage realtionships between what will eventually be about 20 different parent tables, each holding unique information that will tie into one another.
• When using this Parent-Junction realtionship model and adding or editing queried fields, I haven't had any problems- the Parent tables add a new record with unique ID (pk), and the junction tables update with the correct IDs from the related tables. Which is the reason I was hoping to use correlated sub-forms to retreive, edit or add data.

All this being said, can someone assist me with figuring out the right Expression and Syntax for using a junction table and where the new /next combo box uses the value of another combo box as criteria?

Thanks as always,

Rob C.

Answer 1

Scott,

This may help illustrate what part of the big picture is. Because my colleague and I find ourselves needing to accelerate from beginner knowledge of access to advanced, we may be doing things out of the ordinary or unorthodox, and I’m certain previous iterations of this DB had nothing but garbage.

The whole junction table thing may be semantics, so let’s call them stand alone “join tables” of the PKs from several parent tables, the primary difference being that instead of several FK fields in the parent table, we put them in a table unto themselves. All the relationships are valid, information integrity (i.e. this record relates to that one) and DB functionality all work fine. If it matters, which I don’t think it does for the question- We will have other tables that are many-to-many (Apps, Programs, Files, Equipment that are needed to perform a ‘Function’) they just aren't moved in yet.  Regardless;

• If I can use "join/junction/hermaphrodite tables” in a DB query (outside of a form) to retrieve records (and I've done so repeatedly); where there is a unique ‘value’ criteria specified,
• how do I do that in a form where I want the criteria to be based on the value of a previous combo box?

I want the combo query to function like the simple query did, but where I just typed “Blah” in the criteria, I want Combo box 2 to look at Combo 1 and if Combo1 says “blah” it returns a set of records related to that in Cbo2.  If Cbo1 says “Tweaky Weasle Farts” then the records displayed in Cbo2 are related to “Tweaky Weasle Farts” because the ” join/junction/hermaphrodite tables”  says the PKs in the two tables are related.

I’ve being working at this for a while, and mixed results with my test form.  I progressed from getting nothing in cboDivision, thru to the "Floating Error Box of Woe" where upon opening the form it immediately broke and wanted a value for either ServiceID, or DivisionID… once I think it may have asked what my favorite color was… (I could be mistaken due to caffeine levels).

I’ve now got the Form to where after making a selection from the list in cboService, next cboDivision displays the correct DivisionIDs (not the Division text field) that correspond to the “Service” selected.  It appears that it’s SQL is making a backwards association to the Organization/join-junction-hermaphroditetable*.*Here’s the code:

SELECT DivisionsTbl.Division, DivisionsTbl.DivisionID, ServicesTbl.ServiceID, OrgJunctTbl.ServiceID

FROM ServicesTbl

INNER JOIN (DivisionsTbl INNER JOIN OrgJunctTbl ON DivisionsTbl.DivisionID = OrgJunctTbl.DivisionID)

ON ServicesTbl.ServiceID = OrgJunctTbl.ServiceID

WHERE (((OrgJunctTbl.ServiceID)=[ServicesTbl]![ServiceID]

And (OrgJunctTbl.ServiceID)=[Forms]![fmTest_2a_Robs]![cboService])) ORDER BY DivisionsTble.Divison

The DivisionIDs are correct, and match the parent table, but the display is from the join-junction-herma-phodite table, rather than the parent. I say this because the pull-down displays the IDs in the order they’re in on the join-junction-hermaphrodite. 'Division' is the text field that I want displayed.

Answer 2

Again, it is not clear that you need junction tables. Junction tables only exist to model a many to many relationship and I'm not see where you have those. You need to provide a better understanding of your structure, what is the real world situation you are modeling.

Answer 3

Regardless of what you are asking about, you can not use Me in a query.  Me is strictly available only in form, report and class VBA modules.  Instead use the full form reference in this style:

   Forms![name of main form].[name of control in main form]

Or, if the control with the criteria value is in a subform, use:

   Forms![name of main form].[name of main form's subform control].Form.[name of control in subform]

Answer 4

Scott, I used FKs previously and it worked (see second pic above) but only for the Organization portion of the data. The foreign key method failed in later parts of the form I was trying to create. Once you get past the Organization elements, table relationships for all the other Parent tables get complex. Queries and such became really problematic when I tried using multiple foreign keys in each Parent table. I went with the junction option because it was very efficient for tying in different tables and data to others.

In some instances the form versions failed because one or more record matches didn't yet have data in a particular field or fields. So far I don't have that problem using multiple junction tables. The query shows the current values for fields that have data, and leaves blank the ones that don't.  The reasons why the other foreign key design wasn't working properly regarding Null/Blank or no records fields isn't what I'm trying to solve here, but I included that as background for why I'm trying this method. So... back to matter of my question:

Acknowledging that the Organization stuff can be acomplished without a Junction table, I'm using it right now to learn the mechanics and syntax for:

 A correlated combo box which pulls data from tables via a junction table, using criteria from another combo box;

This is a functionality which I will need later in other parts of the form. I just picked Organization tables to work it out on, because they're easier for me to see the behavior and results.

Answer 5

I'm a little confused here. A junction table is used to model a many to many relationship. I'm not sure I am seeing that. Also, when you have a hierarchical structure you may not need to match each level. I haven't seen your other posts but some observations here. 

I see Service, Division and Section as Attributes of an Organization. The question is can a Division apply to multiple Services and a Service to multiple Divisions? Similar question on Division and Sections. If the answer to either is no, then you don't have a many to many and no need of Junction tables. If you do, then you do need the junction tables and your combobox Rowsources have to join the three tables together.

If you don't have many to many then the next question is whether the hierarchy is exclusive. For example is there one division for each section so if you know the section do you know the division? If so, then all you need to store in your main table is the section as a foreign key. .