Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform Problem (Version 2002/Service Pack 1)

    I have attached a database that was created for the purpose of entering in responses to a survey questionaire. Questions 1-3 and 7-8 have one response for each child in a family for a maximum of 3 students per family. Questions 4 and 9 can have multiple responses for each child. Questions 5-6 and 10-17 have one response per family.

    frmParent is based on the responses from a parent with separate subforms for each child. In order to have the entry screen match the order of the questions, I had to create two separate subforms for each child. When I enter data into the first subform for Child #1, the autonumber field of StuNum is filled in with the next sequential number. When I get to the second subform for Child #1, I would like to edit the record for the same student, but a different StuNum is assigned. Is there any way to link the two subforms together so that they are both looking at the same student record?

    I thought it might work by making the second subform properties to be Edit=yes, and Add=no but when I did that, the second subform failed to appear.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    I fear it will be difficult to get this to work correctly. At the least, you must change StuNum in tblStudentsBullied from an AutoNumber field to a Number field, and populate it yourself, in the Before Insert event of the three subforms at the top (sfmStudentsBullied etc.):

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.StuNum = DMax("StuNum", "tblStudentsBullied") + 1
    End Sub

    Put invisible text boxes StuNum1, StuNum2 and StuNum3 on frmParent, with control source =[sfmStudentsBullied]![StuNum] etc.
    Add StuNum / StuNum1 to the fields linking frmParent and sfmStudentsBullied, and analogously for the others.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    Attached is my file after following the directions you provided. The text color for all the objects which will not be visible in the final product are red in order to easily see that the Parent # (ParNum) and the Student # (StuNum) are working correctly. The problem now seems to be that the second subform wants to append the data as a new record rather than update the record so it just freezes and won't let you move off the record until you press escape to remove the data. Is there something else I can do to make this work? It just seems as if I am so close to having it do what I want, that I hate to give up. It would have been so much more simple if the survey form had been designed a little differently so that all the questions that pertain to these three students in each family were together.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    The sfmStudentsBulliedPart2 form had DataEntry set to Yes, so it can never display the same record as sfmStudentsBullied. Set the DataEntry property to No.

    The following code will synchronize sfmStudentsBulliedPart2 with sfmStudentsBullied:

    Private Sub sfmStudentsBullied_Exit(Cancel As Integer)
    Dim rst As DAO.Recordset
    With Me.sfmStudentsBulliedPart2.Form
    .Requery
    Set rst = .RecordsetClone
    rst.FindFirst "StuNum = " & Me.StuNum1
    If rst.NoMatch Then
    Beep
    Else
    .Bookmark = rst.Bookmark
    End If
    End With
    Set rst = Nothing
    End Sub

    You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... for this to work. In the attached version the above code has been inserted; you'll have do the same for the second and third child yourself.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    Everything is working as it should. I don't quite understand what the code is doing but it works. Thanks for all your help.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    The code I posted is the On Exit event procedure for the sfmStudentsBullied subform. It will run when the user clicks out of the subform to go to the next question. The event procedure uses the standard search code that is also used by the Combo Box wizard to find the record with the correct student number in the corresponding subform sfmStudentsBulliedPart2. The subform is requeried first, to make sure that it "sees" the new record (when a new record is created in sfmStudentsBullied, it is not automatically available in sfmStudentsBulliedPart2, you need a Requery for that).

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Problem (Version 2002/Service Pack 1)

    Thanks for taking the time to explain. It helps the next time a similar problem arises if I understand why I did something and I didn't just blindly follow a set of instructions.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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