Query/Filter List Box based upon key words in subforms 1

[cstuart79]

This is a tough one. I have a database consisting of multiple clients whose specific details are viewable upon selecting one client from a List Box. The details consist of specific categories, each of which makes up a subform. I want to be able to perform a search based upon key words typed into a search box that will filter the List Box to show only those clients that meet the criteria and allow for selection of only those clients. Upon completing the search and viewing any clients desired, I would need to be able to refresh the List Box to show all clients again.
To complicate things, some of the key words that may need to be queried are listed on a check box shown with all clients and the query results should only show those clients where the box is checked as True. Other key words are not listed on the check box but simply populate a field in one of the subforms.

 

[dhookom]

Are you positive the code is being run?

Did you actually name a field beginning with "tbl"?

Does your Provider field allow zero-length-strings?

Try create a new query and enter this into the SQL View (changing to your new table and field names):

SELECT [Client ID], [Name] 
FROM CLIENTS 
WHERE [Client ID] IN 
(SELECT [Client ID] FROM EXTRANET WHERE [Provider] Is Not Null) 
ORDER BY [Name];

The following SQL should return the same records

SELECT DISTINCT C.[Client ID], C.[Name] 
FROM CLIENTS C JOIN EXTRANET E on C.[Client ID] = E.[Client ID]
WHERE E.[Provider] Is Not Null
ORDER BY C.[Name];

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

duane,

i see the problem--my tblEXTRANET only includes those records that DO have tblProvider populated, so there would be no records with "Null".

so i updated my code to read as follows and it is working fine!!!:

Private Sub SearchText_AfterUpdate()
If Me.SearchText = "Extranet:" Then
Dim strSQL As String
strSQL = "SELECT [Client ID], [Name] " & _
"FROM CLIENTS " & _
"WHERE [Client ID] IN (SELECT [Client ID] " & _
"FROM EXTRANET WHERE [Provider] = True) " & _
"ORDER BY [Name]"
Me.lboClients.RowSource = strSQL
End If

Thanks again Duane!

 

[dhookom]

You stated earlier

tblProvider" is Text Data Type

and now your SQL statement compares the field to "True" which is a boolean/YesNo field.

Your statements generally confuse me ;-)

Duane
Hook'D on Access
MS Access MVP