faq702-6304 Find As Your Type Combobox 1

[sagamw]

Hi to all from Greece!
I am not a professional programmer but I ve managed to build a fairly good database for my work with Access 2007.

I m trying to embed the find as type code that MajP wrote in my database. I ve already use (with success) a similar approach from Allen Browne but it doesn't **filter** the combobox as MajP describes, so I want to replace it with MajP's.

Unfortunately I can't make it work.
:-(

Here's what I have done and if you please tell me what I am doing wrong....

(1). I created a class module named FindAsYouTypeCombo
(BTW I can't seem to be able to rename a module. I export/edit name/import to get the right name)

(2) The module has the code as in the FAQ without any modification

(3) In my form I paste the code

Public faytCombo As FindAsYouTypeCombo

Private Sub Form_Open(Cancel As Integer)
  Set faytCombo  = New FindAsYouTypeCombo
  Set faytCombo.FilterComboBox = Me.YourComboBoxName
  faytCombo.FilterFieldName = "strLastName"
End Sub

changed like this (I maybe do the mistake here)

Private Sub Form_Open(Cancel As Integer)
  Set faytCombo  = New FindAsYouTypeCombo
  Set faytCombo.FilterComboBox = Me.PSL
  faytCombo.FilterFieldName = "Surname"
End Sub

I have my "search find as you type" text box (named ANAZHTHSH) (i.e. search in greek) but I don't see how can this be functional. I can't find a reference to it in the code.

(I tried to put it in the faytCombo.FilterFieldName = "ANAZHTHSH" with no luck. Either cases no matter what I am writing in the box (ANAZHTHSH) the combobox doesn't select or filters.

Can Anyone help me?

 

[sagamw]

Well *shy*...
because I have the greek version of access 2007 It seems I mixed up the terms combobox and listbox and mine is a listbox after all...

I found another thread thread702-1511591
"Narrowing records as you type each letter in" that seems more appropriate, but still can't make it work....

 

[MajP]

Doing this for a combo box is far more difficult than doing it for a listbox. If you read the thread you will see that awiklendt proposes a simple solution for a listbox. Take a look at his solution and then post if you are still having problems. You will have to post your Sql and code when done. I provided the listbox solution just to show that you could modify the combo box solution to work.

 

[sagamw]

Thank you MajP for your interest.
I will try awiklendt approach with the query.

I wonder which method is more "light" or fast, if you have thousands of records.

But still, I am frustrated because I can't make your code to work. My search text box has no magic at all. It feels like a regular text field and nothing happens.

In my forms module I have also some code for selecting the record you click on, in the listbox

Private Sub Form_Current()
    If Not Me.NewRecord Then
    Me.PSL = Me.PATIENTID
    End If
    
End Sub

Private Sub Form_AfterInsert()
PSL.Requery
End Sub

Private Sub Form_Delete(Cancel As Integer)
PSL.Requery
End Sub

Private Sub PSL_AfterUpdate()

    Dim rs As DAO.Recordset
        Set rs = Me.RecordsetClone
        rs.FindFirst "PATIENTID = " & Me.PSL
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   
End Sub

Is it possible to have some "interference" with it?

Also my adaptation of your form module routine
Public faytList As FindAsYouTypeListBox
Private Sub Form_Load()
Set faytList = New FindAsYouTypeListBox
faytList.Initialize Me.lstProducts, Me.txtBxFilter, "ProductName"
End Sub

is

Public faytList As FindAsYouTypeListBox

Private Sub Form_Load()
  Set faytList = New FindAsYouTypeListBox
  faytList.Initialize Me.PSL, Me.ANAZHTHSH, "???????"
End Sub

I wonder if the problem is in "???????" the name of the field.
I noticed that "???????" is the name of the field in my form but also it is the name of the field in my table which it is the record source..

 

[MajP]

To double check.
1) Put this in a CLASS module not a Standard Module.
The module must be named exactly "FindAsYouTypeListBox"
2) Select Debug Compile in the VB window
after you do this intellisense should work. So when you type
faytList.
you will get the available methods and properties
3) Your listbox is called "PSL" your unbound search text box is "ANAZHTHSH", and the field in your listbox query to filter is "???????".

 

[sagamw]

Thank you MajP I have triple check everything but I had no Luck (my unbound search text box didn't filter the listbox) :-(

I then tried awiklendt's query approach, and after a while I managed to make this work!

My joy didn't last long though because I lost a feature during the change of my code. Previously (with Allen Browne's Findasyoutype module) the first record matched what I was typing, it was automatically selected and displayed.

