CODEWITHSUNDEEP
vbams-access
BeardedSith
BeardedSith

Reputation: 129

Find Keywords in "Notes" field and filter Listbox of names based on results

I have a textbox (txtSearch) that has an On Change Event that should filter out a list of names based on keywords the routine finds in a tables Notes field. However, when I enter text into txtSearch, the listbox (lstCustomers) doesn't just filter out the results, it wipes them out completely and I get an (Invalid use of Null) error.

FYI - I got the routines from another question (Using keywords to find records and list them in a listbox). I'm moving to a new questions because the client has requested some changes to where the keyword search would be conducted.

enter image description here

Here are a few quick details for the routine:

Here is the routine:

Public Function KeyWhere(strKeys As String, strColumn As String) As String

  Dim b    As Variant
  strKeys = Replace(strKeys, vbCrLf, ",") ' remove all line returns

  b = Split(strKeys, ",")
  Dim strWhere   As String
  Dim v    As Variant
  For Each v In b
     If Trim(v) <> "" Then
        If strWhere <> "" Then strWhere = strWhere & " or "
        strWhere = strWhere & strColumn & " like '*" & Trim(v) & "*'"
     End If
  Next
  strWhere = "(" & strWhere & ")"
  KeyWhere = strWhere

End Function

And here is the _Change() routine:

Private Sub txtSearch_Change()
    Dim strWhere As String
    Dim SQL As String

    SQL = "SELECT qrySearchDatabase.Notes FROM qrySearchDatabase WHERE 1=1"
    strWhere = KeyWhere(Me.txtSearch.Text, "Notes")
    SQL = SQL & " AND " & strWhere
    Debug.Print SQL

    Me.lstCustomers.RowSource = SQL
    Me.lstCustomers.Requery

End Sub

I have a feeling my query (qrySearchDatabase) is wonky somehow, but I'm not entirely sure where. Here is the SQL for my query:

SELECT tblContacts.ID, tblContacts.Notes, [Last Name] & ", " & [First Name] AS [File As]
FROM tblContacts
WHERE (((tblContacts.Notes) Is Not Null));

EDIT: Updated code based on response below.
EDIT 2: Added image below to show the messed up columns afterChange()

Weird columns

Upvotes: 1

Views: 68

Answers (1)

Andre
Andre

Reputation: 27634

In the _Change event, the .Value property of the textbox isn't yet set.

strWhere = KeyWhere(Me.txtSearch, "Notes")

Me.txtSearch (implicitely) uses Me.txtSearch.Value

In the _Change event, you must use the .Text property instead.

strWhere = KeyWhere(Me.txtSearch.Text, "Notes")

Upvotes: 2

Related Questions