troubleshooting Question

Search and Filter Text as you Type in a combobox of a subform

Avatar of Michael Paravicini
Michael ParaviciniFlag for Chile asked on
Microsoft Access
9 Comments1 Solution13 ViewsLast Modified:
As part of a subform datasheet, I have the column cmbDebit with the rowsource (Select Account, Account +' - ' +Description From ChartOfAccounts) and would like to have the ability to search description for similar values as being typed. Currently i found the following solution in the internet:

in the Subform:
Private Sub cmbDebit_Change()
Call cmbBoxSearch(Me.cmbDebit, "SELECT Account, Account+'  -  '+Description FROM ChartOfAccount", "Not Inactive", "Description", "Account")
End Sub

Private Sub cmbDebit_AfterUpdate()
Call cmbDebit_LostFocus
End Sub

Private Sub cmbDebit_LostFocus()
Dim lItem As Variant
lItem = Me.cmbDebit
Me.cmbDebit.RowSource = "SELECT Account, Account+'  -  '+Description FROM ChartOfAccount WHERE NOT Inactive"
Me.cmbDebit = lItem
Call cmbBoxCollapse
 End Sub

In Public Function:

Public Sub cmbBoxSearch(cmb As ComboBox, lDefaultSQL, lWhereClause, lLookupField, lOrderClause As Variant)
' Search string in combobox
' combobox DATA->Autoexpand MUST BE SET TO NO

cmb.RowSource = ""
cmb.RowSource = lDefaultSQL & IIf(Len(lWhereClause) > 0 Or Len(cmb.Text) > 0, " WHERE " & IIf(Len(lWhereClause) > 0, lWhereClause & " AND ", "") & IIf(Len(cmb.Text) > 0, lLookupField & " LIKE '*" & cmb.Text & "*'", lLookupField & " LIKE '*'"), "") & IIf(Len(lOrderClause) > 0, " ORDER By " & lOrderClause, "")
End Sub

However, the combobox does not work properly and does not display the correct values. Is there a better way to achieve this? Thank you so much for any help. Rg Michael 
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros