By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
field)

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
SITELOC
FROM [NewSiteAddress-ForDisplay]
ORDER BY TestID;

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
RecordSource.
Me.RecordSource = "Test_Results"
Else
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.