Programmatically Find Access List Controls Items Based on No Text Field Input
I don't know if this is possible, but I am trying to code an Access textbox that will act as a search engine control for the database. Specifically, I wanted to add multi-sheet, invisible, lists to the form and populate the table or query data. When ever an end user types a search word into a text box and clicks on search, I would like to write a series of "if" statements that indicate if an entry in the text box matches a value in one of the lists in order to execute the custom query. For instance:
if (Me.textbox.text = value in list) then etc.
The problem is that every example I've seen so far only traverses the values as a list using a numeric or index like listbox.selected (0). Since the text box accepts string values, not numeric values, the code must equal the text box element as an element in the list. I was able to add a number to the search text box and find the list item that way, but this is not practical since my end users will only know the values. Overall, I was wondering if anyone knows how to programmatically search a list for a specific value equal to the value entered in the textbox.
Thanks,
DFM
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 that you may be approaching the problem from the wrong direction, multiple hidden controls are rarely a good idea, and it might be better to explain your needs more fully.
EDIT re Comment
This example isn't quick, but it's pretty simple.
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
a source to share