472,803 Members | 959 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,803 software developers and data experts.

Searching for records using a subform.

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"
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

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
Me.RecordSource = "Test_Results"
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.
Nov 13 '05 #1
0 1490

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
by: Matt K. | last post by:
Hi there, I have a form in an Access project that contains a subform which displays the results of a query of the style "select * from where = #a certain date#". In the main part of the form...
by: David | last post by:
Hi, I have a form as follows: Main Form (To Select Customer) Sub Form 1 (To Select PO Number, by clicking on field) - Continuous form Sub Form 2 (To view order details)- Continuous form...
by: Jason | last post by:
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. ...
by: Simone | last post by:
Hi All, I have a Find Record button setup in most of my forms in order to find a specific customer's details. I have just noticed today though that this search will only find the customer if it...
by: origin197511 | last post by:
Hello all... I'm having an issue with MSAccess 2000. I have a Form that holds records of my cartridge loads for a rifle and a subform that lists all groups that have been fired with that load. ...
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
by: keri | last post by:
Hi and apologies for duplicates. I am having some posting issues so thought i'd start afresh. Please treat me as a beginner so I get very simple explanations! The below is driving me crazy; ...
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.