Search as you type in a form without Subform

[Hamatto]

Hello friends,
I want to know How can I design Search as you type for a form (In single form view) without put a Subform
Any help will be Appreciated
Thanks a lot

 

[Gasman]

Same way as you would with a subform I would have thought?, just that you would only see the first record that matches the search?

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com

 

[MajP]

You can use a class module so you do not have to write any code except for one line. Works with main form or subform.

Post in thread 'Find as we typed'

Jan 21, 2021

Here is an update of my popular FAYT class. Only takes one line of code to make this work. You need to import the class module. Then instantiate a FAYT variable. No other code or even procedures required.
1. Auto expands
2. Handles apostrophes
3. Handles international characters as demonstrated by the test case.
4. Can be configured to search anywhere in string or from beginning only
5. Can search a multi column combobox.



@Gasman FAYT V14

This is an example of the entire code to turn any combo into an FAYT.

Public faytProducts As New FindAsYouTypeCombo

Private Sub...

• MajP

 

[arnelgp]

sample simple search form.

 

[Hamatto]

Thanks a lot my friends That's much kindly from you
I understand that VBA code from arnelgp

Private Sub txtSearch_Change()

    Const src As String = "select data.* from data"
    Dim txt As String
    Dim new_src As String
    
    txt = Me.txtSearch.Text
    
    new_src = src
    
    If Len(txt) <> 0 Then
        
        new_src = new_src & " where [project name] like '" & txt & "*'"
        
    End If
    
    Me.RecordSource = new_src
    
    With Me.txtSearch
        .Value = txt
        .SelStart = Len(txt)
    End With
    
End Sub

But How to modify it to let me search by more criterias like [Task Name] and [Assigned to] not [project name] only
So, I can search for value in any of this fields by start write a text
I tried to modify but can't
Any help will be really appreciated
thanks a lot

 

[CJ_London]

new_src = new_src & " where [project name] like '" & txt & "*' OR [task name] like '" & txt & "*'"

 

[Gasman]

Learn to concatenate values and strings.

 

[MajP]

But How to modify it to let me search by more criterias like [Task Name] and [Assigned to] not [project name] only

In my class example you simply pass in the fields you want to search. No need for extra code.

 

[Hamatto]

In my class example you simply pass in the fields you want to search. No need for extra code.

Thanks for trying help me.I know that you are an expert in Access
Sure, it is very good example , But it is difficult for me, May you kindly tell me how to use it in the arnelgp's attachement?

 

[Hamatto]

new_src = new_src & " where [project name] like '" & txt & "*' OR [task name] like '" & txt & "*'"

Thanks a lot for help me
That's worked with me, but not work when I search by any date in the Startdate field is there any thing to do??
Thanks a lot

 

[MajP]

Here is a cut down version

To use and search any amount of TEXT fields

'Need to declare a variable at top of module
Private FAYT_Form As FindAsYouTypeForm

Private Sub Form_Load()
  Set FAYT_Form = New FindAsYouTypeForm
  FAYT_Form.Initialize Me, Me.txtFilter, ffrm_Anywhereinstring, True, "ProductName"
End Sub

In the initialize event
1. Me is the reference to the form to filter. If it is a subform you need to pass a reference to the subform
2. Me.TextFilter is the textbox that you type into
3. ffrm_Anywhereinstring is an enum that lets you pick how to search
4. True handles international characters
5. I only list one field "ProductName" but you can list as many as you want seperated by commas

If you are searching a date this is not going to work. You should be doing a different kind of search for dates. You should not have a single textbox for text or dates. It is doable but clunky.

 

[Gasman]

Thanks a lot for help me
That's worked with me, but not work when I search by any date in the Startdate field is there any thing to do??
Thanks a lot

Dates require # delimiters and in mm/dd/yyyy or yyyy-mm-dd format.

 

[Hamatto]

Dates require # delimiters and in mm/dd/yyyy or yyyy-mm-dd format.

Excuse me, What did you mean? what shoul I write in the code and where?

 

[Gasman]

I have no idea when I got this, but this should give you a clue?
I just went searching in a DB for #

You could even use it perhaps?

