Searching a SubForm

T

tarcona

Registered User.
Local time
Today, 10:12
Joined
Jun 3, 2008
Messages
165
I know the issue has been brought up, but I have developed code to search on the main form, but what do I need to change to have the code search the subform?
My mainform is named 'Accounts', my subform that I want to search in is called 'ClientInformation Subform.' And the [Last Name] field is in 'ClientInformation Subform'.

Code: 
Private Sub btnSearchAccounts_Click()
    Dim strWhere As String   ' The Criteria String
    Dim lngLen As Long       ' Length of the criteria string to append to
 
' Adds LIKE Last Name field match to the string
    If Not IsNull(Me.txtSearchLast) Then
        strWhere = strWhere & " ([Last Name] Like '" & Me.txtSearchLast & "*') AND "
    End If
 
lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please try again.", vbInformation, "Invalid Search Criterion"
    Else
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
 
        ' Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 
End Sub
 
Last edited:
No, that did not help.
 
Can anyone help or point me in the right direction?
 
"Filtering on a field in the Subform

You cannot use this simple approach if the field you wish to filter on is in the subform."

I have seen this but my field that I wish to filter on is in the subform. I dont get it though because the title and then the first opening line completely contradict each other.

The last lines of my code are:

Code: 
        ' Apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True

I was playing around with the code and it was getting extremely close to being able to filter in the subform, but it wasnt quite right. My changed code was:

Code: 
        ' Apply the string as the form's Filter.
        Me.ClientInformation_Subform.Form.Filter = strWhere
        Me.ClientInformation_Subform.Form.FilterOn = True
 
Read post #6.

I have tried it and it does not work.
 
Did you read ALL of the article? It goes on to explain how to use the complex approach of filtering the main form on fields from the subform, immediately after the words you quoted in post #6.
 
Yeah, I read everything. And pasted the code into my form. And changed the field names and form names, but I still have had zero luck.
 
All of the information is confidential, but I will create a new database with generic information. I will have it up in a short amount of time.
 
Sounds great. In case I miss it and nobody else helps you, feel free to PM me and let me know it's here.
 
I dont know how to PM you...but it will be up soon. I havent downloaded Win Zip yet so I am downloading it and then I will zip the database. It will be up in a few minutes.
 
Ok there is the example. It is from Access 2000.

A little background: there are three tables. Accounts is information about certain hospitals. ClientInformation is client information pertaining to the hospitals linked by Account ID. Then there is LeadInformation. This database is intended to document lead information that does not exist yet so that table is blank and you really dont have to worry about that.

All Records Search form is the form I have trouble with. I have made a search possible to search the fields that exist on the mainform like Account ID, Company, State, and Zip Code. But when you click the tabbed "Contacts" there are three fields I want to search for in ClientInformation Subform2 and I can not manage to get that quite right. I would like to search on an EXACT basis for Client ID, and then for LIKE basis for First Name and Last Name.

Thanks for your help.
 

Attachments

This seems to work OK:
Code: 
Private Sub btnSearchContacts_Click()
Dim strSQL As Variant
Dim l_strAnd As String
    If (Me.txtSearchClientID & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") Then
        ' If the search criteria are Null, use the whole table as the RecordSource.
        Me.RecordSource = "Accounts"
    Else
        l_strAnd = ""
        strSQL = "SELECT distinctrow Accounts.* " _
            & "FROM Accounts INNER JOIN ClientInformation " _
            & "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
            & "WHERE "
                        
        If Me.txtSearchClientID.Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchFirst.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[First Name] Like '" _
                & Me.txtSearchFirst.Value & "'"
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchLast.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
        End If
        'strSQL = strSQL & " ORDER BY ???;"
        
        Debug.Print strSQL 'Comment this out when you're ready to go live.
        On Error Resume Next
        Me.RecordSource = strSQL
        If Err <> 0 Then
            MsgBox "No records matching filter." 'This indicates a problem with the code, not the matching criteria.
            Me.RecordSource = "Accounts"
        End If
    End If
End Sub

This is based on code I delivered to a large company in Austin, which was based on Allen Browne's code (from post #5).
 
Thanks. I am at home, but I will try it first thing tomorrow and I will send you a message of how it went. Thanks again.
 
In response to your recent PM to filter on "Company" along with the client stuff:
Code: 
Private Sub btnSearchContacts_Click()
Dim strSQL As Variant
Dim l_strAnd As String
    If (Me.txtSearchClientID & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") [COLOR=red]And (Me.[B][COLOR=#a0522d]txtSearchNewCompanyID[/COLOR][/B] & "" = "")[/COLOR] Then
        ' If the search criteria are Null, use the whole table as the RecordSource.
        Me.RecordSource = "Accounts"
    Else
        l_strAnd = ""
        strSQL = "SELECT distinctrow Accounts.* " _
            & "FROM Accounts INNER JOIN ClientInformation " _
            & "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
            & "WHERE "
                        
[COLOR=red]        If Me.[COLOR=sienna][B]txtSearchNewCompanyID[/B][/COLOR].Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "Accounts[COLOR=sienna][B].[Company ID][/B][/COLOR] = " & Me.[COLOR=sienna][B]txtSearchNewCompanyID[/B][/COLOR]
            l_strAnd = " AND "
        End If
[/COLOR]
        If Me.txtSearchClientID.Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchFirst.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[First Name] Like '" _
                & Me.txtSearchFirst.Value & "'"
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchLast.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
        End If
        'strSQL = strSQL & " ORDER BY ???;"
        
        Debug.Print strSQL 'Comment this out when you're ready to go live.
        On Error Resume Next
        Me.RecordSource = strSQL
        If Err <> 0 Then
            MsgBox "No records matching filter." 'This indicates a problem with the code, not the matching criteria.
            Me.RecordSource = "Accounts"
        End If
    End If
End Sub

Add the code in red. And make sure you're using your own object names. I made the sample names up.

HTH.
 
You must log in or register to reply here.

Similar threads

C
Module variable
Replies
9
Views
722
Pat Hartman
P
J
Debugger complains about [apparently] non-existant characters
Replies
9
Views
658
The_Doc_Man
The_Doc_Man
A
Need VBA Help with Printer Status
Replies
1
Views
760
June7
June7
C
Problem with 'Like' SQL filter
Replies
6
Views
416
Josef P.
J
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
1,256
Faoineag
F

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom