Solved FAYT for Multi Column combo code from MajP question (1 Viewer)

sxschech

Registered User.
Local time
Yesterday, 23:19
Joined
Mar 2, 2010
Messages
791
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:
Code:
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

Code:
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
Code:
Option Compare Database
Option Explicit


Public FAYTGotoMessage As New FindAsYouTypeCombo

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
If you need to requery the FAYT there is a method in the class called requery.
It is a long story but doing

Code:
cboGotoMessage.requery
will not cause the FAYT to requery.
You must do
Code:
FAYTGotoMessage.requery
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • MajP FAYT V15.accdb
    1.3 MB · Views: 481

sxschech

Registered User.
Local time
Yesterday, 23:19
Joined
Mar 2, 2010
Messages
791
Thanks for the instructions and update. I will try them out today and report back.
 

sxschech

Registered User.
Local time
Yesterday, 23:19
Joined
Mar 2, 2010
Messages
791
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.
Code:
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

Registered User.
Local time
Yesterday, 23:19
Joined
Mar 2, 2010
Messages
791
Came across error 2185.

Error2185.PNG


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

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
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
Code:
If Left(TypeName(mCombo), 4) = "Form" Then mCombo.SetFocus
to simply
Code:
mCombo.setfocus


Code:
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

Registered User.
Local time
Yesterday, 23:19
Joined
Mar 2, 2010
Messages
791
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

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
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.
 

coypu

Registered User.
Local time
Today, 06:19
Joined
Mar 12, 2019
Messages
26
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! (y)
 

GaP42

Active member
Local time
Today, 17:19
Joined
Apr 27, 2020
Messages
310
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
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
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

Active member
Local time
Today, 17:19
Joined
Apr 27, 2020
Messages
310
The case I have is shown here:
1652967405691.png

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

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
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

Active member
Local time
Today, 17:19
Joined
Apr 27, 2020
Messages
310
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.

Code:
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

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
Each FAYT needs its own module level variable. You only have one called faytAuspostids.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:19
Joined
May 21, 2018
Messages
8,463
Look at the demo with multiple combos.
 

GaP42

Active member
Local time
Today, 17:19
Joined
Apr 27, 2020
Messages
310
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
 

Users who are viewing this thread

Top Bottom