473,221 Members | 1,891 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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

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
3 11055
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
2
by: Colm O'Hagan | last post by:
Hi there, I having a problem with a database I'm setting up, I would be delighted if someone out there could help. The database I'm setting up is a task register datebase, it will be used to...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
1
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have...
1
by: MLH | last post by:
I have a form with a subform control on it listing records returned by a saved query named UnbilledVehicles. I would like to put command buttons on the main form to apply dynamic filters to the...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
2
by: Matuag | last post by:
Hell All, My database has 3 tables Client, Devices and Job Client and Device has One-to-Many Relationship Client and Job has One-to-Many Relationship Device and Job has One-to-Many...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.