Cascading Combo Box - Form, connecting the Sub Form

[June7]

So if every chart review must have a full set of category/objectives, consider:

tblReviews
Review_PK
Client_FK
Staff_FK
ReviewDate

tblCategoryObjectives
CatObj_PK
Cat_FK
Obj_FK

tblReviewDetails
Review_FK
CatObj_FK
Response

Lookup tables for clients, staff, categories, objectives, responses in addition to the above.

One approach is to batch create tblReviewDetails set when review record is created. Then staff just needs to enter responses. In VBA, like:

CurrentDb.Execute "INSERT INTO tblReviewDetails(Review_FK, CatObj_FK) SELECT " & Me.tbxReview_PK & ", CatObj_PK FROM tblCategoryObjectives"

Or you can do cascading comboboxes and let staff create records in tblReviewDetails one at a time.

[Hell_1934]

So if every chart review must have a full set of category/objectives, consider:

tblReviews
Review_PK
Client_FK
Staff_FK
ReviewDate

tblCategoryObjectives
CatObj_PK
Cat_FK
Obj_FK

tblReviewDetails
Review_FK
CatObj_FK
Response

Lookup tables for clients, staff, categories, objectives, responses in addition to the above.

One approach is to batch create tblReviewDetails set when review record is created. Then staff just needs to enter responses. In VBA, like:

CurrentDb.Execute "INSERT INTO tblReviewDetails(Review_FK, CatObj_FK) SELECT " & Me.tbxReview_PK & ", CatObj_PK FROM tblCategoryObjectives"

Or you can do cascading comboboxes and let staff create records in tblReviewDetails one at a time.

Ok - some clarifications needed:

Clients, Staff lookup tables will be related to tblReviews.
What about Categories, Objectives lookup tables? Will they be related to tblCategoryObjectives (Cat_FK, Obj_FK) ?

I need to do a cascading comboboxes and let staff create records in tblReviewDetails one at a time!

For that - what will be my Main Form and a Sub Forms? (please, correct if I am wrong)

I see it like this:

Main Form: (where user will select Staff, Client info, date)
tblReviews

Sub Form 1: (where will be Category drop-down button - field - Cat_FK)
tblCategoryObjectives

Sub Form 2: (where will be the appropriate Objectives - field CatObjFK with Responces (Yes or No or NA) - field Responce)
tblReviewDetails


Thank you!

[ArviLaanemets]

My 5 cents!

[Hell_1934]

My 5 cents!

Thank you, I just looked briefly... this is great
But - my Program table absolutely can not be tied with Category/Objectives. It is a separate table in the main WH system, in SSMS.
Also, the Main Form can't add the new user. Or - at least I don't know - how...

[isladogs]

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

I can see that now though it wasn't obvious before. I'll leave you in the capable hands of Arvil and June

[Hell_1934]

I can see that now though it wasn't obvious before. I'll leave you in the capable hands of Arvil and June

- that's me with this project ))

[ArviLaanemets]

But - my Program table absolutely can not be tied with Category/Objectives. It is a separate table in the main WH system, in SSMS.
Also, the Main Form can't add the new user. Or - at least I don't know - how...

Will this do better? (I mean form fMain2)

[June7]

The db you provided does not include tblClients nor a Client_FK field in any table so relationship is not clear. I assumed a client can have multiple reviews and each review ID is for a single client.

With my suggested tables, arrangement could be:

Main form bound to tblClients with a subform bound to tblReviews and a subsubform bound to tblReviewDetails with comboboxes for selecting category and objective.

If you need to add a new staff or category or objective 'on the fly' during data entry, that's what combobox NotInList event is for.

Quite possibly tblObjectives lookup table is not needed. If an objective can associate with only one category, then just enter the objective descriptions into tblCategoryObjectives.

[mike60smart]

Hi Elena

Here with the Cascade sorted

[ssanfu]

Here is my offering. I normally use queries for form record sources, but didn't want to change too much. I did rename a lot of objects. And I split the staff name into first and last names.


Good luck with your project......

[Hell_1934]

Here is my offering. I normally use queries for form record sources, but didn't want to change too much. I did rename a lot of objects. And I split the staff name into first and last names.


Good luck with your project......

Thank you, I'll look at your version and, if possible, will ask questions (if any). Thank you so much!