Entering data using a partial prefilled form

[stevehoyle]

I would like users to be able to enter data on a form that I prefill as much as possible. This would be done by user entered filters defined at the top of the form. If no filters are present, then I present 1 blank record for them to fill. But if a filter is entered for a specific column then I retrieve all of the data that matches from the respective parent tables, based upon the filter, and then present a single child record for every match, with the proper child fields blank that they then fill. e.g. a teacher is entering grades for every one of her students in a specific section. I want her to enter a grade date, the section, and her ID. I then present a table with a row for every student in that section. Each grade record (row) has the date, Teacher Id, and Section prefilled from the filters the teacher specified, and then I fill these other fields from the Section table: Student ID, Class, etc. The grade fields are left blank for her to fill. When completed she presses a "button", and everything is saved. Thanks!

[June7]

That requires creating a set of records in the grades tables with an INSERT sql action then refreshing the form to display the newly created records. Something like:

CurrentDb.Execute "INSERT INTO gradestable([datefield],TeacherID, Section, StudentID) SELECT #" & Me.datetextbox & "# As D, " & Me.TeacherID & " As T, " & SectionID & "As S, StudentID FROM tablename WHERE TeacherID=" & Me.TeacherID & " AND Section=" & Me.Section

[stevehoyle]

Great, thanks. I'm thinking of doing what you suggest but inserts into a temporary table. Then reviews can be done and the user can do a final commit, and I will then copy the temps to the Grades table. Seems safer. Sound good, or do you suggest something other?

[June7]

If you want to use temp table, go ahead, but I wouldn't.

Another approach involves a 'parent' table that has the class info (teacher, section, subject, year, semester) then the grades input into child table (classID, studentID, grade).

[stevehoyle]

OK, thanks again.

[June7]

Actually, after thinking about this some more, you should already have a table of records that associates students with classes. These records should have been created when the student was assigned to the class. Can just include the grade field in that table and go back to the records and fill in the grade later. No need for the INSERT sql as the records already exist.

Have you looked at the Students template database provided by MS?

[stevehoyle]

A teacher will be entering grades for perhaps 60 or more students. Each grade record contains the Teacher ID, Student ID, Date, and 5 categories for Grades. The teacher can enter a record at a time and fill every field. However, I wanted a datasheet which already had a record for every grade she would enter. I already had the Teacher ID and date prefilled. I then wanted to prefill the student Id based upon criteria she entered in the filter. If she entered class, I would show one row for every student in that class, if she entered Section I would show one row for every student in that section, etc. The Teacher then only has to fill in the grade categories with a numeric for each row. She doesn't have to fill the fields I can fill automatically. At least, that is my desire.

Thanks for all your help. (you are also helping with my other problem, the form with the Allen Browne code).

[June7]

Okay, comments still apply. What have you attempted? Any success?

[stevehoyle]

I’m trying to prefill a template for teachers to enter student grades. The teacher will identify the Class, and/or Section and I will retrieve every student in those classes and/or sections. Each student will appear on a row in the template and the teacher will then fill in the grades.
I plan to retrieve the student info from the existing Student table and write this data into the EnterGrades table. After the teacher fills in the grades I will write these records from the EnterGrades table into the master grades table. I’m having a problem figuring best method – query, form, what type. I tried to addsome sql codeto a form, but I couldn’t get it to work in VBA. It seems relatively straight forward, I just can’t quite see the solution. I’m first time Access user. Thanks

[June7]

At what point and how do you associated students with a teacher or class? Maybe all you need is to filter existing records. Since I don't know your data structure I can't be specific about what you need to do.

[stevehoyle]

Some clarification:
User will enter 3 mandatory parameters and up to 3 more optional parameters, then click the GetGrades button.
Based on these parameters I read the desired records from the STUDENT table and write to the ENTERGRADES table. (Is ENTERGRADES table a temporary table? Multiple teachers could be entering at the same time. I think I can still control this because each teacher has a unique ID in every record, but is it better to get a temporary table for each teacher as she invokes this process and then delete it when done?) When writing to the ENTERGRADES table I populate fields from the STUDENT table and 2 of the parameters that the user entered.
The user then enters the grades into each record that was written to ENTERGRADES.
Finally, the user clicks the SaveGrades button and then I write every record that had grades entered from the ENTERGRADES table to the GRADES table. I then delete the ENTERGRADES table (if temporary) or the records for this process if not a temp table.
Questions: How best to perform these actions? Is it all 1 form that references those 2 tables? Should there be a query against the STUDENT table and an append query using ENTERGRADES and GRADES tables and then 1 form that references those queries? Not sure how to put this all together. Thanks

[stevehoyle]

Data Structures: The STUDENT table contains each student and the class, section and English Level they are in. I read this table based upon the class, and/or section, and/or English Level that was entered by the teacher and write 1 record per student to the ENTERGRADES table. This table does not contain class, section, or EnglishLvl. This table has the student ID, the TeacherID, the Grade Date and the Grade. Thanks

[stevehoyle]

Based on latest feedback - I'm now thinking of eliminating the ENTERGRADES table and just adding a boolean column to the GRADE table. I'll add the "enter grades template" to this table and I will toggle a boolean column (in the GRADE table) to the "entering" value. After teacher enters grades into these new records and clicks the SaveGrades button I will toggle to the "done" value. (I am having a bit of a problem when I tried to insert SQL into my VBA). Then I'll create the form as you suggested. I already have it with the unbound controls for user entered data. I'll create a new query for joining the STUDENT and GRADE tables, and reference this query in the form. How do I use this 1 form for everything? It references the query and the GRADE table? Here is the data structure.

Tables:
Department		Staff		Grade		Student
Department	->	Department		StuNumber	<-	StuNumber			4 Tables:
Notes		StfInitials	->	StfInitials		Class	<-	Class	Class
		StfLastName		GradeDate		Section	<-	Section	Section
		StfFirstName		English		EnglishLvl	<-	EnglishLvl	EnglishLvl
		StfMiddleName		Responsible		Course	<-	Course	Course
		StfLoginID		Behavior		StuLastName
		StfPassword		Respectful		StuFirstName
		StfEMail		Punctual		StuLoginID
		Title		Final(boolean)		StuPassword

[June7]

I am confused, why is English a field in Grade table? What about Math, PhysEd, Art? Can students have more than one class? Explain more about the nature of the business/org.

[stevehoyle]

It is a private Catholic boarding school, students from South America. It isn't traditional grades (for this specific application), but the concepts (and coding) would be the same. English means a numerical score for their consistency in speaking English. Other grades are for comportment. But it still is a classic Grade Book app. Doesn't matter if grade is for Algebra, or Politeness. Individual grades are still assigned, individual grades are then averaged over a defined period. Grading system is exclusively numerical, no letter grades. The 5 fields in the GRADE table are all fields for grades. The only data in GRADE table is unique data for each student, all repeating data comes from their own respective tables. No tables have repeating data, it is all relational.