Hi,


I'm working with Access 2007 at a high beginner/low intermediate level. I have a parent form called frmStudent and a continuous subform called frmStatus. The purpose of the subform is to collect information on what grade the student is in and whether he/she is active in our program for any given year.

The column headings look something like this:

Year Grade Status



There are six years total, and that number is absolute and finite. What I'm trying to do is get the subform to automatically fill in the years like this:

Year Grade Status
1
2
3
4
5
6

The years will never change, and will be the same for every student. That is not the case with grade and status. I thought automatically populating the years would be simple, but I'm having a hard time. My first solution was not normalized. In the status table I had pk, fk, Year1, Year2, Year3, Year4, Year5, Year 6, Grade1, Grade2, Grade3, etc. This worked well for the form because each year, grade and status had it's own place. It did not work well when I tried to set up a parameter query tool. It became a nightmare.

I decided make a more normalized status table and put year, status, and grade as well as the pk and fk fields. This makes querying easier, but I can't figure out how to get my continuous subform to pre populate years 1-6.

I know six new records in the subform would need to be created in order for this to happen. I'm just not sure how to do it. I would want those records to be generated automatically every time a new record in the parent form is created. I'm sure this is possible with a combination of vba and append queries, but I'm at a loss on where to begin. Any help would be very much appreciated. Thank you!