Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 06-17-2018, 04:17 AM   #1
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Find As You Type Combobox

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

MajP is offline  
The Following 2 Users Say Thank You to MajP For This Useful Post:
hima193 (02-17-2019), Tera (02-07-2019)
Old 09-05-2018, 11:08 AM   #2
Lateral
Newly Registered User
 
Join Date: Aug 2013
Location: Sydney, Australia
Posts: 349
Thanks: 5
Thanked 2 Times in 2 Posts
Lateral is on a distinguished road
Re: Find As You Type Combobox

Hi,


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


Thanks
Cheers
Greg
Lateral is offline  
Old 09-06-2018, 07:04 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,360
Thanks: 115
Thanked 3,110 Times in 2,828 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Find As You Type Combobox

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 09-06-2018 at 08:17 AM.
isladogs is offline  
Old 09-06-2018, 11:49 AM   #4
Lateral
Newly Registered User
 
Join Date: Aug 2013
Location: Sydney, Australia
Posts: 349
Thanks: 5
Thanked 2 Times in 2 Posts
Lateral is on a distinguished road
Re: Find As You Type Combobox

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
Lateral is offline  
Old 02-17-2019, 10:59 AM   #5
hima193
Newly Registered User
 
Join Date: Aug 2018
Posts: 23
Thanks: 19
Thanked 0 Times in 0 Posts
hima193 is on a distinguished road
Re: Find As You Type Combobox

thanks alot my friend for your code
but i have a question


can i use it with more than one combobox in one form

hima193 is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find as you type in a listbox Alisa Forms 31 11-17-2012 12:48 AM
find as you type not working batwings Forms 1 11-02-2008 10:03 PM
I can't find the Type Mismatch Lucantha Modules & VBA 7 02-16-2007 11:25 AM
Find as you type xMax Visual Basic 1 08-27-2006 06:15 AM
Type Mismatch - Just can't seem to find it! Abbos Reports 0 07-12-2006 10:50 PM




All times are GMT -8. The time now is 06:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World