[RESOLVED] Access 2007 Append Continuous form with VBA

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!

What you should do instead of making the years as columns in one table is to split it off into a subtable and join them with a relationship. Then when you add a student, automatically populate the child table with 6 entries. Does what i said make sense?

Thank you so much for responding! What you're saying does make sense. I'd just like to understand it more fully. Would the year be the only column in the split table you're suggesting? Would the foreign key be the table with the grade and status? Would I then join those two tables in a one to many relationship? Would I need vba code to populate the years, or would I be able to do it some other way? Again, thanks for your time. I've spent hours trying to research and figure this out and have had no luck.

Your parent table would look like

Code:

---

ID (autonumber), whatever else

---

the subtable would be

Code:

---

ForeignKeyField, Year, Grade, Status

ForeignKeyField should be set to be required and indexed. Do not assign a primary key.

---

The tables would be linked together one to many ID-->ForeignKeyField.

Then in the main form, add the code to the Form.Current event.

Code:

---

Private Sub Form_Current()
    Dim rst As DAO.Recordset
    Dim i As Integer
   
    If Me.NewRecord Then
        txtName = "New Student"
        DoCmd.RunCommand acCmdSaveRecord
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM [SubTable] WHERE [ForeignKeyField]=" & Me.ID)
        If rst.EOF Then
            For i = 1 To 6
                With rst
                    .AddNew
                    ![ForeignKeyField] = Me.ID
                    ![Year] = i
                    .Update
                End With
            Next
            Child0.Requery
        End If
    End If
End Sub

---

I've attached an example database. PS - I forgot to set the foreign key to be required and indexed in the example db.

dmaruca,

I can't thank you enough. I spent some time going over your advice and following the sample database you sent, and that was exactly what I needed. It works perfectly now and will offer a huge qualitative leap forwad in my database. I so appreciate you taking the time to solve my problem.

I just have one more question that's more cosmetic than anything else, so if nothing can be done about it, that's cool. I'll live with it.

Every time the new record icon is clicked, a new studentid generates, along with the 6 records in the status table. Is there any way I can prevent the new studentid from generating if let's say the first and last name is left blank? I think this would prevent blank records if someone accidentally clicks on new records but doesn't want to add one. Would I put a .Undo as part of the Form_Current() event on the main form, or would it be a BeforeUpdate event? Am I on the right track? Again, thank you!

No problem. Yes you can do what you are asking. I have modified the code and the subform and maybe it is to your liking. You could make the year generation automatic, but that may cause confusion to whoever is using it. I added a button that will make it more transparent in its behavior. Once you type your first character and the form becomes "dirty," you can press the button.

The problem with making it automatic is that something has to be in the master table in order to add it to the sub table, so the form must save the data before you can insert the years. You can either add garbage data like I did in the first example, or you can wait until there is something really ready to be saved like in this one. You could make it autogenerate on the first character they type, but what if they did it on accident? Let me know if you have any more trouble.

Thank you so much for all your help!