Student Progress Database Application

There's been an issue with replying here, otherwise I would have replied sooner.

Your tblCourse however is wrong. It should be:

tblCourse:

CourseID (PK autonumber

CourseName

I've told you this several times. Each course should be a record in a table, not a field.

The problem here is you need a better understanding of how comboboxes work. When you used the Wizard I don't think you did it quite right. Comboboxes are powerful tools. They allow you to store a value while selecting a different value.

So, for example, when selecting a course. The user sees not the CourseID, but the Course name. They select a Course Name, but CourseID is what is stored in the table. The way this works is with using the properties of the combobox properly.

First is the Rowsource property. This will be a SQL statement that lists all the columns you need in your list. Second, Is the Bound Column property. This indicates what column in the Rowsource will be stored when an item is selected. The third is the Column Count property which needs to match the columns in the SQL statement. The fourth and final one is the Column Widths property. This controls what the user sees when making a selection.

So lets go back to the CourseID for a moment. The relevant properties would be:

RowSource: SELECT CourseID, CourseName FROM tblCourses ORDER BY CourseName;

Bound Column: 1

Column Count: 2

Column Widths: 0";1.5"

The last is the key. A Combobox displays the first non zero width column once selected. So with this configuration, the user will see and select from the Course names, but the CourseID is stored in the FK field.

Similarly with the Search combo. The first column should be set to 0 so the user sees the Center names.

With Student names you do something a little differently. The Rowsource will use a calculated column to display the student name:

SELECT StudentID, StudentLname & ", " & StudentFName AS Student FROM tblStudents ORDER BY StudentLname, StudentFName;

Again the Bound Column is 1, Column count 2, and Column widths is 0";2"

Note the width of the second column can be anything you want depending on the data to be displayed. So with this, the user sees and selects from the student names, but the ID is stored.

If you go back to form as I suggested and change your comboboxes as I've just indicated, the user will not have to remember any codes, the will be selecting from the descriptive names. Also, I said to use a Tabular layout for the subform. This makes the form in Continuous form mode rather than datasheet. You can then easily hide the StudentProgressID and CenterCode as the user doesn't need to see these.

Hi,

I've tried to do my homework on this myself and made efforts to make it work somehow. I've got some success and just wanted to let you know about what I did. I put in the tblCourse from the navigation bar and made it into a Sub Form. Then I edited the recordsource property and designed a query to filter records on the StudentID on the main form. This gave me some functionality in filtering records on both the Sub Forms. Then I changed the data entry mode on the second form to a 'yes' from the default 'no'. I've attached some screenshots and the recordsource I'm using:

Here's the recordsource I'm using in the second Sub Form:

SELECTtblCenterDetails.CenterCode, tblStudents.StudentID, tblCourse.CourseHindi, tblCourse.CourseEnglish, tblCourse.CourseMath, tblCourse.CourseScience, tblStudentProgress.GradeMonth, tblStudentProgress.Grade FROM (tblCenterDetails INNER JOIN tblStudents ON tblCenterDetails.CenterCode = tblStudents.CenterCode) LEFT JOIN (tblCourse RIGHT JOIN tblStudentProgress ON tblCourse.CourseID = tblStudentProgress.CourseID) ON tblStudents.StudentID = tblStudentProgress.StudentID WHERE (((tblCenterDetails.CenterCode)=[Forms]![tblCenterDetailsONE_TIME_TEST_MAIN_FORM]![Combo15]));

However, there is only one problem now. I can't add marks into the Courses on the second Sub Form even though i set the data entry mode to 'yes' from a default 'no'. So I changed it back to the default value of 'no'.

Now do you think my effort was worth any value???

***Personal information deleted by the moderator. Please see the Microsoft Community Frequently Asked Questions for more information on how you can protect your privacy.***

Hi Scott,

Nice to see you back. I've followed your instructions to the letter.  See the screenshot attached and let me know if I did anything wrong. I really wish to make this application work as intended. The screenshot will show the main form bound to the Centers table with a search combo box and another sub form below bound to the Progress table.

The only problem I see now is having to remember the StudentID's of all the kids. Plus I where and how does the data entry person put in the marks for each student in the sub form?

OK, First, Lets make sure you followed my instructions. Use the wizard and create a form bound to the Centers table. Then, while in Design mode, use the combobox wizard and choose third option to create a "search combo". This combo will allow you to select a Center and bring up that record. Test this out. 

This should work very easily as the wizards do all the work. 

Once you have that working, you embed a subform bound to the Progress table.

I would go with a form that requires minimum amount of coding, though I am not allergic to coding if that is really required to make this happen.

Thank you.