Google-like search across 2 columns, multi-select & populate subform 2

[michaelden]

Hello,

I'm really struggling with a large db (80K rows) table which I need to search across in a google like manner. As text entered it searches both AllDocs.[File Name] & AllDocs.Description returning a unique set of both results combined.

Then the user can select a few options in the combo box to populate a subform. The subform is only there to hold the selected results so the user can open the files associated with each row.

I have been trying to modify the code on this page

http://www.tek-tips.com/faqs.cfm?fid=6295

(How Do I Set Up Find As You Type In A Combo Box?) but I can not get it to search both fields.

Any help is greatly appreciated. I've overdue on this project.

Michael

 

[TheAceMan1]

How are ya michaelden . . .

You need to ping the criteria against both fields. Using [blue]Like '*w* AND *g*'[/blue] as an example, the end result should look like:

[blue]"WHERE [File Name] Like '*w* AND *g*' OR " & _
      "[Description] Like '*w* AND *g*'"[/blue]

However there's a caveat. You say:

[blue]Then the user can [purple]select a few options[/purple] in the combo box to populate a subform.[/blue]

You can't [blue]multiselect[/blue] a combobox!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[michaelden]

so I can't do a google like seearch in a combo box? Would I need a text box and then just diasplay the results in a subform?

e.g. User enters "V plan As Built". I want it to search "*V*" "*plan*" "*As*" "*Built*"

Can that be done?

 

[TheAceMan1]

[blue]so I can't do a google like seearch in a combo box?[/blue]

I wouldn't say that. But it is going to take some time I'm not prepared for. I can only promise I will get back to this. In the meantime perhaps another tipster will come along ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

There is no such thing as multi select combo box, there is a multiselect listbox. I do not even think I can come up with a way to build a multiselect combobox.

If I was doing this I would build a multiselect listbox with a textbox just above it. I do not think find as you type makes sense for that type of search. So I would just have a search button next to the textbox. The search the way you describe it would not work

"*V*" "*plan*" "*As*" "*Built*
This would return any word with a v in it or an as in it. Need to construct a string like

like "V " or like " V " or like " V"

 

[MajP]

This demo is not really a "Google" search but it is a multi column, find as you type listbox. The class module will turn any listbox based on a query into a multi column find as you type. If you look at the parameters for the initialize sub there are several options. (multi column or single column search, search from beginning, search from within, sort)

http://www.4shared.com/file/C7gdIBfQ/MAJP_FAYT_Google_Listbox_28Nov.html

Turning this into a more Google search would not be too complicated, but see if you are able to find your records. Would have to modify the getFilter function.

The difference is if you type
"as built"
it will find all records with "as built" in it, but it will not find all records with as, or built in it.

class module

'Class Module Name: FindAsYouListBox
'Purpose: Turn any Listbox into a "Find As You Type"  listbox
'Created by: MajP

Private WithEvents mListbox As Access.ListBox
Private WithEvents mForm As Access.Form
Private WithEvents mSearchForm As Access.Form
Private WithEvents mTextBox As Access.TextBox
Private mFieldToSearch As String
Private mFilterFromStart As Boolean
Private mSearchAllFields As Boolean
Private mRsOriginalList As DAO.Recordset

Private Sub mTextBox_Change()
  Call FilterList
End Sub
Private Sub mListBox_AfterUpdate()
  Call unFilterList
  mTextBox.SetFocus
  mTextBox.Value = Null
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strFilter As String
  If Not Trim(mTextBox.Text & " ") = "" Then
    strFilter = getFilter(mTextBox.Text)
  Else
    Call unFilterList
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mListbox.Recordset = rsTemp
    mListbox.Selected(0) = True
    mListbox.Value = mListbox.Column(0)
  Else
      MsgBox "No records match " & strFilter
  End If
  mTextBox.SelStart = Len(mTextBox.Text)
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterList()
  On Error GoTo errLable
  Set mListbox.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mListbox = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub Initialize(theListBox As Access.ListBox, theTextBox As Access.TextBox, Optional FieldToSearch As String = "", Optional FilterFromStart = True)
   On Error GoTo errLabel
  If Not theListBox.RowSourceType = "Table/Query" Then
    MsgBox "This class will only work with a ListBox that uses a Table or Query as the Rowsource"
    Exit Sub
  End If
  Set mListbox = theListBox
  Set mForm = theListBox.Parent
  Set mTextBox = theTextBox
  mFieldToSearch = FieldToSearch
  mFilterFromStart = FilterFromStart
  mForm.OnCurrent = "[Event Procedure]"
  mTextBox.OnGotFocus = "[Event Procedure]"
  mTextBox.OnChange = "[Event Procedure]"
  mListbox.AfterUpdate = "[Event Procedure]"
 Set mRsOriginalList = mListbox.Recordset.Clone
 Exit Sub
errLabel:
 MsgBox Err.Number & " " & Err.Description
End Sub

Private Function getFilter(theText As String) As String
   'To make this work well convert all field in the listbox to string
   'Example:  strDateDue: cstr(dtmDueDate)
   Dim fld As DAO.Field
   Dim rs As DAO.Recordset
   Dim strFilter As String
   Dim strLike As String
   theText = Replace(theText, "'", "'")
   If mFilterFromStart Then
     strLike = " like '"
   Else
     strLike = " like '*"
   End If
   Set rs = mListbox.Recordset
   If mFieldToSearch = "" Then
      For Each fld In rs.Fields
        If fld.Type = dbMemo Or fld.Type = dbText Then
          If strFilter = "" Then
             strFilter = fld.Name & strLike & theText & "*'"
          Else
             strFilter = strFilter & " OR " & fld.Name & strLike & theText & "*'"
          End If
        End If
      Next fld
   Else
      strFilter = mFieldToSearch & strLike & theText & "*'"
   End If
   getFilter = strFilter
End Function

Public Property Get FilterFromStart() As Boolean
  FilterFromStart = mFilterFromStart
End Property
Public Property Let FilterFromStart(ByVal blnFilterFromStart As Boolean)
  mFilterFromStart = blnFilterFromStart
End Property
Public Property Get FieldToSearch() As String
  FieldToSearch = mFieldToSearch
End Property
Public Property Let FieldToSearch(ByVal theFieldToSearch As String)
  mFieldToSearch = theFieldToSearch
End Property


Public Sub SortList(SortString As String)
  Dim rs As DAO.Recordset
  Set rs = mListbox.Recordset
  rs.Sort = SortString
  Set mListbox.Recordset = rs.OpenRecordset
  Set rs = mRsOriginalList
  rs.Sort = SortString
  Set mRsOriginalList = rs.OpenRecordset
End Sub

Public Property Get SearchAllFields() As Boolean
  SearchAllFields = mSearchAllFields
End Property

Public Property Let SearchAllFields(ByVal Value As Boolean)
  mSearchAllFields = True
  mFieldToSearch = ""
End Property

here is how to use the class from a form. Need a listbox and a search textbox

Public faytLst As New FindAsYouTypeListBox

Private Sub Form_Load()
  faytLst.Initialize Me.lstSearch, Me.txtSearch, , False
  faytLst.SearchAllFields = True
end sub