cascading combo in continuous form

Question

Hi All, can anyone tell me if it is possible to have cascading combo's in a continuous form?

I have my tables set up with the second combo based on the first... the rowsource for the second combo is:

SELECT tblAssetSubLst.AssetLstIDS, tblAssetSubLst.AssetLstNm, tblAssetSubLst.AssetMainID

FROM tblAssetSubLst

WHERE (((tblAssetSubLst.AssetMainID)=[cboAssetMainID]));

Making a selection in the first row works fine but of course it doesn't work set up in this way when a second row of combo's is selected.

Is there any way to set up cascading combo's in a continuous form?

Answer 1

The problem with Cascading combos on a continuous form is that a continuous form is only one set of controls set up to display multiple times. So the Rowsource of the combo is going to read the value in the first combo for the CURRENT record. So what is going to happen is all the rows will show the list based on the current record. So if you have selected a value in one row, then change the first combo in another row the control in other rows will go blank since the selection is no longer in the Rowsource.

The way to get around this is to use a hybrid control. You place a text box on the form over the Combobox. The control source will display the selected value and the combo will be hidden except when that record has focus. This thread has more info on this:

https://answers.microsoft.com/en-us/office/foru...

Answer 2

The fact that you refer to tables suggests that the combo boxes are bound controls.  This might raise a normalization issue, if the column to which the second combo box is bound is a determinant of the column to which the first is bound.  This point is overlooked in many of the demonstrations of correlated combo boxes which I've seen.

A simple example would be a table of locations which includes columns for the city and state (or equivalent).  As city functionally determines state, such a table would not be normalized to Third Normal Form (3NF) due to the transitive functional dependency of state on the table's key, leaving the table open to the risk of update anomalies.  There is a legitimate exception to this, which I'll refer to later.

In a situation like this, the table should include a column representing only the lowest level of the hierarchy, cities in this case.  The state combo box would be unbound.  The text box superimposed on it to create a 'hybrid' control, would be bound however, but not to a column in the 'locations' table.  The form would be based on a query which includes the cites and states referenced tables in addition to the 'locations' table.  You'll find examples in ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

I said earlier that there can be a legitimate exception which would allow the combo boxes representing each level of a hierarchy to be bound.  This is where the keys are multi-column 'natural' keys rather than 'surrogate' numeric keys.  My ComboDemo file includes an alternative model where this is the case.  While this model is legitimate, most people, including myself, would be unlikely to use it in this context.  In other contexts it is appropriate, however, and you'll find an example in Relationships.zip in the same OneDrive folder.  This uses a simple medical prescriptions database as its example, with multi-column keys being used for all but patients.  This makes the correlation of the controls in the interface very much simpler, avoiding hybrid controls completely.

Another issue with correlated combo boxes is the use of the NotInList event to insert a new row into the referenced table.  An example of this can be found in my DatabaseBasics.zip demo.  This includes a contacts form in which there are correlated combo boxes for Country, Region and City.  As this is in single form view they are not hybrid controls, but the NotInList event procedures for each illustrates how new items can be added to the list in each case.  The code in each case can be thought of as 'correlated', in the same way as the controls themselves are.  The code in each case includes a reference to the control above it in the hierarchy.  Note how this allows the insertion of duplicate city names, Paris, France and Paris, Texas in this case.  It does, for the purpose of the demo make the assumption that city names cannot be duplicated within a single region.  In reality this is not the case, and a sub-regional entity type such as counties in the US and districts in the UK would need to be added to the model to differentiate between such duplicated city names.

Answer 3

The issue here is whether, let’s call it SubAsset, functionally determines Asset, i.e. for each value of SubAsset there can be only one value of Asset.  With your first example, if the SubAsset value in a row is 'Concrete' (actually the numeric key which identifies 'Concrete') the only possible value of Asset can be 'Surface' (again, actually the key which identifies 'Surface').  If that is the case, then SubAsset is functionally determined solely by the table's key.  Asset on the other hand is functionally determined not only by the key, but also by SubAsset.  It is then said to be transitively dependent on the key.

The formal definition of Third Normal Form (3NF) is:

Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.

Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table (or more accurately a table definition), a tuple to a row (record) and an attribute to a column (field).

In plain English this means that every non-key column must be determined solely by the whole of the table's primary key, or 'the key, the whole key, and nothing but the key, so help me Codd'.  It was Codd who first introduced the database relational model to the world around 1970.

The implication of a table not being normalized to 3NF is that the table contains redundancy, which is 'a bad thing'.  If we know that the SubAsset is 'Concrete' then we know that the Asset value is 'Surface', so an Asset column in the table is unnecessary.  More importantly it leaves the door open to bad data, as a row could contain an Asset value of 'Surface' and an SubAsset value of 'Handrail', which is an illegitimate combination of values.

It is this illegitimate combination of values which we are trying to prevent by the use of correlated combo boxes of course, but the interface should only reinforce the constraint imposed by the design of the tables and the relationships between them, not be the only means of applying the constraint.  By normalizing the table through the removal of the Asset column this possible 'update anomaly' is removed.  By the use of unbound controls, as illustrated in my ComboDemo file, the operation of the database via the form is unchanged.

My same OneDrive folder also contains a Normalization.zip file which goes into the subject in more detail, illustrating each Normal Form with simple examples; or at least as simple as possible, but no more so.

Answer 4

Hi Ken,

Thank you for your reply, it's always great to have an explanation of things from someone like yourself and other MVP's, I'd love to have even 1% of the knowledge of you guys... and girls.

I understand what you are saying about the State table being unbound I'm just not sure it would suit my situation, but I'm not positive about that.

What I have is a continuous form on a tabbed subform on a main form. On the continuous form I have two combo boxes (and other fields). The first combo represents "Assets" of which there are initially 15 or so. The second combo is a description of those assets i.e.  Asset may be 'Surface', description may be 'Concrete', 'Pinebark' etc. Each of these have other fields associated and need to be completed such as hazards, risks, comments etc.

If I had just 15 assets every time I could probably do it a different way but because I will also need to add additional assets (for each job) for instance I might have one surface which is bitumen and another which is concrete. I need to add a second 'Surface' so as I have it set up, being a continuous form, I can select surface again in the first combo and the description in the second. With each one I need to add further details/fields

I have bound both combo's so I can use the first combo selection as a label for the second combo in the report so it looks like:

Surface      Concrete          ....... other details

Barrier       Handrail            ...... other details

I populate the continuous form with the 15 initial combo's by appending the table the continuous form is linked to (with the main form ID) from basically a template table. I then don't have to go down and select each of the first combo's, just select from the second combo and complete the other fields next to each one.

I don't know of a better way to do it but would be grateful for any ideas.

Answer 5

Thanks Scott,

I understood why it wasn't working but the dbase example on the link you gave did the trick. Thankyou.