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.
