Results 1 to 6 of 6
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have a form and subform. The forms shows details about a student. The subform shows info about the subjects they have taken.

    Most users (teachers) are particularly interested in one subject. So if they are looking at Maths on the subform for one student, they are most likely to want to look at Maths for each student as the move through the records on the main form. So I am trying to avoid them having to move to the correct subform record each time they move to a new student.

    I have code in the On Current event of the main form that will find a record in the subform, using a subject code for the search criteria. (If I hardcode a subject code this works OK.)

    My question is: what event can I use to find the subject code from the current subform record , as they move to a new record in the main form.
    The OnCurrent event is too late. By this stage the subform has already moved back to the first record.

    I am providing Navigation Buttons on the main form (as one way to move between records). I mention because I can't access the code behind them.

    I have added a demo db, which has MATH8 hardcoded as the subject code to find.

    [attachment=86764:students.zip]
    Attached Files Attached Files
    Regards
    John



  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,

    Add a combo to your form with as recordsource the studentsubjects.
    In the After Update event of the combo, set the recordsource of the subform to the same sql but with a where condition : Where StudentCode = Me.Combo
    Move to your recordset as you want.

    See modified db in the attachment.
    Attached Files Attached Files
    Francois

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks but that does not quite do what I want.
    I want all the other other subjects to remain visible in the subform, (so that the teachers can look at the other subjects as they move through the various students). In the real db, there is loads of data about each subject. Even if they do nothing else, they would scan the list and see the results.

    I could do what I want by removing the built in navigation buttons and replace them with custom navigation buttons, but I was hoping to avoid that.
    The file attached here demonstrates that using very crude custom navigation buttons. Can I do the same thing with the built in navigation buttons?
    [attachment=86770:studentsa.zip]
    Attached Files Attached Files
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can this one be of any help ?
    I this one I change the order of tblStudentsubjects (in query1) so that the selected code is at the top of the recordset.
    See attached db.
    [attachment=86772:students.zip]
    Attached Files Attached Files
    Francois

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks Francois

    You have given me an idea that, I think, provides a workable solution.
    I don't want the users to have to do anything special to make this work. It should just happen as they use this form in the normal.

    So I have replaced your combo box with a hidden text box, and I use the On Click event for various controls on the subform to set the hidden text box's value to the current subject code.
    The On Current event of the main form can then use this text box to find out what subject to find.
    Here is my third version.
    [attachment=86783:studentsb.zip]
    Attached Files Attached Files
    Regards
    John



  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Glad I could help you to find a workable solution
    Francois

Posting Permissions

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