Searching for records using a subform.

[Guest]

I would like to be able to place a command button on a primary (parent) form
that opens up a subform. I want to use this subform to search for or limit
the recordset of data in the primary form. The database is setup as follows:
the primary form is named "TestDataCleanup" which is based on a table named
"Test_Results". This primary form has multiple subforms associated with it,
including "Names_Display" and "SiteAddresses_Display". The tables for these
two subforms are "Names" and "Total_Site_Address" and have relationships with
the "Test_Results" table (Names is one-to-many, Total_Site_Address is
one-to-one).

Currently I have two fields in the primary form that are used to limit the
recordset in the "TestDataCleanup" form. The first is named "txtNameFilt"
and is an unbound text box where I have the user enter in a last name and
when they click enter, it limits the recordset in the primary form to those
records with that last name. The field "TestID" is the related field between
the "Names" table and the "Test_Results" table. The "LASTNAME" field
contains the last name of the individual in the record. The code that allows
me to do this is:

Private Sub txtNameFilt_AfterUpdate()
Dim strSQL As String
If IsNull(Me.txtNameFilt) Then
Me.RecordSource = "Test_Results"
Else
strSQL = "SELECT DISTINCTROW Test_Results.* FROM Test_Results " & _
"INNER JOIN Names ON " & _
"Test_Results.TestID = Names.TestID " & _
"WHERE Names.LASTNAME = '" & Me.txtNameFilt & "';"
Me.RecordSource = strSQL
End If
End Sub

The second field is named "cboSiteAddLU" and is an unbound combobox with the
row source being a query named "SiteAddressForDisplay". This query selects
data from the "Total_Site_Address" table and is the source query for the
"SiteAddresses_Display" subform. The query contains three fields: TestID,
ADDTOTAL, and SITELOC; with the bound column in the combo box being ADDTOTAL.
TestID is the related field between the "Total_Site_Address" and the
"Test_Results" tables. The ADDTOTAL field contains address information
(ex."W123 Bob Road"). The SITELOC field contains the municipality location
of the site address.

Currently the user can select an address from the combobox and the
recordsource will be limited in the primary form ("TestDataCleanup") to those
that contain the address choosen. The code for this is as follows:

Private Sub cboSiteAddLU_AfterUpdate()
Dim strSQL As String
Dim strAddr As String

If IsNull(Me.cboSiteAddLU) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "Test_Results"
Else
strAddr = Me.cboSiteAddLU
'Escape any single-quotes.
strAddr = Replace(strAddr, "'", "''")

strSQL = "SELECT DISTINCTROW Test_Results.* FROM Test_Results " & _
"INNER JOIN SiteAddressForDisplay ON " & _
"Test_Results.TestID = SiteAddressForDisplay.TestID " & _
"WHERE SiteAddressForDisplay.ADDTOTAL = '" & strAddr & "'"
Me.RecordSource = strSQL
End If
End Sub

As stated above, I would like to be able to move these two functions onto
another subform. I want the user to be able to click a command button, open
up a subform, and then basically perform the same filtering as is being done
now with the lookup fields currently in primary form. Thank you in advance
for any advice.

 

[Guest]

Ok, if I understand what you wrote, you want a subform (or perhaps a pop-up
form) to allow the user to filter your main form in the same manner they are
doing now, which is either enter part of a last name in a textbox or choose
an address from a combo-box.

Create your subform with the same textbox and combo-box on it. However,
your code on the afterupdate events will have to change to reflect that you
are modifying the form "TestDataCleanup".

Change
Me.RecordSource = strSQL
to
Forms!TestDataCleanup.RecordSource = strSQL

I hope I understood what you're trying to do. I have worked with forms that
had as many as 8-10 subforms, but usually the filtering is done on the main
form or prior to opening the main form.

Hope this helps!