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
"TestDataCleanu p" which is based on a table named "Test_Resul ts". This
primary form has multiple subforms associated with it, including
"Names_Disp lay" and "SiteAddresses_ Display". The tables for these two
subforms are "Names" and "Total_Site_Add ress" and have relationships
with the "Test_Resul ts" table (Names is one-to-many,
Total_Site_Addr ess is one-to-one).
Currently I have two fields in the primary form that are used to limit
the recordset in the "TestDataCleanu p" form. The first is named
"txtNameFil t" 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_Resul ts" 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_Aft erUpdate()
Dim strSQL As String
If IsNull(Me.txtNa meFilt) Then
Me.RecordSource = "Test_Resul ts"
Else
strSQL = "SELECT DISTINCTROW Test_Results.* FROM Test_Results " &
_
"INNER JOIN Names ON " & _
"Test_Results.T estID = Names.TestID " & _
"WHERE Names.LASTNAME = '" & Me.txtNameFilt & "';"
Me.RecordSource = strSQL
End If
End Sub
The second field is named "cboSiteAdd LU" and is an unbound combobox
with the row source being a query named "SiteAddressFor Display". This
query selects data from the "Total_Site_Add ress" 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_Add ress" and the "Test_Resul ts" 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 ("TestDataClean up")
to those that contain the address choosen. The code for this is as
follows:
Private Sub cboSiteAddLU_Af terUpdate()
Dim strSQL As String
Dim strAddr As String
If IsNull(Me.cboSi teAddLU) Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "Test_Resul ts"
Else
strAddr = Me.cboSiteAddLU
'Escape any single-quotes.
strAddr = Replace(strAddr , "'", "''")
strSQL = "SELECT DISTINCTROW Test_Results.* FROM Test_Results
" & _
"INNER JOIN SiteAddressForD isplay ON " & _
"Test_Results.T estID = SiteAddressForD isplay.TestID " & _
"WHERE SiteAddressForD isplay.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.