Student Pre-Test and Post Test Skills Tracking Question

[June7]

You did not provide an attempted form in the db so can't analyze what you had a problem with.

Options:

1. form/subform arrangement - main form bound to tblStudents and subform bound to tblTestResults with combobox for skills
This will allow to navigate by student on main form, can view/edit existing records and add new student and their skills.

2. form/subform arrangement - main form bound to tblSkills and subform bound to tblTestResults with combobox for students
Functionality similar to option 1.

3. one form bound to the query - don't allow edits of the tblStudent or tblSkills info (set textboxes as Locked Yes, TabStop No), just the tblTestResults fields
Can have combobox for students and combobox for skills to create new records.
Set sort order criteria in the query.

SELECT tblTestResults.StudentID, tblStudents.[Last Name], tblStudents.[First Name], tblSkills.SkillDescription, tblSkills.SkillID, tblTestResults.PreTestDate, tblTestResults.ResultPre, tblTestResults.PostTestDate, tblTestResults.ResultPost
FROM tblStudents RIGHT JOIN (tblSkills RIGHT JOIN tblTestResults ON tblSkills.[SkillID] = tblTestResults.[SkillID]) ON tblStudents.StudentID = tblTestResults.StudentID
ORDER BY tblStudents.[Last Name], tblStudents.[First Name], tblSkills.SkillDescription;

[cohnhead]

I have tried to create a form based on Option 1 and Option 3. I was able to create option 3, but I can't figure out how to create the combo box for skills in either option.

Here is what I have so far (See attachment)Classworks Skills Tracker.zip.

[June7]

Forms do not show combobox for either student or skill.

Combobox properties:

Name: cbxSkill
ControlSource: SkillID
RowSource: SELECT SkillID, SkillDescription FROM tblSkills ORDER BY SkillDescription;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";3.0"

Name: cbxStudent
ControlSource: StudentID
RowSource: SELECT StudentID, LastName & ", " & FirstName AS FullName FROM tblStudents ORDER BY LastName, FirstName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2.0"

The StudentID fields of Option1 do not need to be visible to users.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Note that I do not show spaces in LastName, FirstName.

[cohnhead]

Forms do not show combobox for either student or skill.

Combobox properties:

Name: cbxSkill
ControlSource: SkillID
RowSource: SELECT SkillID, SkillDescription FROM tblSkills ORDER BY SkillDescription;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";3.0"

Name: cbxStudent
ControlSource: StudentID
RowSource: SELECT StudentID, LastName & ", " & FirstName AS FullName FROM tblStudents ORDER BY LastName, FirstName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2.0"

The StudentID fields of Option1 do not need to be visible to users.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Note that I do not show spaces in LastName, FirstName.

I have no idea how to get the combo boxes to work properly. The last database that I posted is as far as I have gotten.

[June7]

I provided the property settings for each combobox. Did you try?

[cohnhead]

I provided the property settings for each combobox. Did you try?

Hi June,

Ok I worked on it some more, but I know I am doing something wrong. Here are all three options with combo boxes.

[June7]

frmOption1
The Skills combobox needs to be in the subform, not on the main form.

frmOption2
The Student combobox needs to be in the subform, not on the main form.

frmOption3
This appears to be properly structured.

[cohnhead]

frmOption1
The Skills combobox needs to be in the subform, not on the main form.

frmOption2
The Student combobox needs to be in the subform, not on the main form.

frmOption3
This appears to be properly structured.

How do I add a combo box to the subform? When I add one, I can't see it in form view. The subform shows up as a table without the combobox.

[cohnhead]

Ok. Now I have the combobox in the subform, but it doesn't seem to change the results when I change the skill in option 1.Classworks Skills Tracker.zip

[June7]

It does for me.

[Azharkhalil01]

I am a expert in databases now, I have been studying it like 1 year and 5 months, so ask me I know a lot of things and I suggest you to keep save your data every time when you add the records in table.
Primary key is really needed in tables if you wants to use relationships in 4 kinds of table.
Don't forget to use input mask it will make your data more accurate and it only allows entries that you want in a field.