Populate FORM with values entered in another table

[giol]

Hi this is a simple descritpion of what i am trying to do:
I have a table called TblSubjects with 3 fields :
SubjectID,
SubjectName,
SubjectPoints.

SubjectID id the primary key and the user must add it manually using a number from 1-15 (The subjects must be limited to 15).

I have second table which is called TblGradesheet with fields:

GradesheetID
StudentData
ExamDate
SubjectNo1
ScoreofNo1


SubjectNo2
ScoreofNo2
...
SubjectNo15
ScoreofNo15
TotalScore

On each SubjectNo1-15 field i used lookup wizard and criteria to select a SubjectName based on the SubjectID.

I created a form FrmGradesheet where i input the test results for each student on each subject. My problem is that the SubjectNo1-15 fields are empty when i open the form and i have to select from a combobox the entry although there is only one choice for each box since i used the criteria in the lookupwizard.
What is the best way to tell the TblGradesheet and the FrmGradesheet to display the SubjectName where SubjectId = 1 for SubjectNo1 (and so on) by default? The whole purpose is to allow me to change SubjectNames from a form instead of going to design mode because i expect other teachers will want to change the Subjects they are teaching and testing and the points as well.
Thank you.

[alansidman]

This would be easier to analyze if you uploaded your db with sample data. Visualizing your request is hazy at best.

[rpeare]

also looks like a non-normalized structure as well which may be hampering you. a sample db would help.

[orange]

I agree with Alan that the request is a little vague. It appears to me that your second table TblGradesheet is not Normalized.
I would suggest something along this:

GradesheetID
StudentID
ExamDate
Subject
Score

where each Subject and Score constitute different records in the table. Each uniquely identified by the combo
GradesheetID, StudentID, ExamDate


OOOops: I see rpeare responded while I was editing.

[giol]

i am uploading a small sample of the Db. I use the Me.cmboboxname.ItemID(0) and it kind of works but i bet there is more professional way of doing this.
While you are it can propose of good way to calculate and save the final score of each test?
https://drive.google.com/file/d/0B3E...it?usp=sharing

Also i have to clarify something, each gradesheet of a student on a single day of the exam includes all 15 subjects and all must be graded. Each subject is graded with check boxes (5 checkboxes for subjects that correspond to the percentage of the max subject points the student achieved e.g. the 5th check box in subject2 means all subject points (12) will be added to the total. The total score is derived by the sum the scores in each subject.

[rpeare]

I don't understand this form.

1. your two checkboxes aren't attached to anything, what are they supposed to do, just indicate the student got a 100% score? if that's your intent that's not what they are doing.
2. don't store your totalscore, storing calculated values is a bad idea and you can score any given set of records when you run a query/report/form
3. you aren't giving your users the ability to enter 'percentage of the max subject points'. You are forcing the user to select from a list that only has the maximum score for a given subject.
4. your combo boxes as you have them assume you will never have any more than 15 classes, if that's correct, you're fine, otherwise this form will not work for all situations because you are tying your combo boxes to classes where the ID are 1 and 2 respectively.
5. your relationships are not set up correctly (I don't use relationships, you don't actually need them and I find they hamper more than help more often than not). You are linking tables by fields that aren't related to one another.

I'm enclosing an updated version of your database with a very simple data entry form, I didn't do a whole lot of error checking but the basic function is there.

grades4.zip

note the structure of testing dates/testing scores is normalized.

[giol]

Thank you rpeare.
The check boxes are not a finished product but more of test. I will have 5 checkboxes next to each Subject where the teacher will tick one of the 5.I intend to use a simple "if check box 1 is ticked then the subject score is subjectpoints / 5" logic.
I dont intent to have more than 15 test subjects. The names may change but the students will be graded on 15 subjects.
Your data entry form looks way better than mine but how can i make it display all the subjects always. If you read my previous post the teacher must grade all 15 subjects on every date the student is evaluated, so i prefer all the subjects to be visible and each to have 5 checkboxes next to it in order to make the form easy to fill with just ticking the checkboxes (after selecting the student ofcourse).

[rpeare]

When doing data entry if you can confine your data entry to keyboard input it is far faster and more efficient (easier) than having to engage the mouse. Stay away from checkboxes for data entry if you can avoid it. If your evaluation has a set of discrete values (20, 40, 60, 80, 100) you can put those in a combo box and base it on a table so your users have a limited set of evaluation items they can enter.

You can perform logic checks on the basic form to find out if there is a certain subject/score that is missing from your data entry (as I said, I didn't do a lot of error checking, for instance right now you could put in the same subject 5 times and have it be valid) your 'error' checking could be done when you select a testing period through code and after adding a new record, or updating a record.

[giol]

I hear what you are saying in it seems reasonable. I opted for the check boxes because after filling each gradesheet i will need to print it and the standard format we use now is with check boxes. Maybe i could follow your way and then find a way to create a report that looks like the format we use and somehow tick the boxes corresponding to the grade of each subject.

[rpeare]

absolutely you can you can do it either via an option group or with a formula on a report or in a query.