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]

If you review your thread thread702-1577906, you can see where I suggested you set a Record Source to a SQL statement that changes the sort order. I use the exact same type of code to set the Row Source of combo and list boxes.

You allow your users to make selections and enter criteria and then build a SQL statement. Finally, you set the Row Source property to the SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

So if I have a "search" text box where key words are typed, am I correct that a code similar to the following should go in "on enter" event of this text box? or should it go in "control source" or "row source" of List Box containing all clients? or "record source" of main form (already contains "clients" table)?

Private Sub Text85_Enter()
Dim strSQL As String
Dim str OrderBy as String
strSQL = "SELECT .... FROM .... "
Select Case Me.grpSortOrder
Case 1 'direct connect
strOrderBy = " ORDER BY [Provider]"
Case Else
End Select
Me.sfrmDIRECT_CONNECT.Form.RecordSource = strSQL & strOrderBy
Select Case Me.grpSortOrder
Case 1 'extranet
strOrderBy = " ORDER BY [Provider]"
Case Else
End Select
Me.sfrmEXTRANET.Form.RecordSource = strSQL & strOrderBy
Select Case Me.grpSortOrder
Case 1 'master key direct connect
strOrderBy = " ORDER BY [Direct Connect]"
Case Else
Case 2 'master key DR
strOrderBy = " ORDER BY [DR]"
Case Else
Case 3 'master key acting as service bureau
strOrderBy = " ORDER BY [On Behalf Of]"
Case Else
Case 4 'master key via service bureau
strOrderBy = " ORDER BY [Via Service Bureau]"
Case Else
End Select
Me.sfrmMASTER_KEY.Form.RecordSource = strSQL & strOrderBy
End Sub

 

[dhookom]

I would use the after update event of whatever control the user updates (text box or combo box or option group). Based on the user entries, you would build a SQL statement to apply to whatever you want to filter and order.

Typically, I will create a sub and then call it from the After Update of several controls.

I don't think I have ever used the Enter event.

Here is some code pulled directly from an application I am working on. There are 4 sorting options and 4 filtering combo boxes.

Sub UpdateListBoxRowSource()
    Dim strSQL As String
    Dim strWhere As String
    strWhere = " WHERE 1=1 "
    If Nz(Me.cboMatID, 0) <> 0 Then
        strWhere = strWhere & " AND cmpMatID = " & Me.cboMatID & " "
    End If
    If Nz(Me.cboSite, 0) <> 0 Then
        strWhere = strWhere & " AND cmpSitID = " & Me.cboSite & " "
    End If
    If Nz(Me.cboVenID, 0) <> 0 Then
        strWhere = strWhere & " AND cmpVenID = " & Me.cboVenID & " "
    End If
    If Nz(Me.cboStaID, 0) <> 0 Then
        strWhere = strWhere & " AND cmpStaID = " & Me.cboStaID & " "
    End If
    strSQL = "SELECT cmpCmpID, DateValue([cmpCreateDate]) AS Created, [matCode] & ': ' & [matTitle] AS Material, " & _
        "cmpShortDesc AS Description, venName AS Vendor, sitTitle AS Site, staTitle AS Status " & _
        "FROM tblComplaintStatus RIGHT JOIN (tblMaterials RIGHT JOIN (tblSites RIGHT JOIN " & _
        "(tblVendors RIGHT JOIN tblComplaints ON tblVendors.venVenID = tblComplaints.cmpVenID) " & _
        "ON tblSites.sitSitID = tblComplaints.cmpSitID) ON tblMaterials.matMatID = tblComplaints.cmpMatID) " & _
        "ON tblComplaintStatus.staStaID = tblComplaints.cmpStaID "
    strSQL = strSQL & strWhere
    Select Case Me.grpSortOrder
        Case 1 ' Date
            strSQL = strSQL & " ORDER BY cmpCreateDate Desc; "
        Case 2 ' Item
            strSQL = strSQL & " ORDER BY matCode; "
        Case 3 ' Short Description
            strSQL = strSQL & " ORDER BY cmpShortDesc; "
        Case 4 ' Vendor
            strSQL = strSQL & " ORDER BY venName; "
    End Select
    Me.lboComplaint.RowSource = strSQL

End Sub

Private Sub cboMatID_AfterUpdate()
    Call UpdateListBoxRowSource
End Sub

