Cascading Combo Box - Form, connecting the Sub Form

[Hell_1934]

Hi,



I am trying to create a cascading Combo Box

My Main Form has a Sub Form, where I have a combo Box, after clicking on which I want my 2nd Sub Form would show the only values, associated with that Combo box.

So, it is Main Form -> Sub Form - Sub Form 2.
And I want a cascading combo box - selecting values in the button located in Sub Form -> the only Sub Form 2 values appear, associated with that button in the Sub Form.

(plz, see the screenshot below):


Below, are my Categories/ Objectives structure(the certain Category -> associated with the certain Objective:


To achieve my goal, I clicked on the ObjectiveID in my SubForm2 and in the Data section I wrote an expression, plz see below:



And, also, in the Event Procedure "On Click" I added -

Private Sub ObjectiveID_Click ()
Me.Requery
End Sub


But this is not working, still selecting ALL Objectives ...

Plz, help
Thank you..


Ps-
I can't attach a zipped file, because it's 6 MB while the maximum is 2 MB and zipx format is not allowed...

[June7]

Can make copy of db and remove most data and/or objects not related to issue for attachment to post.

[Hell_1934]

Can make copy of db and remove most data and/or objects not related to issue for attachment to post.

I did, but it's still big
I will try today in a few hours again, when more time!

[isladogs]

You actually have a form with one subform. There are no cascading combo boxes.
The subform includes a subdatasheet of related records but that isn't a second subform

Subdatasheets cause performance issues and often confuse end users.
You would indeed be better off with main form / subform & sub-subform all linked together with master and child fields.

If you want to view an example of a form with cascading combos (5 of them), see https://www.access-programmers.co.uk...d.php?t=302126

[Hell_1934]

You actually have a form with one subform. There are no cascading combo boxes.
The subform includes a subdatasheet of related records but that isn't a second subform

Subdatasheets cause performance issues and often confuse end users.
You would indeed be better off with main form / subform & sub-subform all linked together with master and child fields.

If you want to view an example of a form with cascading combos (5 of them), see https://www.access-programmers.co.uk...d.php?t=302126

Ok,
Is it possible to have a dependency-
Category ->Objective in my case?

So, that after I'll select the Category from combo box in my Sub Form, the appropriate Obective list would appear in the Sub Data sheet?

If yes - what do I have to do?

I'll also try to redevelop a little bit, the way you adviced - with Form -> Sub Form - Sub Sub Form.
And then - will look at your link where it's a zipped db example...

Thank you!

[June7]

Do you have a table that associates categories with objectives?

Really need to see your db structure.

[Hell_1934]

Ok,
Is it possible to have a dependency-
Category ->Objective in my case?

So, that after I'll select the Category from combo box in my Sub Form, the appropriate Obective list would appear in the Sub Data sheet?

If yes - what do I have to do?

I'll also try to redevelop a little bit, the way you adviced - with Form -> Sub Form - Sub Sub Form.
And then - will look at your link where it's a zipped db example...

Thank you!

I am trying to send you a message, and I will send a DB, but in 2 hours - when I'll have access to my PC


Thank you!

[Hell_1934]

Do you have a table that associates categories with objectives?

Really need to see your db structure.

Hello,
I am finally at the Desktop!

I added a zip file as an attachment. It is really a .zipx file, but this Forum doesn't allow to attach zipx.
Please, rename into <File Name>.zipx and it should be no problem to unzip it.
I think it should be no problem in any case...

Your advice about the cascading Combo box worked great on another Project!
But this one is a little bit trickier. Very complex Relations and tables (I removed the Program / Client only)

So, my goal is in that SubForm - to select "Category" combo box, then - only 4 choices should appear for Category 1 - 1. Orientation / Assessment - (while now there are All of them)


Below are the tables - Category / Objectives with the appropriate selection - (my goal - how I want it to be)



Thank you, I'll be waiting for your answer

[Hell_1934]

Do you have a table that associates categories with objectives?

Really need to see your db structure.

Ok, Just attached zipx (renamed to zip) into my main post...

[Hell_1934]

You actually have a form with one subform. There are no cascading combo boxes.
The subform includes a subdatasheet of related records but that isn't a second subform

Subdatasheets cause performance issues and often confuse end users.
You would indeed be better off with main form / subform & sub-subform all linked together with master and child fields.

If you want to view an example of a form with cascading combos (5 of them), see https://www.access-programmers.co.uk...d.php?t=302126

Actually, I double checked - there is a Main Form / Sub Form / Sub Sub Form -

[June7]

I cannot extract from your zip, renaming to zipx just makes it worse. Why don't you use Windows Compression?

[Hell_1934]

I cannot extract from your zip, renaming to zipx just makes it worse. Why don't you use Windows Compression?

Let me try ...

[Hell_1934]

I cannot extract from your zip, renaming to zipx just makes it worse. Why don't you use Windows Compression?

How about this?
(I shortened the Category / Objectives down to 2 Categories, 7 Objectives - 4 in Category 1; 3 in Category 2), Compact & Repair Database

Just to remind my problem - After running the "Main" Form, when adding a new record (pencil) there are All Objectives in each Category;
while I need 4 Objectives for Category 1; 3 Objectives for Category 2.

Thank you so much...

[June7]

Suggest not using WHID as fieldname for PK in every table. Each name should be unique. Like StaffID_PK.

Advise to remove hyphen character from EHRChartReview_Category-Objective. Spaces/punctuation/special characters (except underscore) will cause issues.

No need to link EHRChartReviewCategory table to EHRChartReviewObjective. Why is CategoryID field even in EHRChartReviewObjective table?

Now what exactly is this business process? What is being reviewed? When a chart review is initiated, do you need to select category/objective pairs? Will each review have multiple category/objective pairs? Perhaps relationship should be between EHRChartReview_Review and EHRChartReview_Category-Objective? Presume each staff can have multiple category/objective pairs and each pair can associate with multiple staff. A junction table to associate EHRChartReview_Review WHID and EHRChartReview_Category-Objective WHID may be appropriate.

[Hell_1934]

Suggest not using WHID as fieldname for PK in every table. Each name should be unique. Like StaffID_PK.

Advise to remove hyphen character from EHRChartReview_Category-Objective. Spaces/punctuation/special characters (except underscore) will cause issues.

No need to link EHRChartReviewCategory table to EHRChartReviewObjective. Why is CategoryID field even in EHRChartReviewObjective table?

What is being reviewed?
When a chart review is initiated, do you need to select category/objective pairs?Will each review have multiple category/objective pairs? Perhaps relationship should be between EHRChartReview_Review and EHRChartReview_Category-Objective? Presume each staff can have multiple category/objective pairs and each pair can associate with multiple staff. A junction table to associate EHRChartReview_Review WHID and EHRChartReview_Category-Objective WHID may be appropriate.

What is being reviewed?
This is the Client's (patient's) evaluation process.
Employee has to evaluate the Client, by entering Client's answers from the paper into the Objective-Category Forms
I removed the Clients table to simplify this DB. It was the same as the Staff (Employee) table.

When a chart review is initiated, do you need to select category/objective pairs? yes, pairs only.

Each category must be associated with the Objective: You can see it by clicking the + sign in the Category Form

Category 1 - 1. Orientation/Assessment -> a. Comprehensive orientation completed
b. Assessment process thorough & complete
c. Assessment process within the guidelines for timely completion
d. Individual's strengths, abilities, needs and prrefferrennccess, desired outcomes and expectations assessed during the
assessment

Category 2 - 2. Individual Plan -> a. Individual plan is completed
b. Goals & objectives are comprehensive and based on the assessment
c. Goals & objectives are based on the input of the person's served

Will each review have multiple category/objective pairs? yes. Each Review has to have ALL the Category/Objective pairs selected - either Yes/No/NA values
In this case - 1. Orientation / Assessment with its Objectives and 2. Individual Plan with its Objectives has to be reviewed