Public Sub FilterList(FilterValue As Variant)
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim FilterString As String
  Set rsTemp = mRsOriginalList.OpenRecordset
 
  Select Case mColumnType
    Case ColumnType.datecolumn
        FilterValue = Format(FilterValue, "MM/DD/YYYY")
        FilterString = "#" & FilterValue & "#"
    Case ColumnType.TextColumn
        FilterValue = Replace(FilterValue, "'", "''")
        FilterString = "'" & FilterValue & "'"
    Case ColumnType.NumericColumn
        FilterString = FilterValue
  End Select
  If mFilterType = Exact_Records Then
    FilterString = mFilterPrefix & " = " & FilterString
  ElseIf mFilterType = Like_Records Then
        FilterString = mFilterPrefix & " Like '*" & FilterValue & "*'"
  End If
  'Debug.Print FilterString
  mFilterString = FilterString
  rsTemp.Filter = FilterString
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mListbox.Recordset = rsTemp
    mListbox.Selected(0) = True
    mListbox.Value = mListbox.Column(0)
    mFilterString = FilterString
  Else
    MsgBox "No Records Found Matching the Filter: " & FilterString
    Call unFilterList
  End If
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify filter string is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub

Here is where I was using it for one control.

Private Sub mTextBox_AfterUpdate()
  If Not Trim(mTextBox & " ") = "" Then
    Call FilterList(mTextBox.Value)
  Else
    mFilterString = ""
  End If
End Sub

Personally now I would write it as one statement, but if you are going to include dates in the same control as text/numeric values, perhaps it could actually come in handy.?

I can see it is a class in this DB, so more than very likely one of MajP's classes? I have not used any others to my bad memory/knowledge.

My advice to you is to choose one or the other's offerings, not try and combine them.
FWIW I have used @MajP 's classes a good few times. try and understand them, then they are easy to use.
Same with @arnelgp 's code, you still need to understand it, to make modifications.

Do not become what I call an 'Oliver', where you keep coming back and asking for 'more', by just taken what is given for on problem and then need to adapt for something else.
Take time to understand what has been offered. When you start to solve issues yourself, you will get great satisfaction.

 

[Hamatto]

Here is a cut down version

To use and search any amount of TEXT fields

'Need to declare a variable at top of module
Private FAYT_Form As FindAsYouTypeForm

Private Sub Form_Load()
  Set FAYT_Form = New FindAsYouTypeForm
  FAYT_Form.Initialize Me, Me.txtFilter, ffrm_Anywhereinstring, True, "ProductName"
End Sub

In the initialize event
1. Me is the reference to the form to filter. If it is a subform you need to pass a reference to the subform
2. Me.TextFilter is the textbox that you type into
3. ffrm_Anywhereinstring is an enum that lets you pick how to search
4. True handles international characters
5. I only list one field "ProductName" but you can list as many as you want seperated by commas

If you are searching a date this is not going to work. You should be doing a different kind of search for dates. You should not have a single textbox for text or dates. It is doable but clunky.

I tried in the attachement file but not work.Please what is the issue?

 

[MajP]

You cannot rename the class to Class1. You need to name the class exactly. It is always better to import the class instead of copying it.
Rename it to:
FindAsYouTypeForm

In your declarations the field names have spaces so you need to add []

Option Compare Database
Option Explicit
'Need to declare a variable at top of module
Private FAYT_Form As FindAsYouTypeForm

Private Sub Form_Load()
  Set FAYT_Form = New FindAsYouTypeForm
  FAYT_Form.Initialize Me, Me.txtSearch, ffrm_Anywhereinstring, True, "[Project Name]", "[Task Name]", "[Assigned to]"
End Sub

This will search in those three fields. Which may not be good. That is why I have properties to dynamically change which fields to searc.

 

[Gasman]

You can have meaningful labels for your fields in the controls for forms/reports.

What the actual fieldname is called should not matter to a user. So get rid of spaces in field names from the start. :-(

 

[arnelgp]

here again is the Simple search, with multiple field search.

 

[Hamatto]

here again is the Simple search, with multiple field search.

Thank you very much