Solved FAYT for Multi Column combo code from MajP question

[sxschech]

I have used the great code from MajP FAYT on a few forms and some work perfectly and a few have an issue. I read the post Populating Combobox with a lot of data and the link in there.

The issues I am experiencing are

• When I refresh the underlying table (I click a button on my form and it pulls in the data into a table from Outlook) , it shows #Deleted. I saw in the Class Module code

NOTE: IN ORDER TO REQUERY MUST USE THE METHOD OF THIS CLASS

Perhaps that is what I need to do and if so, how do I?

• Probably from habit, I sometimes hit the tab key as I have done when using a non-FAYT combo. If tab key is pressed, it generates an error (null value since nothing was actually selected) and then instead of seeing all the records, only one record displays and am unable to select another item in the combo
• Under both of these scenarios, only way to restore the list of items in the combo is to either go to design view and back to form view or close the form completely and then reopen the form

Rowsource of Combo:

SELECT [qryOutlook].[SentFrom], [qryOutlook].[DateSent], [qryOutlook].[subject], qryOutlook.ID FROM [qryOutlook] ORDER BY 1,3,2;

The combobox has an after update event to find the record chosen and display on the form

Private Sub cboGotoMessage_AfterUpdate()
    Me.RecordsetClone.FindFirst "[DateSent] = #" & Me.cboGotoMessage.Column(1) & "#"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.cboGotoMessage = ""
End Sub

Since I am trying to use the feature for FAYT on multi columns this is what I set up.

Top of code window of the Form

Option Compare Database
Option Explicit


Public FAYTGotoMessage As New FindAsYouTypeCombo

Private Sub Form_Load()
'Enhanced combo to use Find As You Type
    FAYTGotoMessage.InitalizeFilterCombo Me.cboGotoMessage, , anywhereinstring, True, False
End Sub

 

[MajP]

If you need to requery the FAYT there is a method in the class called requery.
It is a long story but doing

cboGotoMessage.requery

will not cause the FAYT to requery.
You must do

FAYTGotoMessage.requery

 

[MajP]

Here is an update of the FAYTs. I especially updated the fayt form and listbox. It is a little nicer in that you can specify which fields to search. In the multi field combo you can search one or all.
I cannot replicate the tab error. See if you can replicate it here, or provide your database.

 

[sxschech]

Thanks for the instructions and update. I will try them out today and report back.

 

[sxschech]

After trying out a few scenarios is looking pretty well.

I replaced the Class Module for FindAsYouTypeCombo and made the other changes you recommended.

For the tab key issue, I modified the after update code with a messagebox.

Private Sub cboGotoMessage_AfterUpdate()
'Added message for hitting tab without selecting anything when using the FAYT
'version.  This was not needed for the standard combobox
'https://www.access-programmers.co.uk/forums/threads/fayt-for-multi-column-combo-code-from-majp-question.319205/
'20210825   
    If IsNull(Me.cboGotoMessage.Column(1)) Then
        MsgBox "A selection was not properly made.  Please click on OR use the arrow keys to highlight the item to be selected.", vbExclamation + vbOKOnly, "Incomplete Selection"
    Else
        Me.RecordsetClone.FindFirst "[DateSent] = #" & Me.cboGotoMessage.Column(1) & "#"
        Me.Bookmark = Me.RecordsetClone.Bookmark
        Me.cboGotoMessage = ""
    End If
End Sub

' Note: If you plan to filter non text fields then in the rowsource wrap the field in a CSTR
' Example: Select cstr(SomeNumericField) as StrNumericeField from someTable

This note was very helpful. Thanks for including it in your code.
After adding that to the rowsource, it worked great in allowing search within the date field. I could type 18 and it would filter not only the rows that were on the 18th but also those that had 18 in the time portion.

When I have time, I hope to apply the updated search code to some of my other applications to take advantage of its functionality that was previously problematic.

 

[sxschech]

Came across error 2185.

If I select an item and click on it, whether I directly click on it (not typing any keywords since it is visible in the combo list) or typing letters and then click on it, this error pops up. The good news is the record I chose comes up after I click OK.

If I use another method such as <arrow key+ enter> OR <arrow key + tab> no error message pops up and all is well.

 

[MajP]

The issue would be that somehow you are immediately losing focus from the combobox even though you are in the combox. It is kind of discussed below. What happens if you change

If Left(TypeName(mCombo), 4) = "Form" Then mCombo.SetFocus

to simply

