Narrowing records as you type each letter in 1

[Ktx7ca]

Hello Everyone

The issue I'm having is that I need the listbox to narrow the displayed records as I type the letters in the Textbox

I can't quite figure out what event in the textbox to put the listbox requery statement into to get it to work right

Any Ideas on this would be greatly aprecaited

chris

 

[PHV]

Use the Change event and the Text property of the textbox

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ktx7ca]

HI PHV

I'm not sure I understand the "Text property of the text box"

could you please clarify

Thanks Chris

 

[MajP]

faq702-6304

 

[Ktx7ca]

Thanks Majp

If could you point me in the rigth direction as to the best way to modify this to work for my application as I'm using a text box and a list box, I'd be very greatfull.

Thanks Chris

 

[Ktx7ca]

Hello Everyone

I'm still trying to get my Listbox to narrow the records shown as I type in my textbox.

looking for any ideas that might turn the light on in my brain

thanks

 

[jamaarneen]

Hi Ktx7ca,

You might want to try the suggestion of PHV (5 nov 08)

The text property, is a property of a textbox(or combobox), that returns the text what in currently in the textbox.
by example, when user types the an 'a' as the first character, then YourTextboxName.Text will be "a". when a 'b' is added, then the value of YourTextboxName.Text is changed to "ab". and so on.

So the idea is, that in the ChangeEvent, you should add a WHERE clause to the Row Source of the listbox.
to do this you will have to generate a string for the YourListboxNameRowSource. where you should add: "WHERE YourFieldName like '" & YourTextboxName.Text & *' ".

you should experiment a little
see something?
Ja

 

[Ktx7ca]

HI Jam

I undertand a fair bit in access it's usally the terminollogy that confuses me. In PHV sugestion the user would have to press enter after every letter to get an updated listbox. the user would be typing out a pharse so it would be more then a couple of letter.

I'm usally fairly good at figuring out code examples and modifing them for my needs. but I just can't get MajP's example to work. I'm pretty curtin it does what i'm looking for and can for tweeked for my needs

I apreacaite your help but I'm sure PHS's idea works for me unless I implemented it wrong.

Thanks Chris

 

[PHV]

In PHV sugestion the user would have to press enter after every letter
Really ?

 

[Ktx7ca]

HI Jam

I after saying all that I went back and looked at PHV 's info once again. I may have made a mistake when I was trying it the last couple of times.

My listbox flashes like it's requerying after each letter typed but dosen't acutally update the screen.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ktx7ca]

Hey PHV

Sorry for the false statement there I should never write and eat and at the same time.

Forms![Sf-Booklookup]![booksearchlist].requery

the listbox in on a subform

 

[PHV]

How is your code related to my suggestion ????

 

[Ktx7ca]

thats the code in the onchange event my textbox

 

[MajP]

Drop this in a CLASS module called "FindAsYouTypeListBox"

Option Compare Database
Option Explicit

'Class Module Name: FindAsYouListBox
'Purpose: Turn any Listbox into a "Find As You Type"  listbox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use:To use the class, you need code similar to the 'following in a form's module.
'Also requires a reference to DAO
'Two properties must be set: FilterListBox
'and FilterFieldName.  These are the combo box object
'and the name of the field that you are filtering.
'

Private WithEvents mListbox As Access.ListBox
Private WithEvents mForm As Access.Form
Private WithEvents mTextBox As Access.TextBox
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Public Property Get FilterListBox() As Access.ListBox
  Set FilterListBox = mListbox
End Property
Private Sub mTextBox_Change()
  Call FilterList
End Sub
Private Sub mListBox_AfterUpdate()
  Call unFilterList
  mTextBox.SetFocus
  mTextBox.Value = Null
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mTextBox.Text
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  strFilter = mFilterFieldName & " like '" & strText & "*'"
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mListbox.Recordset = rsTemp
  End If
  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 mListbox.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_Terminate()
    Set mForm = Nothing
    Set mListbox = Nothing
    Set mRsOriginalList = Nothing
End Sub

Public Sub Initialize(theListBox As Access.ListBox, theTextBox As Access.TextBox, FieldName As String)
   On Error GoTo errLabel
  If Not theListBox.RowSourceType = "Table/Query" Then
    MsgBox "This class will only work with a ListBox that uses a Table or Query as the Rowsource"
    Exit Sub
  End If
  Me.FilterFieldName = FieldName
  Set mListbox = theListBox
  Set mForm = theListBox.Parent
  Set mTextBox = theTextBox
  mForm.OnCurrent = "[Event Procedure]"
  mTextBox.OnGotFocus = "[Event Procedure]"
  mTextBox.OnChange = "[Event Procedure]"
  mListbox.AfterUpdate = "[Event Procedure]"
  
 Set mRsOriginalList = mListbox.Recordset.Clone
 Exit Sub
errLabel:
 MsgBox Err.Number & " " & Err.Description
End Sub

On a form put a list box and text box. In the initialize procedure pass your listbox name, your textbox name, and the name of the field you are filtering.

in the form

Option Compare Database
Option Explicit
Public faytList As FindAsYouTypeListBox
Private Sub Form_Load()
  Set faytList = New FindAsYouTypeListBox
  faytList.Initialize Me.lstProducts, Me.txtBxFilter, "ProductName"
End Sub

 

[PHV]

So, what is the SQL code of the RowSource property of booksearchlist ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

It does not matter to me what your row source is. Write the 3 lines of code and you are done.

 

[PHV]

I asked Ktx7ca

 

[MajP]

If this listbox is multiselect then you do not want it to unfilter after a selection. So delete or comment out the code out the following

Private Sub mListBox_AfterUpdate()
Call unFilterList
mTextBox.SetFocus
mTextBox.Value = Null
End Sub

However if this is not a multiselect than why would you not use a combobox. If you look at my original as you enter the find as you type combo it automatically expands like a listbox.

 

[lbigk]

MajP,
Thank you for the code, it's very clean. I am having problem with 'Public faytList As FindAsYouTypeListBox' line, I am getting a Compile error: A module is not a valid type. Any advice?

Thank you,
lbigk