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

sxschech

Registered User.
Local time
Today, 00:04
Joined
Mar 2, 2010
Messages
729
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, 03:04
Joined
May 21, 2018
Messages
5,890
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, 03:04
Joined
May 21, 2018
Messages
5,890
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: 133

sxschech

Registered User.
Local time
Today, 00:04
Joined
Mar 2, 2010
Messages
729
Thanks for the instructions and update. I will try them out today and report back.
 

sxschech

Registered User.
Local time
Today, 00:04
Joined
Mar 2, 2010
Messages
729
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
Today, 00:04
Joined
Mar 2, 2010
Messages
729
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, 03:04
Joined
May 21, 2018
Messages
5,890
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
Today, 00:04
Joined
Mar 2, 2010
Messages
729
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, 03:04
Joined
May 21, 2018
Messages
5,890
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.
 

Users who are viewing this thread

Top Bottom