Filtering records in a form based on records in subform or related tables.

Jason

I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The Names-Normalized table is a one-to-many
relationship with the TestResults table (with the Names-Normalized on
the many side), and SiteAddress is a one-to-one relationship with the
TestResults table. Both tables are related via the TestID value. The
Names-Normalized table has the following fields: TestID, LastName, and
FirstName. Both the Names-Normalized and the SiteAddress data are
displayed in a subform on the primary form; each based on a query that
is used only to display certain parts of the records from each table
into the subform.

I need a user to be able to do the following:
A)Type in a users Last Name (in a textbox)and filter those records in
the primary form that have the same TestID (e.g. Last Name = Johnson,
Johnson returns 3 different TestIDs, filter those records with the
same TestID in the primary form).

B) Same has (A) for the SiteAddresses, though the filtering could be
done through a combo box.

I currently have an unbound text box on the primary form with the
following code (used for filtering Last Name):

Private Sub NameFilter_AfterUpdate()
Me.Filter = "TestID In(SELECT TestID FROM Names-Normalized WHERE
[LastName] = '" & Me!NameFilter & "')"
Me.FilterOn = True
End Sub

I get the following error:

Run Time Error 2448
You can not assign a value to this object.

Any ideas on resolving this problem will be greatly appreciated.
Thanks in advance.

Nov 12 '05 #1

Allen Browne

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The basic idea is to reassign the RecordSource of the main form to an INNER
JOIN statement, so it contains only the records that have a match in the
related table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"Jason" <JA********@CO.COLUMBIA.WI.US> wrote in message
news:f0**************************@posting.google.c om...

I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The Names-Normalized table is a one-to-many
relationship with the TestResults table (with the Names-Normalized on
the many side), and SiteAddress is a one-to-one relationship with the
TestResults table. Both tables are related via the TestID value. The
Names-Normalized table has the following fields: TestID, LastName, and
FirstName. Both the Names-Normalized and the SiteAddress data are
displayed in a subform on the primary form; each based on a query that
is used only to display certain parts of the records from each table
into the subform.

I need a user to be able to do the following:
A)Type in a users Last Name (in a textbox)and filter those records in
the primary form that have the same TestID (e.g. Last Name = Johnson,
Johnson returns 3 different TestIDs, filter those records with the
same TestID in the primary form).

B) Same has (A) for the SiteAddresses, though the filtering could be
done through a combo box.

I currently have an unbound text box on the primary form with the
following code (used for filtering Last Name):

Private Sub NameFilter_AfterUpdate()
Me.Filter = "TestID In(SELECT TestID FROM Names-Normalized WHERE
[LastName] = '" & Me!NameFilter & "')"
Me.FilterOn = True
End Sub

I get the following error:

Run Time Error 2448
You can not assign a value to this object.

Any ideas on resolving this problem will be greatly appreciated.
Thanks in advance.

Nov 12 '05 #2

Viktor Lakics

Dear Allen,

This was a great tip on your home page about the simple filter example with
the categories. I was looking for the exact same thing for a little database
of mine. I have a little problem though:

I wanted to set up not just one but two combos two have two (independent)
filtering option. I changed the names of the combo and the ID field in your
example and I got a beautifully working filtering control. Then I went on
repeated the code to have a second combo, for filtering another field, but
when I "preview" my form it I get an error message, the two combos seem to
"disturb" each other.

Any idea why?

Thanks in advance: Viktor
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:zJ********************@news-server.bigpond.net.au...

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The basic idea is to reassign the RecordSource of the main form to an INNER JOIN statement, so it contains only the records that have a match in the
related table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"Jason" <JA********@CO.COLUMBIA.WI.US> wrote in message
news:f0**************************@posting.google.c om...

I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The Names-Normalized table is a one-to-many
relationship with the TestResults table (with the Names-Normalized on
the many side), and SiteAddress is a one-to-one relationship with the
TestResults table. Both tables are related via the TestID value. The
Names-Normalized table has the following fields: TestID, LastName, and
FirstName. Both the Names-Normalized and the SiteAddress data are
displayed in a subform on the primary form; each based on a query that
is used only to display certain parts of the records from each table
into the subform.

I need a user to be able to do the following:
A)Type in a users Last Name (in a textbox)and filter those records in
the primary form that have the same TestID (e.g. Last Name = Johnson,
Johnson returns 3 different TestIDs, filter those records with the
same TestID in the primary form).

B) Same has (A) for the SiteAddresses, though the filtering could be
done through a combo box.

I currently have an unbound text box on the primary form with the
following code (used for filtering Last Name):

Private Sub NameFilter_AfterUpdate()
Me.Filter = "TestID In(SELECT TestID FROM Names-Normalized WHERE
[LastName] = '" & Me!NameFilter & "')"
Me.FilterOn = True
End Sub

I get the following error:

Run Time Error 2448
You can not assign a value to this object.

Any ideas on resolving this problem will be greatly appreciated.
Thanks in advance.

Nov 12 '05 #3

Allen Browne

Yes, you can use a text box instead of a combo box, or a combination of text
box and combo box. The approach is exactly the same. Use IsNull() to test
which one(s) the user entered a value in. If both, use AND in the WHERE
clause.

You can mock up a dummy query, and switch it to SQL View (View menu in query
design) to get an example of what your SQL statement should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Jason" <JA********@CO.COLUMBIA.WI.US> wrote in message
news:f0**************************@posting.google.c om...

I looked at the example and I was wondering if this could be
accomplished by using a textbox to enter a name or part of a name, and
not a combo box as the example shows? Actually using a combination of
both would be best, a text box for the names and possibly a combo box
for the site addresses.
"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<zJ********************@news-server.bigpond.net.au>...

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The basic idea is to reassign the RecordSource of the main form to an INNER JOIN statement, so it contains only the records that have a match in the
related table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"Jason" <JA********@CO.COLUMBIA.WI.US> wrote in message
news:f0**************************@posting.google.c om...

I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The Names-Normalized table is a one-to-many
relationship with the TestResults table (with the Names-Normalized on
the many side), and SiteAddress is a one-to-one relationship with the
TestResults table. Both tables are related via the TestID value. The
Names-Normalized table has the following fields: TestID, LastName, and
FirstName. Both the Names-Normalized and the SiteAddress data are
displayed in a subform on the primary form; each based on a query that
is used only to display certain parts of the records from each table
into the subform.

I need a user to be able to do the following:
A)Type in a users Last Name (in a textbox)and filter those records in
the primary form that have the same TestID (e.g. Last Name = Johnson,
Johnson returns 3 different TestIDs, filter those records with the
same TestID in the primary form).

B) Same has (A) for the SiteAddresses, though the filtering could be
done through a combo box.

I currently have an unbound text box on the primary form with the
following code (used for filtering Last Name):

Private Sub NameFilter_AfterUpdate()
Me.Filter = "TestID In(SELECT TestID FROM Names-Normalized WHERE
[LastName] = '" & Me!NameFilter & "')"
Me.FilterOn = True
End Sub

I get the following error:

Run Time Error 2448
You can not assign a value to this object.

Any ideas on resolving this problem will be greatly appreciated.
Thanks in advance.

Nov 12 '05 #4