dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] Access 2007 Append Continuous form with VBA

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Resolved [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!

  2. #2
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: Access 2007 Append Continuous form with VBA

    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?

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Access 2007 Append Continuous form with VBA

    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.

  4. #4
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: Access 2007 Append Continuous form with VBA

    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.
    Attached Files Attached Files

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Access 2007 Append Continuous form with VBA

    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!

  6. #6
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    Re: Access 2007 Append Continuous form with VBA

    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.
    Attached Files Attached Files

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Location
    Massachusetts
    Posts
    65

    Re: Access 2007 Append Continuous form with VBA

    Thank you so much for all your help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width