Advanced Form (s) - needed advice or suggestion

[Hell_1934]

Hi All,
I have a dataset -

1 Fact table ChartReview:


ChartReview

ID	EmployeeFK	CategoryFK	ObjectiveFK	YesNoFK
1	22	1	1	0
2	11	1	2	1
3	11	2	3	1
4	22	3	6	2
5	22	2	5	0
6	22	2	4	1

4 Dimension tables 3_Employee, 4_YesNo, 1_Category, 2_Objective:

3_Employee
ID	EmployeeName
11	James Smith
22	John Doe

4_YesNo
ID	DescriptionYesNo
0	N/A
1	Yes
2	No

1_Category
ID	DescriptionCategory
1	Orientation
2	Individual
3	Progress

2_Objective

ID	CategoryFK	DescriptionObjective
1	1	Object1
2	1	Object2
3	2	Object345
4	2	Object556
5	2	Object666
6	3	ObjectLast

Here is their Relations Model (colored dots show the appropriate relation):





My goal is to create a Form or (Form + SubForm)- where user will add data as following -
User will select the Employee Name, and then - will proceed with the Category selection, then - will enter the Objective values (Yes, No or N/A) according to each Category associated with its Objective.


I created a Main Form (data source - ChartReview table):


and a Sub Form (data source - dimensions - Category, Objective tables):




But - my problems - I don't know how to connect them... (so the user would enter data into Main, and then would be transferred to a Sub and will enter Yes/No/N/A for each Objective)?
Also, I need my Yes/No/N/A field would be bound to each Objective, while now, unfortunately it's bound to only the 1st Objective...
And the last, I am not even sure the user will actually be able to fill out my SubForm, as it's created out of the Dimensions.



When I tried to enter or to change the info in the SubForm - the following error appears -



Please, HELP !!!

[June7]

Main form would be bound to Employees, subform bound to ChartReview, and comboboxes to select employee and objective.

CategoryFK would not be in ChartReview because it is associated with Objective.

Exactly what does Yes/No/NA indicate?

As you have this set up, Objectives is a lookup table. Objectives must exist for users to select from.

[Hell_1934]

Main form would be bound to Employees, subform bound to ChartReview, and comboboxes to select employee and objective.

CategoryFK would not be in ChartReview because it is associated with Objective.

Exactly what does Yes/No/NA indicate?

As you have this set up, Objectives is a lookup table. Objectives must exist for users to select from.

I created the Main Form using [ChartReview] Fact table data source.. Should I also bound this Form to the Employee table?
(Sorry, I am using Access seldom for these projects, sorry if asked something not smart)

Yes, No, N/A - are the text values in my 4_YesNo table. They indicate the response for every Objective value (the real data - are some type of Survey).
Real Objective example -
a. Comprehensive orientation completed User must select either Yes or No or N/A
b. Assessment process thorough & complete User must select either Yes or No or N/A
c. Assessment process within the guidelines for timely completion User must select either Yes or No or N/A
......

and so on!

Also, I must design my Objectives values as a sort of list, not as a drop down, because it's absolutely uncomfortable for users to select out of drop down list - out of 8+ amounts of real Objectives.
User should hover towards each Objective and possibly click or lookup - to select Yes or No or N/A.

[June7]

If main form is ChartReview then it should have comboboxes to select employee and objective and Yes/No. Sources for comboboxes would be Employee, Objective, YesNo tables.

No subforms.

Advise not to begin object names with number.

[Hell_1934]

If main form is ChartReview then it should have comboboxes to select employee and objective and Yes/No. Sources for comboboxes would be Employee, Objective, YesNo tables.

No subforms.

Advise not to begin object names with number.

But, as I mentioned above, it's a big problem in this case - just to have a Form with combobox for Objective!

There are 8+ real Objectives for each Category.

The goal is - to have Objectives displayed as a sort of List (ListBox?)
Example -

Objective1
Objective1
Objective3
Objective4
with yes or no or na choices in front of each Objective ....

[mike60smart]

Hi

Can you upload a zipped copy of the Db??

[Hell_1934]

Hi

Can you upload a zipped copy of the Db??

Yes, in 25 mon I will!

[June7]

Questionnaire/survey type of db is fairly common topic. Have you searched for examples?

[Hell_1934]

Hi

Can you upload a zipped copy of the Db??

Just added - DB.zip

This is just a replica ofthe real Project.
My goalis to create a Form or Forms, so that user will be able -

1. Toselect EmployeeName 2. To select one Category,after this - the appropriate Objectives should appear(associated with the Category)
3. Usershould check or select Yes or No or N/A valuesfor each Objective



For example -
Employee Name: John Doe

Category:Individual

Objective:
Object345 Yes or No or N/A
Object556 Yes or No or N/A
Object666 Yes or No or N/A

***
Yes / No / N/A values could be as CheckBox or a ComboBox - doesn't matter.
All this should go into the Main (Fact) table - ChartReview


Please help -)
Thank you!

[Hell_1934]

Questionnaire/survey type of db is fairly common topic. Have you search for examples?

I am going to search today...
If you have any suggestion of free (code, tables, Forms) accessible databases for my topic - Please, post their names here !

Thank you!

[June7]

Look at this one http://www.rogersaccesslibrary.com/forum/topic3.html

Every possible category/objective combination would have to be a record in Objectives. So 8 objectives for each category means 24 records.

You might make use of cascading comboboxes. Select category in first combobox then second combobox choices are limited by selected category. This is also a very common topic. Otherwise, have a single combobox (or listbox) that lists all 24 records.

Can also use code to create a 'batch' of question records in ChartReview for all or specific employee then user just needs to fill in Yes/No/NA answer.

[mike60smart]

Hi

If you select a specific Category and a list appears of all associated Objectives. Would you then select All or just some of the Objectives?

[Hell_1934]

Hi

If you select a specific Category and a list appears of all associated Objectives. Would you then select All or just some of the Objectives?

All Objectives should be selected and voted for [Yes] or [No] or [N/A] - for each Objective!
If I need to change or update my DB design - totally ok; Just I am not sure how...

as mentioned previously-

After selecting a specific Category, then - after all associate Objectives appear - user should be able to make a choice - for each (All) of the Objective - either Yes or No or N/A

For example -

Category:Individual

Objective:
Object345 Yes or No or N/A
Object556 Yes or No or N/A
Object666 Yes or No or N/A

[June7]

A set of records needs to be created in ChartReview for each employee/category combination. This can be done by user one record at a time or code can 'batch' create records then user later fills in the Yes/No/NA. Consider this action SQL in VBA.

CurrentDb.Execute "INSERT INTO ChartReview(EmployeeFK, ObjectiveFK) SELECT " & Me.cbxEmpID & ", ObjectiveID FROM Objectives WHERE CategoryID=" & Me.cbxCategory

This creates a batch of records for specific employee and category. Later user fills in Yes/No/NA answer.