Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: Select summary record subform1 and display full record subform2

    Hello all.

    I'm struggling to find an answer to this one short of what I already know.

    Previously I have had summary records on a continuous subform and clicked on it to open up the full record in a new form or pop-up form. Nothing stopping me doing that but I'm trying to be a bit cleverer than that this time if at all possible.

    This time my main form has both a summary data subform and a main record subform bound to it. Basically, click on the summary record in one subform and display the main record in another subform. My problem is setting focus on the 2nd subform and applying the condition without the functionality of the open form command.

    The Collection references are [Forms]![frmSchool]![sfrmPupilBulk]![Pupil#] for the first subform and [Forms]![frmSchool]![sfrmPupilIndividual]![Pupil#] for the second.

    Thanks for your help.
    Last edited by jonesyfella; 02-06-07 at 11:25.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would start by setting the RecordSource for Subform2 equal to something like:

    SELECT * FROM tblStudent WHERE 0

    I am not sure the source of you data, but include WHERE 0 to default to showing no records when the main form opens.

    Next in Subform1, for each field in the subform I would put code in the GotFocus event to change the RecordSource for Subform2 when you select any field in the record from Subform1.

    The code is going to look like:

    strSQL = "SELECT * FROM tblStudent WHERE Pupil#=" & Me.Pupil#
    Me.Parent.SubForm2.Form.RecordSource = strSQL

    I would try to perfect the code in one field of the subform then copy and paste to other fields.

    Keep in mind, by using Parent the code will only work when you open SubForm1 as a subform in another form.

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Thanks DC. I had a job getting that working but came up with a simple method in the end. Simply to have an unbound text box on the main form and have the Pupil# value pushed to the text box when the particular record was clicked in the summary subform. That then enabled me to use the link Parent/Child fields function on the subform displaying the full record. Bish bash bosh- job's a good'n!

Posting Permissions

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