Student Progress Database Application

OK, I found the problem and it wasn't your fault. Everything was set right, but there was some weirdness in that the query was reading the Text control for the month as a number rather than a text string. So it wasn't adding the Month value to the table. I fixed that by converting the value to a string. And then everything worked.

Also you will note that I entered the Month (for Center 1 & 12) as June 2016. You should enter Both Month and Year to distinguish from one year to the next.

I also made a couple of other changes. DOB did not belong in the Progress table it should only be in the Student table. 

I set the controls in the second subform so they can't be edited except for the score. So the user can just type in the score, hit enter and it will go to the next row to enter the next score. 

I also modified the code behind the Add Progress Records button so it doesn't display any warning messages.

So I reuploaded to My OneDrive. 

Note: I'm leaving on vacation today, so I probably won't be able to respond as quickly for the next week.

But I think you understand more of how this works now.

Hi Scott,

Here's the link to the database I've uploaded: https://1drv.ms/u/s!Aix108ZSfLMKhZ4OHy366HGDYxgnJA on OneDrive.  The database is known as Students Database. Please let me know what was not working.

Thank you.

Something is not right. If you followed my instructions correctly then you shoukd not have duplicates like that.

You can upload your file to my OneDrive and I will take a look and see if I can spot what is wrong.

Okay I changed the query into an append query, and the SQL is like so:

INSERT INTO tblStudentProgress ( CenterCode, StudentID, CourseID, GradeMonth )

SELECT DISTINCT tblStudents.CenterCode, tblStudents.StudentID, tblCourse.CourseID, [Forms]![Production_CenterDetails_Main_Form]![Text37] AS Mth

FROM tblStudents, tblCourse

WHERE (((tblStudents.CenterCode)=[Forms]![Production_CenterDetails_Main_Form]![CenterCode]));

and this output gives this:

There is still something wrong and needs to be fixed before we move on.

OK, so now we go onto the next step; add the column for the month to the query and test it again. 

Mth: [Forms]![Production_CenterDetails_Main_Form]![Text37]

So the SQL should now be:

SELECT DISTINCT CenterCode, StudentID, CourseID, [Forms]![Production_CenterDetails_Main_Form]![Text37] AS Mth

FROM tblStudents, tblCourse

WHERE CenterCode=Forms![Production_CenterDetails_Main_Form]![CenterCode];

If that still works then convert the query to an Append Query so the SQL should be:

INSERT INTO tblStudentProgress (CenterCode, StudentID, CourseID, GradeMonth) 

SELECT DISTINCT CenterCode, StudentID, CourseID, [Forms]![Production_CenterDetails_Main_Form]![Text37] AS Mth

FROM tblStudents, tblCourse

WHERE CenterCode=Forms![Production_CenterDetails_Main_Form]![CenterCode];

Then empty tblStudentProgress and try the button again.