On a form in a datasheet or continuous forms view how do we base the possible values in a second combobox on the value selected in a first combobox?

Question

This problem would apply equally if we used listboxes, some combination of list and comboboxes, or even another control type for our first control (textbox, checkbox, etc.) upon which our second control (a combbox or listbox) bases its possible values from which a user could choose.

There are two parts to this problem.

Firstly, having the second combobox's possible values (from which a user can select) be based on a value previously chosen through the first combobox.

After that problem is solved there arises a second. That across the multiple records in the displayed datasheet (or continuous form) the second combobox will blank out values that that were excluded by the last choice of the first combobox.

I answer my own question, solving both parts of the problem ...

Edit 01: Added first and second parts of the problem.

Edit 02: Note to Mods. It is not possible for the question to be more focused. It expresses a single focused problem. If anything the question could be changed to be more general, swapping out "combobox" for "control". However that's undesirable as: a combobox is the more common scenario; and the general applicability is explained in the body of the answer..

Edit 03: Note to mods ...

Just to emphasize why the question is as focused as it reasonably ought be. There exists online the nearby less focused problem E.g. at O'Reilly > Limit the Items in One Combo Box Based on the Selected Item in Another.

But that only addresses the case where you have a combo on a main form and subform (a solution that could also apply to two combos on a form in the standard "form view"). Indeed the O'Reilly page warns that in the more focused circumstance "Using related combo boxes in the detail section of a continuous form" that this "can cause problems" with the implication these are unsolvable problem. (This is the same problem if the form is datashet view).

That focused problem, the specific problem my question references, is also raised repeatedly on stack overflow. E.g.

But each of those posts are less ideally formed in manner which mine has sought to overcome. The first and second aren't in the form of a question (and arguably neither is the third even with the presence of a question mark). And all three of these don't capture (in the text of the "question") that the problem applies just as well to a form in datasheet view as with continuous view (they pick out one view over the other). In that way they are wrongly too focused.

In addition each of these posts have chosen answers that wrongly assert there is no solution to this problem, sometimes with vague allusions to workarounds. My answer provides a detailed solution to something this is generally dismissed on stackoverflow as unsolveable.

I think it was closed, because it is not easily answerable as a programming question (see on-topic ). I don't use ms-acess, so it's just a guess, but maybe if you included the code you have already written and pointed out what exactly doesn't work, the question would not be closed. — pptaszni
I've done everything you suggest in my orignal posting. In my question body I point out what exactly doesn't (and does) work. I expand on what exactly doesn't (and does) work in my answer. In my answer I also provide code that solves the problem. In addition to all that is required in an ms-access environment (such as setting properties of controls). So your speculations about the thinking of the moderator are implausible. — John Bentley
mmm yeah, I didn't see the answer (it is not visible in the review queue); anyway, it should not be necessary to read the answer to understand the question — pptaszni
Right. It should not be necessary to read the answer to understand the question. Which is why I formulated the question as I did (in a way that best expresses a focused question on a very specific problem; and which is obviously a programming question to anyone familiar with MS Access). In any case thanks to whomever has unclosed the question. — John Bentley

John Bentley John Bentley · Accepted Answer · 2020-11-02T15:24:32

Overview

Broadly this is achieved by having the second combobox be based on a parameters query that references the first combobox.

But after you've done that the relatively harder part of the problem emerges. Across the multiple records in the displayed datasheet (or continuous form) the second combobox will blank out values that that were excluded by the last choice of the first combobox.

