Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restricting records in a form (2007)

    I have a form based on “ParentTable” with a subform “StudentTable” (linked by a field [ParentID]).

    Is there a way, when the form is opened, to limit the records that load from the “ParentTable” to those records where the linked records from the “StudentTable” have a field [Active] that is True?

    (The ParentTable contains about 280 records, but for this time period, there are only 65 of those records whose child(ren) are “Active” for this particular activity. Ideally, I would like to have the form load by default only for those active families, but with the option to reload the form showing all 280 when I need to see them all.)
    Does that make sense?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Restricting records in a form (2007)

    Do you want to display records from ParentTable where (1) *all* corresponding records in StudentTable have Active = True or (2) at least one of the corresponding records in StudentTable has Active = True.

  4. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    at least one

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

    Re: Restricting records in a form (2007)

    And what do you want to show in the subform? (1) Only records with Active = True, or (2) All records (of which, as you indicated, at least one must have Active = True)?

  6. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    I guess on opening the form, I would only need those marked active on the subform. Right now, I have it ordered by Active, so the active one(s) display first.

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

    Re: Restricting records in a form (2007)

    The attached demo database shows how you can do this. I used stripped down versions of the Categories and Products tables from the Northwind sample database. The field ysnDiscontinued in the Products table has been renamed to ysnActive.
    I created queries qryActiveCategories and qryActiveProducts to select the categories that have at least one active product, and the active products.
    The from frmCategories contains two toggle buttons that let you switch between the various combinations. They do this by switching the record source of the main form and subform between the tables and the queries. The code behind the toggle buttons is simple:

    Private Sub tglCategories_Click()
    If Me.tglCategories Then
    Me.RecordSource = "tblCategories"
    Else
    Me.RecordSource = "qryActiveCategories"
    End If
    End Sub

    Private Sub tglProducts_Click()
    If Me.tglProducts Then
    Me.sbfProducts.Form.RecordSource = "tblProducts"
    Else
    Me.sbfProducts.Form.RecordSource = "qryActiveProducts"
    End If
    End Sub
    Attached Files Attached Files

  8. #7
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    Thanks so much, Hans. I will work my way through that and see how it goes. Thanks again,

    Warren

  9. #8
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    Well, it seems that whenever the button is toggled to restrict the form to "ActiveParents", no students show up in the subform with the student data.

    Since the Main Form is now based on the qryActiveParents (unless the button is toggled), does the subform record source have to reflect that somehow in order to maintain the Parent-Child relationship (in the relational database sense)?Here's what my code looks like at present:

    Private Sub tglParents_Click()
    If Me.tglParents Then
    Me.RecordSource = "ParentTable"
    Else
    Me.RecordSource = "qryActiveParents"
    End If

    End Sub

    Private Sub tglStudents_Click()

    If Me.tglStudents Then
    Me.StudentInfoForm.Form.RecordSource = "StudentTable"
    Else
    Me.StudentInfoForm.Form.RecordSource = "qryActiveStudents"
    End If

    End Sub

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Restricting records in a form (2007)

    The subform should be linked to the main form the usual way: through the Link Master Fields and Link Child Fields properties of the subform as a control on the main form. There should be no need to change this in the toggle button code.

    Check carefully that you have designed the queries the same way as in the sample database. If you can't find the error, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions. (I won't be able to look at it immediately, it's bedtime for me, but I'm sure someone else will)

  11. #10
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    Well, I'm not finding the problem, let alone the solution. Here's a stripped copy (there is also a problem with the search combo box in the header, as you will see). Thanks in advance for any help offered.

    Warren
    Attached Files Attached Files

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

    Re: Restricting records in a form (2007)

    In both of your queries removed the tick from the "show box" for the fields use to set criteria. You don't need them as the * already gives them to you.

    When you include two copies of a field in the query results the field names get changed.
    Regards
    John



  13. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Restricting records in a form (2007)

    John Hutchison has already posted the solution.

    The database you posted doesn't work - there's a missing subform, and it has missing references. And if you had compacted the database before zipping it, you could have reduced the size of the zip file to about 50 K, so there was plenty of room.

  14. #13
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    Thanks for your help, John. That did the trick. Not sure I would have ever tracked that down.

    Warren

  15. #14
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting records in a form (2007)

    Sorry it didn't work. I had compacted it and that took it down to 700 kb or so on my end before zipping. Then I deleted that last subform in an effort to get it down below the 100 kb limit. Maybe the 2007 compact and repair doesn't compact so well?

    Nevertheless, thanks again for your help. It is working like a charm now. Have a good evening.

    Warren

Posting Permissions

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