quickly choosing option as you type from combo-box for invoice (1 Viewer)

rehanemis

Registered User.
Local time
Today, 23:22
Joined
Apr 7, 2014
Messages
195
Hi,

I have small program in which I need to add option for a combo box that when user click on it, it will be easy to choose item from 1000 of items by typing its keyword and automatically item is selected in combo box and when enter is pressed it added to invoice: For example:

Items are as in combo box:
Apple
Orange
Ananas
Iron
Silver
Goldle
zippan
Mobile Nokia
etc etc

When I type "an" it show only orange Ananas and Zippan and selection to first item ("Orange") . when I more add a letter like "ana" it only select Ananas and when I press enter button it adds that item to invoice.

Only want quick way to choose and add item to invoice.

Any suggestion?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:22
Joined
Feb 28, 2001
Messages
26,999
It is normal behavior for combo boxes to auto-select the first item that matches a partial type-in. However, your case where you want "Ananas" and "Zippan" when you type "AN" will not happen so easily. Combo boxes auto-select from the left, not from the right.

Most people don't think about typing to a combo box because they are so used to selecting from a combo. But I assure you that typing to jump-scroll to the first matching choice DOES work automatically.

You might need to actually click on the selected item, though. The fact that the highlight is on the first partially matching choice doesn't mean that choice is actually selected, and hitting some other control will not "know" which item is highlighted.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:22
Joined
May 21, 2018
Messages
8,463
This is my code I use to turn any combo into a find as you type combo. It requires the user to drop this code into a class module named FindAsYouTypeCombo (class module not standard module).

Then with one line of code you can turn any combobox into a find as you type. You can choose to filter from beginning or as you want anywhere in the string.

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
 

rehanemis

Registered User.
Local time
Today, 23:22
Joined
Apr 7, 2014
Messages
195
This is my code I use to turn any combo into a find as you type combo. It requires the user to drop this code into a class module named FindAsYouTypeCombo (class module not standard module).

Then with one line of code you can turn any combobox into a find as you type. You can choose to filter from beginning or as you want anywhere in the string.

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

How can apply to my combo box? Suppose my form name="frmInvoice" and combo box name is: "cboItem"
How to link the code with cboItem?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:22
Joined
May 21, 2018
Messages
8,463
As stated in the instructions at top of code

On form invoice
Code:
Option Compare Database
Option Explicit

Public faytItems As New FindAsYouTypeCombo

 Form_Open(Cancel As Integer)
   faytItems.InitalizeFilterCombo Me.cmboItem, "Name_Of_Your_ItemField", False
 End Sub
 

rehanemis

Registered User.
Local time
Today, 23:22
Joined
Apr 7, 2014
Messages
195
As stated in the instructions at top of code

On form invoice
Code:
Option Compare Database
Option Explicit

Public faytItems As New FindAsYouTypeCombo

 Form_Open(Cancel As Integer)
   faytItems.InitalizeFilterCombo Me.cmboItem, "Name_Of_Your_ItemField", False
 End Sub

If you please see the attached snap shot. I apply the code but it is not working.
 

Attachments

  • question to ask.jpg
    question to ask.jpg
    101.4 KB · Views: 39

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:22
Joined
May 21, 2018
Messages
8,463
The code needs to be on your subform. Is frmInvoice your main form or subform?
Also this
"Name_Of_Your_ItemField" needs to be the name of your item field
"ItemName"
 

rehanemis

Registered User.
Local time
Today, 23:22
Joined
Apr 7, 2014
Messages
195
The code needs to be on your subform. Is frmInvoice your main form or subform?
Also this
"Name_Of_Your_ItemField" needs to be the name of your item field
"ItemName"

Can you please give a look. I have attached the program.
 

Attachments

  • AlShakoor Clinic _1.2.zip
    775 KB · Views: 63

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:22
Joined
May 21, 2018
Messages
8,463
I am having difficulty posting the database.
1) You need to rename the class module correctly. It has to be spelled exactly. You are missing an "o" on the end.
2) The very first three lines of the form module should look like below (remove it from the middle of your code):
Option Compare Database
Option Explicit

Public faytItems As New FindAsYouTypeCombo
3) Remove the code from the on open event and put the following in the on load event instead
Code:
Private Sub Form_Load()
   Dim rs As DAO.Recordset
   Me.MedicineBill.Visible = False
   Set rs = CurrentDb.OpenRecordset(Me.ItemName.RowSource)
   Set Me.ItemName.Recordset = rs
   faytItems.InitalizeFilterCombo Me.ItemName, "ItemName", False
End Sub

I do not fully understand why this code is needed, but under certain conditions the combobox recordset is never set. I see where other people have had this issue.

After I made these changes it worked for me.
 

Users who are viewing this thread

Top Bottom