Restricting records in a form (2007)

[wsryan]

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?

[HansV]

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.

[wsryan]

at least one

[HansV]

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)?

[wsryan]

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.

[HansV]

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

[wsryan]

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

Warren

[wsryan]

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

[HansV]

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)

[wsryan]

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

[johnhutchison]

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.

[HansV]

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.

[wsryan]

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

Warren

[wsryan]

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