Cascading 3 Combo Boxes, Produce data in Subforms

[michie999]

Hi

I am designing my first database. I'm trying to create cascading combo boxes in a form and display the data inside the subform. I have 3 combo boxes on the form. I'm trying to make them dependent on each other so by selecting one combo box updates the others in the series.

Diagrammatically the model is:

1. Context of Approval----<Type of Approval----<Risk Category
2. Produce data inside subform (Risk data).
3. ONE Risk can have many Risk Category and can belong with many Approval.

This my current table, just to clarify im using many to many type relationship.

Table:

Risk
    ID_Risk  (PK)
    Risk_Name
    Date_Created

Type of Approval
    ID_Approval_Type (PK)
    Approval_Type_Name
    Date_Created

Context of Approval
    ID_Context_Approval (PK)
    Context_Approval_Name
    Date_Created

Risk Category
    ID_Risk_Category (PK)
    Category_Name
    Date_Created

Join Table : called cross

Cross_Risk_Approval (between Risk & Approval)
    ID_Risk (FK)
    ID_Approval_Type (FK)

Cross_Context_Approval (between Type of Approval & Context of Approval)
    ID_Approval_Type (FK)
    ID_Context_Approval (FK)

Cross_Risk_Category (between Risk & Risk Category)
    ID_Risk (FK)
    ID_Risk_Category (FK)

[michie999]

The first combo box is called CBO_CONTEXT, second CBO_APPROVAL and third CBO_CATEGORY. CBO_CONTEXT combo box has several data represent as mother grouping for CBO_APPROVAL, selecting a particular CONTEXT, should filter APPROVAL belonging only from the CONTEXT selected. Selecting a particular APPROVAL from the CBO_APPROVAL combo box filters the CATEGORY combo box.

Then there are subform below will produce a data (data inside table Risk) which will only produce data base on what has been selected on combo box previously. This is how it should behave.

[michie999]

So the position in the RELATIONSHIP is: "Refer the attachment"

"Context of Approval", "table cross", "Type of Approval", "table cross", "Risk", "table cross", "Risk Category"

Im not very familiar with access + just a basic knowledge of codes. Right now the way Im doing:

The way I make the form, main form is the Risk Category, for the SUBFORM, I make query base on table : "Refer the attachment"

“Cross_Risk_Approval”, “Risk”, “Cross_Risk_Category”, “Risk Category”.


After selecting Risk Name the column that I want to display. Base on query I create from

Form Wizard creating a sub-from Datasheet. After that I just drag and insert into the main form.

To make the combo box working. For combo box CBO_CONTEXT I just simply selected from combobox wizard, same goes for CBO_APPROVAL & CBO_CATEGORY. After that inside CBO_APPROVAL row source: "Refer the attachment"

"Cross_Context_Approval" & "Type of Approval"


Within the query under the table Cross_Context_Approval, column ID_Context_Approval inside criteria I enter " [Forms]![SubFrom]![ComboBox] ". Same method goes to Inside CBO_CATEGORY.


-The result is, after selecting CBO_CONTEXT then it will produce a specific CBO_APPROVAL data.
-Then after selecting from CBO_APPROVAL, it will produce a specific CBO_CATEGORY data.
-But after that inside the subform, it produce all data inside the CBO_CATEGORY but not filtering from CBO_APPROVAL.

Its not like what I'm trying to achive. "subform will produce a data (data inside table Risk) which will only produce data base on what has been selected on combo box previously" which is CBO_APPROVAL & CBO_CATEGORY.

Base on my search for this situation, they are more on one to one relationship or one to many, or only using one table. I trying my hard to understand the code givens out there.

Base on codes what I found and trying to tweak it all around. All the try and error I've been try it have been like a month or more.

I really really really appreciate all help that I can get. I also glad to share my datababase for you guys to look into it. I'm using Access 2013.

[twinnyfo]

Michie999,

Welcome to Bytes!

I will offer a generic solution as a way to begin your approach to this issue.

If you want cascading combo boxes (that a generally understood term here), the approach is as simple as this:

When an item is selected from the first combo box, do the following things:

1. The combo box should have an AfterUpdate event
2. Design a query in VBA in that event procedure which is based upon the value of that combo box
3. Assign that query string to the Record Source of the next combo box
4. Requery the next combo box
5. Enable the next combo box (this assumes you don't want them to choose anything from this combo box until they've made their first selection)
6. Repeat these steps for as many combo boxes as necessary

Again, this is the approach. We can help you through any particular struggles and snags you may come across.

Hope this hepps and gets you pointed in the right direction.

[NeoPa]

Hi Michie.

Welcome to Bytes.com :-)

I see that you've done particularly well at presenting your question. That would be true for anyone posting such a well specified question but it's particularly impressive for a first-timer.

I have an article (Cascaded Form Filtering) that should give you detailed help to handle this.

As someone who obviously cares and tries to lay out their information neatly and easily to read I'll give you a couple of tips. Only a couple as you seem to have worked out the rest already :

1. In the CODE window spaces can be used to indent. I suspect you may have tried using TABs, which don't, but spaces do.
2. If you have a block of code between the [CODE]...[/CODE] tags and you don't want an extra blank line showing at the bottom, simply put the closing [/CODE] tag at the end of the last line of your code.
   The opening [CODE] tag isn't as fussy. It will ignore the first new line immediately after it if it finds one.

Let us know how you get on and if you feel you've got what you needed here.