Now with the query/filter approach, my listbox shows the filtered entries but it doesn't selects/displays automatically the first matched record. You have to click on it to show. :-( *shy*

Here's my current code

Private Sub txtSearchText_Change()
    Dim vStrSearch As String
    
    vStrSearch = txtSearchText.Text
    txtSearchVal.Value = vStrSearch
    
    Me.PSL.Requery
   
End Sub

Private Sub PSL_AfterUpdate()

    Dim rs As DAO.Recordset
        Set rs = Me.RecordsetClone
        rs.FindFirst "PATIENTID = " & Me.PSL
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_AfterInsert()
PSL.Requery
End Sub

Private Sub Form_Current()
 If Not Me.NewRecord Then
 ' ?? ??????? ??? ???? ????? ?????????? ?? ?????
 ' Me.PSL = Me.PATIENTID
 End If
 
End Sub

As you can see the last sub is disabled (at least the Me.PSL = Me.PATIENTID). It was needed in my previous code but if I leave it now (a) It doesn't help in my problem (to select/display the first record of the filtered results and (b) If I disabled it the record shown it's highlighted (which is a good thing!) in the listbox.

How can I change my code to select/display the first record of the filtered results?

P.S. My listbox is in the form not a separate window

 

[MajP]

http://www.4shared.com/file/151404420/cc5ae9c/DemoFAYTlistBox.html

Here is my demo. Try using your rowsource and see if you can get it to work. I changed my class module to select the first row in the list.

Although the class module is lengthy it is well encapsulated. This allowed me to do the whole demo in under 3 minutes. This is all the code I had to write to make all of this work:

Public faytListSuppliers As FindAsYouTypeListBox
Private Sub Form_Load()
  'This is all that is needed for the FAYT class method
  Set faytListSuppliers = New FindAsYouTypeListBox
  faytListSuppliers.Initialize Me.lstSuppliers, Me.txtBxFilter, "CompanyName"
End Sub

 

[sagamw]

Thank you MajP for all your trouble.
I deleted all my relevant with the listbox code, I added the class module etc. and it worked as a filter BUT...

(1) It still doesn't select and display the first record from the list. (My listbox is inside the form of the records NOT a separate window). I want as I type to go to the first matched record, without pressing any key...

(2) If I click on an entry of the listbox, it doesn't go to that record.
If I embed a part of my previous code, it gains that functionality BUT it resets the filtered results of your code (I use requery). i.e. when I click an entry the list box shows all the records.

 

[MajP]

In my original post I did not have the code to select the first record in the list. But I added it in the demo. Ensure you use the class module in the demo, not the original class:

the line of code is

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
    [b]
    'modified code to select first item
     mListbox.Selected(0) = True
     [/b]
  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

if it is still not working then just add that piece of code on your form event.

Private Sub txtBxFilter_Change()
Me.lstSuppliers.Selected(0) = True
End Sub

 

[sagamw]

Thanks again MajP.
It selects the first entry BUT doesn't display the selected record if you don't click on it.

I want to display the first matched record as I type.
I think I need a

 Me.Bookmark = ....

code.

 

[MajP]

I do not understand what you mean by "does not display" the selected record. If you are using the listbox, where do you expect it to display.

I am taking a guess, and maybe you want to both filter the listbox, but also use this to find a record on the form. Here is a demo. As you type it goes to the first record in the list.

http://www.4shared.com/file/151793571/1124281e/DemoFAYTlistBox_and_Search.html

 

[sagamw]

Thank you MajP. Thats exactly what I meant by "display"!
Your demo is similar to my database BUT there is a flaw.

It doesn't filter the listbox when I type the 1st and the 2nd letter. It does when I type the 3rd letter.

Same goes to your demo that you send me.
When I type the 1st letter it selects (and displays!) the record but it doesn't filter the listbox. It works after the 2nd letter (in my db after the 3rd).

(I am using Access 2007 if that matters.)

 

[MajP]

I did not notice that. I have no idea why that happens, it has to do with order of events. However, here is the fix for the class module.

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
    'modified code to select first item
    mListbox.Selected(0) = True
    'Modified to set the value of the listbox
    mListbox.Value = mListbox.Column(0)
    Call moveForm
    'Set it again to fix the problem with not filtering on first record
    Set mListbox.Recordset = rsTemp
  End If
  mTextBox.SelStart = Len(mTextBox.Text)
  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