Automatically changing previously entered values when a related field is updated

[Serendipityww]

On my form (and in an underlying table and query), three of my fields are GradeLevel with a value such as "PSA" (preschool a.m.), GradeCaption with a value of "Preschool Morning", and the third field is GradeSort with a value of "001" because sorting on GradeLevel would not give my a list of students starting at preschool and ending at 8th. The GradeCaption and GradeSort get filled in with a Case Select. The problem is that when I update the GradeLevel field for the next school year (with an update query), the Grade Caption and GradeSort stay the same. I thought that it would work if the Case were triggered by Change Event in the GradeLevel field.

 

[Abby N]

What you may want to do is break out GradeCaption and GradeSort into a separate table and relate them back to the original table. That should solve your problem and have the additional benefit of reducing your overall file size. Access even provides a handy tool for exactly this situation. Go to Tools>Analyse>Tables and follow the instructions provided by the wizard. Though I recommend you first save a backup of your original table, just in case.

Alternatively, you could modify the update query you'd mentioned to update those fields as well. I hope this helps. If you have any problems let me know. Good luck.

~Abby