Programmatically Search an Access Listbox control's Items Based Off of a Textbox Entry

Question

I don't know if this is possible, but I am trying to code an Access textbox that will act as a search engine entry control for a database. Specifically, I wanted to add mulitple, non-visible, listboxes to a form, and have them filled with table or query data. When ever an end-user enters a search word in the textbox and presses search, I wanted to write a series of "if" statements that would specify if the entry in the textbox matched a value in one of the listboxes, to execute a custom query. For example:

if (Me.textbox.text = a value in the listbox) then etc ...

The problem is that every example I have seen so far only searches listbox values via numerically or by index, such as listbox.selected(0). Since the textbox takes string values, as opposed to numeric values, the code must equate the textbox entry with an item in the listbox. I have been able to add a number in the search textbox and find the listbox item that way, but this is impractical since my end users will only know values. In all, I was wondering if anyone knew how to programmatically search a listbox for a specific value that equals the value entered in a textbox.

Thank you,

DFM

Fionnuala Fionnuala · Accepted Answer · 2009-05-17T14:35:32

There are several ways to do this, for example:

Function CheckForItem(strItem, ListB As ListBox) As Boolean
Dim rs As DAO.Recordset
Dim db As Database
Dim tdf As TableDef

  Set db = CurrentDb

  CheckForItem = False

  Select Case ListB.RowSourceType
      Case "Value List"
          CheckForItem = InStr(ListB.RowSource, strItem) > 0

      Case "Table/Query"
          Set rs = db.OpenRecordset(ListB.RowSource)

          For i = 0 To rs.Fields.Count - 1
              strList = strList & " & "","" & " & rs.Fields(i).Name
          Next

          rs.FindFirst "Instr(" & Mid(strList, 10) & ",'" & strItem & "')>0"

          If Not rs.EOF Then CheckForItem = True

      Case "Field List"

          Set tdf = db.TableDefs(ListB.RowSource)

          For Each itm In tdf.Fields
              If itm.Name = strItem Then CheckForItem = True
          Next

  End Select

End Function

From: http://wiki.lessthandot.com/index.php/Listbox:_Does_an_Item_Exist

However, I suspect you may be approaching a problem from the wrong direction, multiple hidden controls are rarely a good idea, and it may be better to explain your needs more fully.

EDIT re Comment

This example is not fast, but it is quite easy.

Sub SearchTables(strFind As String)
''Reference: Microsoft DAO x.x Object Library
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMessage As String

Set db = CurrentDb

For Each tdf In db.TableDefs

    strFieldList = ""

    For Each fld In tdf.Fields
        strFieldList = strFieldList & " & [" & fld.Name & "]"
    Next

    strSQL = "SELECT * FROM [" & tdf.Name & "] " _
    & "WHERE Instr(" & Mid(strFieldList, 4) & ",'" & strFind & "') > 0"

    Set rs = CurrentDb.OpenRecordset(strSQL)

    If Not rs.EOF Then
        rs.MoveLast '' Populate recordset, a little slower
        strMessage = strMessage & vbCrLf & tdf.Name & " : " & rs.RecordCount
    End If
Next

MsgBox "Found in - " & vbCrLf & IIf(strMessage = vbNullString, "None", strMessage)
End Sub

Programmatically Search an Access Listbox control's Items Based Off of a Textbox Entry

1 Answers