Find As You Type Combobox (1 Viewer)

Status
Not open for further replies.

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:19
Joined
May 21, 2018
Messages
8,463
By placing the below code into a class module (not standard module) you can turn any combobox into a find as you type combo with one line of code on a form. In other words if you type J it will filter the list to anything starting with J, if you type Jo it will filter to anything starting with Jo. There is also a property you can set to instead find the letters you type anywhere in the string. This will not work with a value list only combos based on queries or tables. I have posted this on other forums and it is very popular.

To use the code you need one line of code and a variable.
Code:
' Public faytProducts As New FindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False
' End Sub


Code:
Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use: To use the class, you need a reference to DAO and code
'similar to the following in a form's module.

'Parmaters:
'  TheComboBox: Your Combobox object passed as an object
'  FilterFieldName: The name of the field to Filter as
'  string
'  FilterFromStart: Determines if you filter a field that 
'  starts with the text or if the text appears anywhere in 
'  the record.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' Public faytProducts As New FindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False
' End Sub
'
'******* END: Form Code ******************


Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Private mFilterFromStart As Boolean

Public Property Get FilterComboBox() As Access.ComboBox
  Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
  Set mCombo = TheComboBox
End Property
Private Sub mCombo_Change()
  Call FilterList
End Sub
Private Sub mCombo_GotFocus()
   mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
  Call unFilterList
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub mForm_Close()
  ' Code provided by BenSacheri to keep Access from crashing
   Call Class_Terminate
End Sub 
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mCombo.Text
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  If mFilterFromStart = True Then
    strFilter = mFilterFieldName & " like '" & strText & "*'"
  Else
    strFilter = mFilterFieldName & " like '*" & strText & "*'"
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mCombo.Recordset = rsTemp
  End If
  mCombo.Dropdown
  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 mCombo.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
Public Property Get FilterFieldName() As String
  FilterFieldName = mFilterFieldName
End Property
Public Property Let FilterFieldName(ByVal theFieldName As String)
  mFilterFieldName = theFieldName
End Property
Private Sub Class_Initialize()
    
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, Optional FilterFromStart = True)
   On Error GoTo errLabel
   If Not TheComboBox.RowSourceType = "Table/Query" Then
      MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
      Exit Sub
   End If
   Set mCombo = TheComboBox
   Set mForm = TheComboBox.Parent
   mFilterFieldName = FilterFieldName
   mFilterFromStart = FilterFromStart
   mForm.OnCurrent = "[Event Procedure]"
   mCombo.OnGotFocus = "[Event Procedure]"
   mCombo.OnChange = "[Event Procedure]"
   mCombo.AfterUpdate = "[Event Procedure]"
   mForm.OnClose = "[Event Procedure]"
   With mCombo
     .SetFocus
     .AutoExpand = False
   End With
   Set mRsOriginalList = mCombo.Recordset.Clone
   Exit Sub
errLabel:
    MsgBox Err.Number & " " & Err.Description
End Sub
 

Lateral

Registered User.
Local time
Today, 13:19
Joined
Aug 28, 2013
Messages
388
Hi,


Can you please post a sample database so that I can better understand how to implement this into my code?


Thanks
Cheers
Greg
 

isladogs

MVP / VIP
Local time
Today, 20:19
Joined
Jan 14, 2017
Messages
18,186
Lateral
Your post got lost in the system & only discovered / approved today. Relatively quick ...this time!

For future reference, please report your own posts to moderated areas to alert moderators. See sticky post above for more details

However reading MajP's post, he's already explained clearly what to do.
1. Create a new class module called FindAsYouTypeCombo and paste in the supplied code
2. Add a public variable to a standard module as described.
3. Add one line of code to the Form_Open event of any form with a combo you want to use for this purpose

Apologies to MajP for treading on his toes with this explanation
 
Last edited:

Lateral

Registered User.
Local time
Today, 13:19
Joined
Aug 28, 2013
Messages
388
Hi and thanks for the reply and comments


I'm a bit of a newbie and am struggling with implementing this that's why i asked for a sample DB.



Cheers
 

hima193

Registered User.
Local time
Today, 22:19
Joined
Aug 29, 2018
Messages
23
thanks alot my friend for your code
but i have a question


can i use it with more than one combobox in one form
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom