Placing a value in a field in a continuous form from another field

[andy49]

Getting myself in a bit of a mess here and hope someone can help

the form FrmTierEntry should list pupils names with an empty Tiertaken which it gets from another table as part of the form query



Id like that to be filled by default with a tier which is linked to another field (defaultTier)

Pressing a command button (I hope) will then allow my staff to alter the TierTaken field.

But I cannot seem to find any solution. And the more I look at it, the more I get lost. Hope someone will look with a fresh pair of eyes and get me back on track

Andy

DBschoolexamPapersvtest.zip

[June7]

A form should be set up for data entry to one table. Which table should FrmTierEntry edit - TblPaperStudent?

You have this form bound to a non-updatable query, can't edit data or add records via the form. The query includes tables whose fields are not even referenced.

The code is attempting to edit data that doesn't exist. There is only one record in TblPaperStudent. Your query gives the appearance that every student has an associated record in TblPaperStudent when they do not. Examine the studentID_FK field of the query and you will see that it is empty except for only 1 student because there is only 1 record in blPaperStudent.

TblPaperStudent appears to be a junction table for a many-to-many relationship of TblTestPaper and TblStudent. Conventional options for data entry design:

1. single form bound to TblPaperStudent and comboboxes to select paper and student

2. main form bound to TblStudent and subform bound to TblPaperStudent with combobox to select paper.

3. main form bound to TblTestPaper and subform bound to TblPaperStudent with combobox to select student.

Even if each student did have a related record in TblPaperStudent, the code would fail because the query is not editable therefore the RecordsetClone object is not editable.

Other tables ('lookup' tables) can be included in a form RecordSource to display related info but should not allow edit of fields from those other tables on the same form.

[andy49]

Thanks June. But pupil names are in one table and the level at which they will sit an exam (foundation or higher) will vary from pupil to pupil and may change from month to month too.

Is my setup ok to be able to do this (leaving some tables out of the query for example) or should I approach this part of the problem a different way.

Thanks for your help btw.

[June7]

I would need a better understanding of business process and data relationships. For instance, don't really understand why tier value is in both TblTestPapers and TblPaperStudent.

Also, you say you want to change the value of TierTaken, yet your code is trying to change value of defaultTier field. I really don't think you want to change values in tblclasses.

I think what makes the query nonupdatable is the linking between TblPaperStudent and tblCalendar and tblTestPaper. Remove those tables and what is left is all editable.

[andy49]

Happy to provide that June. I'm a teacher trying to automate a process which is currently done ad hoc using excel.

Pupils in year 9,10,11 take exams at the end of each academic session (4 months)

Exam papers vary from session to session but are always foundation or higher. The list of papers grows as the exam board releases new papers.

Staff (my team) select whether a pupil is foundation or higher then students take those exams (3 papers)

Staff then enter results (out of 80 for each of 3 papers) which are stored on access.

These totals out of 240 are also graded by access

If you need other details I'm happy to provide them.

[June7]

Do you want to maintain history in this db - multi-session and multi-year data?

So your tblStudentPaper associates studentID and paperID and should probably also have TestDate and TestScore fields. Tier is already associated with a paper in tblTestPaper. Select the appropriate test/tier record and save that paperID into tblStudentPaper. Why would the tier taken be different from the assigned tier value of a testpaper?

The 3 options for structure of data entry form for entering records into tblStudentPaper still applies.

[andy49]

Many thanks June. I definitely want to maintain history so will add a date field

The tier taken will indeed be the same. So guess only one needed in tables.

I was thinking of using insert query to populate tblstudentpaper. Theory would be if a pupil was in yeargp 10 and selected as foundation then he must be taking paperid_fk 1.

Will post updated version tomorrow if I may. You've been a huge help already.