Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How would I filter a subform from a combo box? (Access 2000)

    I want to make a form where you can select a grade from a combo box, then it shows in the subform all the students in that grade. I'd like to see all the students in the subform at first when I go into this form, then show the students corresponding to the grade I select. I also want to run a query that puts a check in a yes/no box when you select the grade from the combo box. I hope this is clear enough.

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

    Re: How would I filter a subform from a combo box? (Access 2000)

    In the afterupdate event of the combo, you can build an sql string for the recordsource of your subform. Something like :
    strSQL = "Select Fiedl1, Field2 From MyTable Where Grade = " & Me.MyCombo
    Me.SubformControl.Form.RecordSource = strSQL

    If your grade is as text field use :
    strSQL = "Select Fiedl1, Field2 From MyTable Where Grade = " & chr(34) & Me.MyCombo & chr(34)

    For the yes/no field you could run an update query.
    strSQL ="UPDATE Table1 SET Table1.MYyesNO = True WHERE (((Table1.Grade)=" & chr(34) & Me.MyCombo & chr(34) & "))"
    DoCmd.RunSLQ strSQL
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How would I filter a subform from a combo box? (Access 2000)

    It works great, except that when I select a different grade from the drop-down combo box it isn't sorted by alphabetical Last Name. How could I make it sort that way each time I select a different grade?

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

    Re: How would I filter a subform from a combo box? (Access 2000)

    Just add an order by clause to your sql string :
    strSQL = "Select Fiedl1, Field2 From MyTable Where Grade = " & chr(34) & Me.MyCombo & chr(34) & " Order By [Last Name]"
    Don't forget to add a space between the " and Order or it will give an error.
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How would I filter a subform from a combo box? (Access 2000)

    Awesome! It works great! Thank you.

Posting Permissions

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