470,582 Members | 2,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,582 developers. It's quick & easy.

Filtering records in a form based on data in a subform or another query.

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 form
by field named TestID. The subform is used just for displaying site
address data, data which is stored in another table named
Total_Site_Address. In the Total_Site_Address table there are
numerous fields that form the site addresses including: Address,
TaxName, NewSteNum, NewSteRdNm, and NewSteMuni...there is also a
TestID field which is related to the Test_Results table (on the TestID

There are two queries which hit on the Total_Site_Address table. One
is a select query that selects the TestID, Address, and TaxName fields
(named SiteAddresses-ForDisplayInMainForm). The second query is also a
select query but this one combines the NewSteNum and NewSteRdNm fields
into one field, resulting in a query having the TestID, NewAddress
(combination of the NewSteNum and NewSteRdNm fields) and NewSteMuni
(this query is named NewSiteAddress-ForDisplay).

In order to display the site addresses in the subform, I created an
Union query (named SiteAdd-ForDisplay) that combines the fields from
the two queries listed above, therefore resulting in a query that
contains only one Site Address (named ADDTOTAL) field (a combination
of the Address and the NewAddress fields), one Municipality (named
SITELOC)field (a combinatation of the TaxName and NewSteMuni
fields),and a TestID field. Here is the code for this query:

SELECT [TestID], [Address] AS [ADDTOTAL], [Tax_Name] AS SITELOC
FROM [SiteAddresses-ForDisplayInMainForm]
UNION ALL SELECT [TestID], [NewAddress] AS [ADDTOTAL], [NewSteMuni] AS
FROM [NewSiteAddress-ForDisplay]

The subform that displays site addresses on the primary form is
controlled by this union query.

I would like to place a combo box on the primary form that contains a
list of the site addresses in the SiteAdd-ForDisplay union query and
use it to limit the records in the primary form (from the Test_Results
table), based on what address the user chooses. To further clarify,
if the user chooses N1234 Bob Road from the combo box, I want only
those records which have a site address of N1234 Bob Road to be
available (limiting the recordset). Is this possible to do with a
combo box? The following is code that I have already tried, but I get
a syntax error when running it:

Private Sub cboSiteAddLU_AfterUpdate()
Dim strSQL As String
If IsNull(Me.cboSiteAddLU) Then
' If the combo is Null, use the whole table as the
Me.RecordSource = "Test_Results"
strSQL = "SELECT DISTINCTROW Test_Results.* FROM Test_Results
" & _
"INNER JOIN SiteAddLU ON " & _
"Test_Results.TestID = SiteAddLU.TestID " & _
"WHERE SiteAddLU.ADDTOTAL = " & Me.cboSiteAddLU & ";"
Me.RecordSource = strSQL
End If
End Sub

cboSiteAddLU is the combobox field that the list of site addresses is
in. It is an unbound control on the primary form.

Thank you in advance.
Nov 13 '05 #1
0 1835

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ryan.Chowdhury | last post: by
3 posts views Thread by paquer | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.