mCombo.setfocus

Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim StrFilter As String
  'There are conditions where the combo will immediately lose focus need to reset. This is normally when the control is in the header and no records return
  'There is another error when the control is on a tab. You cannot set the focus without selecting an item and you get a not item in the list
  'I do not understand this error or what is actually happening. So I disallow this call if on a tab
  If Left(TypeName(mCombo), 4) = "Form" Then mCombo.SetFocus
  strText = mCombo.Text
  'Debug.Print mAutoCompleteEnabled
  If mAutoCompleteEnabled = False Then Exit Sub
  StrFilter = getFilter(strText)
  'MsgBox strFilter
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = StrFilter
  Set rsTemp = rsTemp.OpenRecordset
 
  If Not (rsTemp.EOF And rsTemp.BOF) Then
    rsTemp.MoveLast
    rsTemp.MoveFirst
    'Debug.Print rsTemp.RecordCount & " Count " & strFilter
  Else
    beep
    mAutoCompleteEnabled = True
  End If
  Set mCombo.Recordset = rsTemp
  If rsTemp.RecordCount > 0 Then
    If Nz(mCombo.Value, "") <> Nz(mCombo.Text, "") Then mCombo.Dropdown
  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 & " In Filterlist."
  End If
End Sub

 

[sxschech]

Made the change and at first nothing worked, no error, not finding the record either. I closed Access completely and reopened and so far, it is working fine without an error.

 

[MajP]

Made the change and at first nothing worked, no error, not finding the record either.

The find as you type class is instantiated on load of the form. If you throw an error and continue or go into design mode the instance goes out of scope. You have to reload the form. If not then it is not doing anything. You probably just needed to close the form and reopen.

 

[sxschech]

Good to know.

 

[coypu]

Here is an update of the FAYTs. I especially updated the fayt form and listbox. It is a little nicer in that you can specify which fields to search. In the multi field combo you can search one or all.
I cannot replicate the tab error. See if you can replicate it here, or provide your database.

MajP, thanks so much for this. Excellent example of OOP and “with events” as well as enhancing the functionality of combos and form filtering. I’m discarding loads of now redundant and hard to maintain code that handled comboboxes row source dynamically. I eagerly look out for your class module examples. Nicely done and much appreciated!

 

[GaP42]

MajP, using your code - v15 - specifically FindAsYouTypeCombo, I have found a little issue. I have a Form with tabs on which two combo controls are being used on the one tab that are initialised as you specify. However the first combo does not automatically filter on entry of text (the list is not displayed until I stop typing and use the drop down button) whereas the second does work as described by the your demo db. Now the thing is, if I reverse the order of the initialise commands for the two combos in Open Form event then the problem switches to the other combo and the first is working OK. [About to investigate a workaround - initialise a hidden combo on the form first]. Will let you know how it goes.
Thank you for your efforts.

UPDATE: No luck with adding a third (hidden) combo (and initialising first) - it seems that only the last combo initialised on the tab works correctly as a FAYT

 

[MajP]

Can you post an example? I have never seen this, and would have no idea what could cause such a thing. If not can you show your code how you initialize the combos?

 

[GaP42]

The case I have is shown here:

The first combo on the screen [Select locality]: Rubicon works as FAYT - the second: Select PO locality "RY" shows that the FAYT did not initiate correctly. Both are on a TAB control

Code to initialise:
faytAusPostIDs.InitalizeFilterCombo Me.cboPOLocality, "Locality", anywhereinstring, True, True

faytAusPostIDs.InitalizeFilterCombo Me.cboAusPostLocality, "Locality", anywhereinstring, True, True

cboPOLocality - is the Postal Address Combo
cboAusPostLocality is the Physical Address Combo

Swapping the order for initialising these combos toggles the problem from one combo to the other - and adding a third combo I found that it was only the last combo initialised that behaved.

 

[MajP]

The only thing i can think is there is an error happening after the first initialization and the second is not initialized. Is there any error handling? Can you post the complete code? I can have multiple FAYTs as shown in the demo.

 

[GaP42]

Not a lot to see in the Open event - and just to emphasise - the combo that is working correctly is the second one that is initialised - not the first. (it seems to be that all but the last one are not working when more than 2 combos are on the form/tab). No error handling on this event.

Private Sub Form_Open(Cancel As Integer)

Dim strPicLogo As String
Dim strPath As String
Dim strTablename As String

    strPicLogo = "logo.png"
    strTablename = "t_System"
    strPath = GetBackEndPath(strTablename)
    strPath = strPath + "\01_Graphics\"

    Me.Logo.Picture = strPath + strPicLogo
    
    Call ChangeFormProperty(Me.Form)

faytAusPostIDs.InitalizeFilterCombo Me.cboPOLocality, "Locality", anywhereinstring, True, True

faytAusPostIDs.InitalizeFilterCombo Me.cboAusPostLocality, "Locality", anywhereinstring, True, True

Me.OrgFullName.SetFocus 'to move to editable control

pgMembership.Visible = True


End Sub

 

[MajP]

Each FAYT needs its own module level variable. You only have one called faytAuspostids.

 

[MajP]

Look at the demo with multiple combos.

 

[GaP42]

Thanks - done, and working correctly - very much appreciated.

Public faytAusPostIDs As New FindAsYouTypeCombo
Public faytPOAusPostIDs As New FindAsYouTypeCombo

and

faytPOAusPostIDs.InitalizeFilterCombo Me.cboPOLocality, "Locality", anywhereinstring, True, True

faytAusPostIDs.InitalizeFilterCombo Me.cboAusPostLocality, "Locality", anywhereinstring, True, True