Solved Filter continuous form with multiple criteria (1 Viewer)

foshizzle

Registered User.
Local time
Today, 06:56
Joined
Nov 27, 2013
Messages
277
I have this exact code working in another Access database which I have modified the code to match the controls on the new database. I'm trying to filter a single continuous form using multiple search criteria. The code works for all controls in the header except the combobox. I believe this may have something to do with the combobox data being based on off a query which references two separate tables.. Either that or the column that it is using to search by. I'm attaching a copy of the database for reference. Thanks for any help provided.

Details
Both the form and combobox are based off the same query 'qryAirlineUpdateForm.
Combobox row source: SELECT DISTINCT qryAirlineUpdateForm.tblProviders.ProviderName FROM qryAirlineUpdateForm;
Continuous form field control source: tblProviders.ProviderName

Recreating the issue
If I use the combobox to select any option and click the search button, I receive "Run-time error '3709' The search key was not found in any record.

VBA Code
Code:
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    'Look at each search box, and build up the criteria string from the non-blank ones.
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtAirlineCode) Then
        strWhere = strWhere & "[AirlineCode] like ""*" & Me!txtAirlineCode & "*"" AND "
    End If
    
        'Look at each search box, and build up the criteria string from the non-blank ones.
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtAirlineCompany) Then
        strWhere = strWhere & "[AirlineCompany] like ""*" & Me!txtAirlineCompany & "*"" AND "
    End If

    'Text/String field example.
    If Not IsNull(Me.cboProviderName) Then
        strWhere = strWhere & "([ProviderName] = """ & Me.cboProviderName & """) AND "
    End If

    'Text/String field example.
    If Not IsNull(Me.cboAirlineStatus) Then
        strWhere = strWhere & "([AirlineStatus] = """ & Me.cboAirlineStatus & """) AND "
    End If

    'Chop off the trailing " AND ", and use the string as the form's Filter.
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
 

Attachments

  • Database5.accdb
    640 KB · Views: 419

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:56
Joined
May 7, 2009
Messages
19,175
i added ProviderID from tblProviders to the Query.
also change to Total Query the Row source of the provider combobox.
change the code in the Search button.
 

Attachments

  • Database5.zip
    62.7 KB · Views: 451

foshizzle

Registered User.
Local time
Today, 06:56
Joined
Nov 27, 2013
Messages
277
i added ProviderID from tblProviders to the Query.
also change to Total Query the Row source of the provider combobox.
change the code in the Search button.

That's awesome! Thanks so much for the quick response and explanation.
 

vhung

Member
Local time
Today, 03:56
Joined
Jul 8, 2020
Messages
235
i added ProviderID from tblProviders to the Query.
also change to Total Query the Row source of the provider combobox.
change the code in the Search button.
wow very prompt
>add this to my filter mode
Dim strWhere As String
Dim lngLen As Long

Me.Filter = strWhere
Me.FilterOn = True
 

vhung

Member
Local time
Today, 03:56
Joined
Jul 8, 2020
Messages
235
I have this exact code working in another Access database which I have modified the code to match the controls on the new database. I'm trying to filter a single continuous form using multiple search criteria. The code works for all controls in the header except the combobox. I believe this may have something to do with the combobox data being based on off a query which references two separate tables.. Either that or the column that it is using to search by. I'm attaching a copy of the database for reference. Thanks for any help provided.

Details
Both the form and combobox are based off the same query 'qryAirlineUpdateForm.
Combobox row source: SELECT DISTINCT qryAirlineUpdateForm.tblProviders.ProviderName FROM qryAirlineUpdateForm;
Continuous form field control source: tblProviders.ProviderName

Recreating the issue
If I use the combobox to select any option and click the search button, I receive "Run-time error '3709' The search key was not found in any record.

VBA Code
Code:
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    'Look at each search box, and build up the criteria string from the non-blank ones.
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtAirlineCode) Then
        strWhere = strWhere & "[AirlineCode] like ""*" & Me!txtAirlineCode & "*"" AND "
    End If
    
        'Look at each search box, and build up the criteria string from the non-blank ones.
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtAirlineCompany) Then
        strWhere = strWhere & "[AirlineCompany] like ""*" & Me!txtAirlineCompany & "*"" AND "
    End If

    'Text/String field example.
    If Not IsNull(Me.cboProviderName) Then
        strWhere = strWhere & "([ProviderName] = """ & Me.cboProviderName & """) AND "
    End If

    'Text/String field example.
    If Not IsNull(Me.cboAirlineStatus) Then
        strWhere = strWhere & "([AirlineStatus] = """ & Me.cboAirlineStatus & """) AND "
    End If

    'Chop off the trailing " AND ", and use the string as the form's Filter.
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
good luck with you project
>thanks for the like reaction
 

Users who are viewing this thread

Top Bottom