Private Sub cboSite_AfterUpdate()
    Call UpdateListBoxRowSource
End Sub

Private Sub cboStaID_AfterUpdate()
    Call UpdateListBoxRowSource
End Sub

Private Sub cboVenID_AfterUpdate()
    Call UpdateListBoxRowSource
End Sub

Private Sub grpSortOrder_AfterUpdate()
    Call UpdateListBoxRowSource
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

Duane,

Can you help me understand what the following refer to (MAIN FORM, SUBFORM, FIELD?)so I can figure out what to replace them with?:

-Complaint.RowSource
-cmpMatID
-strWhere
-cboMatID
-cboSite
-cmpSitID
-cboStaID
-cmpCmpID
-cmpCreateDate
-matCode
-matTitle
-cmpShortDesc
-venName
-sitTitle
-staTitle
-tblMaterials

I also don't understand what the following refer to:

-strWhere = " WHERE 1=1 "
-If Nz(Me.cboMatID, 0) <> 0 Then
-RIGHT JOIN (tblSites RIGHT JOIN " & _
"(tblVendors RIGHT JOIN tblComplaints ON

 

[dhookom]

My code has only a main form and I am filtering a list box. The list box displays complaint records with the associated vendors, sites, status, and materials.

There is no "complaint.RowSource". It is "lboComplaint.RowSource".
"lbo" is a prefix I (and many others) use to identify list boxes.
all "cbo..." are the combo boxes used to filter
all "cmp...", "ven...", "sit...", "sta...", "mat..." are fields in the list box row source

I try to use a naming convention for all of my tables and fields.
It should be fairly evident if you check the sql statement I build.
A typical table like tblComplaints has every field beginning with "cmp".
My tblSites has every field beginning with "sit".
My tblVendors has every field beginning with "ven".
All primary key fields are autonumbers and are named like venVenID (primary key of tblVendors) and matMatID (primary key of tblMaterials) and cmpCmpID (primary key of tblComplaints).

A foreign key field has a name like cmpVenID which is the field in tblComplaints (begins with cmp) and links to tblVendors (ends with venID). Once you understand my naming convention, you should be able to identify all of the primary and foriegn keys. I don't generally have a field name ending with "ID" unless it is a primary or foreign key. If you don't understand primary and foreign keys, google it.

strWhere is the string memory variable I use to build a where clause for the Row Source.
I start with " WHERE 1 = 1 " so that when I add additional conditions, I can use " AND..."


If Nz(Me.cboMatID, 0) <> 0 Then
Most of my combo boxes used for filtering have a Row Source that is a union query
where the first returned record has a value of 0 for "ALL Vendors" or "ALL Sites".
If the combo box values is 0 meaning all materials, then I don't want to use it in the where condition.

-RIGHT JOIN (tblSites RIGHT JOIN " & _
"(tblVendors RIGHT JOIN tblComplaints ON
This is just part of the query that is the Row Source of the list box

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

I know that I need to have a SQL statement that changes the sort order/filters List367, but am unsure of how to go about this. Tried to figure it out based on the sample provided but this is new for me. I know that I need the following:

-Unbound search text box "Text85" where key words will be entered
-Unbound list box of clients "List367" which should be filtered upon searching
-Key words should reference the following fields in subforms:

-"ExtraNetProvider" in subform "EXTRANET" (upon key word of "Extranet [keyword]" any clients with [keyword] in ExtraNetProvider should show up on list box)
-"Provider" in subform "DIRECT_CONNECT" (upon key word of "Direct Connect [keyword]" any clients with [keyword] in Provider should show up on list box)
-"Via_Service_Bureau" in subform "Via_Service_Bureau" (upon key word of "Service Bureau [keyword]" any clients with [keyword] in Via_Service_Bureau should show up on list box)
-"On_Behalf_Of" in subform "On_Behalf_Of" (upon key word of "On Behalf Of [keyword]" any clients with [keyword] in On_Behalf_Of should show up on list box)
-"chkDirect_Connect" in subform "Master_Key" (upon key word of "Direct Connect True" if chkDirect_Connect is true then should show up on list box)
-"chkDR" in subform "Master_Key" (upon key word of "DR True" if chkDR is true then should show up on list box)
chkDR_Direct_Connect (upon key word of "DR Direct Connect True" if chkDR_Direct_Connect is true then should show up on list box)
-"DR_DC-Provider" in subform "DR_DIRECT_CONNECT" (upon key word of "DR Direct Connect [keyword]" any clients with [keyword] in DR_DC-Provider should show up on list box)
-"DR Extranet_Provider" in subform "DR_EXTRANET" (upon key word of "DR Extranet [keyword]" any clients with [keyword] in DR_Extranet_Provider should show up on list box)

 

[cstuart79]

so i decided to break this up into two pieces. i have a search text box where keywords will be entered. i also have a drop down box where a category is selected that will move focus to the appropriate subform that is to be searched. i have been able to properly code so that the focus is set on proper subform but the problem is that it is only looking at 1 specific record rather than all clients. is there a way to "unfilter" or set focus to table rather than form?

and will this approach work if focus is set first and then code for something like the following:

Select Me.Extranet_Provider
From Me.EXTRANET
Where Me.Extranet_Provider
Like '%" & txtsearch.text & "%'
Replace RowSource with text "%'

 

[dhookom]

Set focus to a "table"? IMO, you should never expose your tables in applications, only forms and reports. Your field and table names in an SQL statement can't be variables. They must be actual names.

If I were you, I would create a very simple form and subform with the minimal number of controls to see if you can get something to work.

BTW: Your continued use of default control names like "Text85" and "List367" suggests you don't want to improve your development skills. Please do yourself a favor and kick it up a notch and rename your controls.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

I have successfully set RowSource for Listbox to show only clients with certain criteria (clients beginning with "A" for example) when category in dropbox is selected, but I cannot seem to do this when the criteria is referenced outside of the Listbox. To explain, when "Extranet:" is selected from Dropbox I want to set RowSource to show only clients that have field "Provider" in subform "EXTRANET" populated. How can I revise the following code to accomplish this?!

Private Sub lboClients_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[Client ID] = " & Str(Nz(Me![lboClients], 0))

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


If SearchText = "Extranet:" Then

lboClients.SetFocus
Dim rs As Object

lboClients.RowSource = "SELECT CLIENTS.[Client ID], CLIENTS.Name FROM CLIENTS WHERE CLIENTS.Name Like 'A*' ORDER BY CLIENTS.[Name]"
lboClients.Requery

lboClients.SetFocus

End If

 

[dhookom]

There are no fields in a subform. A table or query has fields and forms have controls.

Please provide the record source (table or query or SQL Statement) of the subform and how it is related to your CLIENTS table.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

Record Source of subform "EXTRANET" is table "EXTRANET". This is linked to main form "CLIENTS" by field "Client ID". lboClients is unbound list with Row Source "SELECT CLIENTS.[Client ID], CLIENTS.Name FROM CLIENTS ORDER BY CLIENTS.Name;

 

[dhookom]

Your reply didn't mention anything about a field named "Provider" as suggested in your previous reply. Please look at this through the eyes of someone who can't see your tables or data.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

Sorry Duane. Field "Provider" is in table "EXTRANET" & control "Provider" is in subform "EXTRANET".

 

[dhookom]

It doesn't make any difference what subform the control is located on. Forms only display stuff, they don't store stuff.

Try:

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] Is Not Null) " & _
      "ORDER BY [Name]"
    Me.lboClients.RowSource = strSQL
End If

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

thanks duane. tried out your code but it does not seem to change the List Box Row Source. no errors, but no change.

 

[dhookom]

Where did you place the code? What happens when you set a break point and step through the code? What is your complete code?

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

i placed the code in "on change" event of "SearchText". stepping through does not yield any anomalies. my code is as follows:

Private Sub SearchText_Change()
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] Is Not Null) " & _
"ORDER BY [Name]"
Me.lboClients.RowSource = strSQL
End If

 

[dhookom]

The change event should fire with every key-stroke. I doubt this is what you want to happen. Consider putting the code in the after update event of the "SearchText".

What type of field is Provider? You stated earlier

field "Provider" in subform "EXTRANET" populated

. This suggests using Is Null in the subquery.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

i have changed my naming conventions to be more specific. i have field "tblProvider" in table "tblEXTRANET" and control "sfmProvider" in subform "sfmEXTRANET". "tblProvider" is Text Data Type.
i moved the code to the "after update" event as suggested but still no changes. i also tried changing "is not null" to "is null" but still no changes.
i am trying to retrieve any records where "tblProvider/sfmProvider" IS populated and filter out any records where it is NOT populated.