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