That problem is solved by temporarily altering the Row Source of the second combobox during the Enter event of that combobox to a "selecting relevant" dataset (that limits possible values in the second combobox based on the value selected in the first). Then on the Exit event of the second combobox the RowSource restores a "display relevant" dataset (that doesn't limit possible values in the second combobox based on the value selected in the first).

Details

Our Example

For all objects, tables apart, I'll use Reddick's RVBA Naming Conventions. Specifically from Host Application and Component Extensions Tags > Access Objects. For tables I won't have any "Hungarian" prefix (this is just a personal quirk of mine).

In our example we'll have a frmCashInflow (Form), displayed as a datasheet, that (obviously enough) tracks cash inflow to a business.

Our first combobox will be cboCashInflowCategory.
Our second combobox, which is to restrict its possible values based on the value chosen in the first combobox, will be cboCashInflowSubcategory.

The relevant lookup data is as follows:

CashInflowCategory (Table)

| CashInflowCategoryID | CashInflowCategoryName                                |
| -------------------- | ----------------------------------------------------- |
| 1                    | Sales                                                 |
| 2                    | Bank Interest Received                                |
| 3                    | Refunds from Suppliers                                |
| 4                    | Property, Plant, and Equipment - Asset Disposal Sales |
| 5                    | Intangible Assets - Asset Disposal Sales              |
| 6                    | ATO Refunds Received                                  |
| 7                    | Contributions from Owner                              |

CashInflowSubCategory (Table)

| CashInflowSubcategoryID | CashInflowSubCategoryName              |
| ----------------------- | -------------------------------------- |
| 1                       | Retained-copyright-software-copy-sales |
| 2                       | Services                               |
| 3                       | Notice of Assessment, Business Portion |
| 4                       | Business Activity Statement            |

Next we have a table the expresses the subcategories that belong to particular categories. For the sake of a full example we'll allow that some categories don't have any subcategories.

CashInflowCategorySubcategory (Table)

| CashInflowCategorySubcategoryID | CashInflowCategoryID                                  | CashInflowSubcategoryID                |
| ------------------------------- | ----------------------------------------------------- | -------------------------------------- |
| 1                               | Sales                                                 | Retained-copyright-software-copy-sales |
| 2                               | Sales                                                 | Services                               |
| 3                               | Bank Interest Received                                |                                        |
| 4                               | Refunds from Suppliers                                |                                        |
| 5                               | Property, Plant, and Equipment - Asset Disposal Sales |                                        |
| 6                               | Intangible Assets - Asset Disposal Sales              |                                        |
| 7                               | ATO Refunds Received                                  | Notice of Assessment, Business Portion |
| 8                               | ATO Refunds Received                                  | Business Activity Statement            |
| 9                               | Contributions from Owner                              |                                        |

Some aspects of this table:

The fields CashInflowCategoryID and CashInflowSubcategoryID actually store numbers. In design view of the table, on the [Lookup] tab a Display Control of "Combo Box" is set, with Row Sources of CashInflowCategory and CashInflowSubCategory respectively. Other values on the [Lookup] tab are set appropriately/as desired. The net effect is that in the table above we see the user friendly ID names (as above) rather than the raw numeric IDs.
Also in table design view we create an Index called idxCashInflowCategorySubcategory comprising the two fields CashInflowCategorySubcategoryID and CashInflowSubcategoryID . For this index "Ignore Nulls" is set to No. Unique is set to "Yes". This index is created (and a single field primary key is added) rather than setting the primary key to comprise both CashInflowCategorySubcategoryID and CashInflowSubcategoryID to allow that some Categories have no Subcategories. This is a common, although not a necessary, requirement - depending on the business rules.
In the relationship diagram referential integrity is set between CashInflow (table) and:
- CashInflowCategorySubcategory (table), via both CashInflowCategorySubcategoryID and CashInflowSubcategoryID. That is, rather than
- CashInflowCategory (table) and CashInflowSubCategory (table) separately.
This is to enforce that in CashInflow (table) a Subcategory can't be chosen that isn't associated with a Category in the way expressed by CashInflowCategorySubcategory (Table).
In the relationship diagram referential integrity is set between:
- CashInflowCategorySubcategory (table) and CashInflowCategory (table); and between
- CashInflowCategorySubcategory (table) and CashInflowSubcategory (table)
That is, the CashInflowCategorySubcategory table expresses a many-to-many relationship between CashInflowCategory (table) and CashInflowSubcategory (table).

The Procedure

We have the tables as above. We have frmCashflow bound to CashInflow (table), the table that adds our transactions.
On frmCashflow add cboCashInflowCategory.
1. Control Source: CashInflowCategoryID
2. Row Source: CashInflowCategory
3. Row Source Type: Table/Query
4. Bound Column: 1
5. Limit to List: Yes
6. Column Count: 2
7. Column Widths: 0cm;1cm [We hide the ID, as this is numeric and not user friendly]
8. List Width: 11cm [Some generous value to show our user friendly Category name]

Create a parameter query qprmCashInflowSubcategory in the Query By Example (QBE) grid, to produce the SQL as follows.

SELECT CashInflowSubcategory.*
FROM CashInflowSubcategory INNER JOIN CashInflowCategorySubcategory ON CashInflowSubcategory.CashInflowSubcategoryID = CashInflowCategorySubcategory.CashInflowSubcategoryID
WHERE CashInflowCategorySubcategory.CashInflowCategoryID=[Forms]![frmCashbook]![sbctCashInflow].[Form]![cboCashInflowCategory];

Note that the combobox cboCashInflowCategory on the form is referenced. This is what powers our second combobox cboCashInflowSubcategoryto confine it's values to that selected by the first combobox cboCashInflowCategory.

On frmCashflow add cboCashInflowSubcategory
1. Control Source: CashInflowSubcategoryID
2. Row Source: qprmCashInflowSubcategory [Temporarily set, to demonstrate a problem]
3. Row Source Type: Table/Query
4. Bound Column: 1
5. Limit to List: Yes [Attempting to set to "No" causes all sorts of strife]
6. Column Count: 2
7. Column Widths: 0cm;1cm [We hide the ID, as this is numeric and not user friendly]
8. List Width: 8cm [Some generous value to show our user friendly Category name]
Add the following to cboCashInflowSubcategory's On Enter event (among several possible events where you might be tempted execute the Requery) .
```
Private Sub cboCashInflowCategory_AfterUpdate()
  Me.cboCashInflowSubcategory.Requery
End Sub
```

Open frmCashflow (in datasheet or continuous form view), select a "filtering" value from cboCashInflowCategory. Namely "Sales" or "ATO Refunds Received". Move to cboCashInflowSubcategory and observe that the values are successfully filtered. E.g. If in cboCashInflowCategory you selected "ATO Refunds Received" then in cboCashInflowSubcategory the available choices are only "Notice of Assessment, Business Portion" and "Business Activity Statement" (and we can't see "Retained-copyright-software-copy-sales" nor "Services" ).

However, you'll also see a new problem. The only values displayed in cboCashInflowSubcategory, across the whole datasheet, will be confined to the lastly chosen value in cboCashInflowCategory. E.g. You'll get either ...

| Date       | Category                                              | Subcategory                            | Amount (Inc. any GST) |
| :--------- | ----------------------------------------------------- | -------------------------------------- | --------------------- |
| 2020-07-28 | ATO Refunds Received                                  | Notice of Assessment, Business Portion | $70.00                |
| 2020-07-29 | ATO Refunds Received                                  | Business Activity Statement            | $2,000.00             |
| 2020-09-19 | Intangible Assets - Asset Disposal Sales              |                                        | $500.00               |
| 2021-01-26 | Property, Plant, and Equipment - Asset Disposal Sales |                                        | $100.00               |
| 2021-03-11 | Sales                                                 |                                        | $3,000.00             |
| 2021-04-17 | Sales                                                 |                                        | $1,000.00             |

.... or, if "Sales" was lastly chosen in cboCashInflowCategory ...

| Date       | Category                                              | Subcategory                            | Amount (Inc. any GST) |
| ---------- | ----------------------------------------------------- | -------------------------------------- | --------------------- |
| 2020-07-28 | ATO Refunds Received                                  |                                        | $70.00                |
| 2020-07-29 | ATO Refunds Received                                  |                                        | $2,000.00             |
| 2020-09-19 | Intangible Assets - Asset Disposal Sales              |                                        | $500.00               |
| 2021-01-26 | Property, Plant, and Equipment - Asset Disposal Sales |                                        | $100.00               |
| 2021-03-11 | Sales                                                 | Retained-copyright-software-copy-sales | $3,000.00             |
| 2021-04-17 | Sales                                                 | Services                               | $1,000.00             |

... while what we want is to display all Subcategories ...

| Date       | Category                                              | Subcategory                            | Amount (Inc. any GST) |
| ---------- | ----------------------------------------------------- | -------------------------------------- | --------------------- |
| 2020-07-28 | ATO Refunds Received                                  | Notice of Assessment, Business Portion | $70.00                |
| 2020-07-29 | ATO Refunds Received                                  | Business Activity Statement            | $2,000.00             |
| 2020-09-19 | Intangible Assets - Asset Disposal Sales              |                                        | $500.00               |
| 2021-01-26 | Property, Plant, and Equipment - Asset Disposal Sales |                                        | $100.00               |
| 2021-03-11 | Sales                                                 | Retained-copyright-software-copy-sales | $3,000.00             |
| 2021-04-17 | Sales                                                 | Services                               | $1,000.00             |

To give us what we want, to both restrict cboCashInflowSubcategory's possible values based on the value chosen in cboCashInflowCategory but also display all chosen cboCashInflowSubcategory values as above, we do the following ...

Undo the previous step. That is delete the cboCashInflowSubcategory's On Enter code and event.
Set cboCashInflowSubcategory's Row Source to CashInflowSubcategory (table). This will be our "display relevant" dataset.

Temporarily set cboCashInflowSubcategory's Row Source to a "selection relevant" dataset, then restore the "display relevant" dataset. Add the following event code (verifying this is hooked up correctly in the comboboxes' property sheet)

Private Sub cboCashInflowSubcategory_Enter()
  ' Temporarily set a "selection relevant" dataset
  Me.cboCashInflowSubcategory.RowSource = "qprmCashInflowSubcategory"
  Me.cboCashInflowSubcategory.Requery
  Me.cboCashInflowSubcategory.Dropdown ' Optional
End Sub

Private Sub cboCashInflowSubcategory_Exit(Cancel As Integer)
  ' Restore the "display relevant" relevant dataset
  Me.cboCashInflowSubcategory.RowSource = "CashInflowSubcategory"
  Me.cboCashInflowSubcategory.Requery
End Sub

Everything should now work seamlessly in the UI as desired.

Edit: Added 'Unique is set to "Yes".' for idxCashInflowCategorySubcategory