Filter Combo As You Type (Prefix) (1 Viewer)

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
Closer but not quite there. The select statement changed. For example the last record I was adding "RI" for the state and the RowSource captured it. BUT when you click on the Combo13 dropdown list, it shows every option.
1584918149543.png

Looking at the Query builder's results
1584918334585.png


But the drop down results are showing something different.
1584918206437.png
 

Attachments

  • FAYT_Challenge v6.zip
    60.7 KB · Views: 142

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
Interesting. On your version I had to leave the rowsource completely blank, but not on the version I had.
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
So how do we proceed? I'm liking this direction, just need to figure out what the actual issue is.
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
@MajP As I'm trying to implement your FAYT capability across my database, I've come across this situation. Error 13 Type mismatch.

I've isolated the error message to the Manager_Name_ID and Assistant_Name_ID combo boxes (I commented just them out and the error went away). If we can fix one, then I'll know why.

I'm not sure if it's because the Manager_Name_ID is in the form's footer or something else.

I have a master table of Contacts. The Primary Key is Name_ID (Integer - Autonumber).
For each person (Contact), I have a fixed field of a 1:1 for a Manager. Manager_Name_ID is a Number (double).
My combo box is trying to use your FAYT to look up a Name and then store the result in the Manager_Name_ID field for the Contact.

The combo box value being stored is Name_ID which is an integer. It is the first column in the combo box.es results.

1584921239298.png
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
What is contact_search? is that a concatenated field of firs and last name?
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
Yes, and a couple other fields.

Contact_Search: [prefix] & "-" & [Last_Name] & "-" & [First_Name] & "-" & [Email_Address] & "-" & [other_email] & "-" & [t_Organization]![Organization]

What is contact_search? is that a concatenated field of firs and last name?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
For test purposes try it against the last name field instead. I think it should handle strings with non standard characters, but there is a lot going on in that field. If not the error is somewhere else. Can you see where the code breaks?
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
When the vba code hits the public function for the Manager_Name_ID line, it went to Public Function IsBlank, I have in the database.

1584927871404.png


Hovering over the arg shows "arg = "HQDA G-1"". It then appears to be going thru every Organization value in the database on this below loop for some reason.
1584927394408.png

Code:
'-----------------------------------------------------------------------------
' True if the argument is Nothing, Null, Empty, Missing or an empty string .
' http://blog.nkadesign.com/2009/access-checking-blank-variables/
'-----------------------------------------------------------------------------
Public Function IsBlank(arg As Variant) As Boolean
    Select Case VarType(arg)
        Case vbEmpty
            IsBlank = True
        Case vbNull
            IsBlank = True
        Case vbString
            IsBlank = LenB(arg) = 0
        Case vbObject
            IsBlank = (arg Is Nothing)
        Case Else
            IsBlank = IsMissing(arg)
    End Select
End Function

It pops out of this loop, back in the Public Sub InitalizeFilterCombo.

I'm out of mental energy on why this isn't working.
 

Attachments

  • 1584927838116.png
    1584927838116.png
    22.8 KB · Views: 148

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
No idea how that code is called. My first guess this has nothing to do with the FAYT and it is other code you have.
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
I wouldn't disagree. It's a hodgepodge database. Unfortunately for me, it compiles.

It's interesting that it's running into this error in between 2 FAYTs.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
If you get rid of the rowsource should work fine.
 

Attachments

  • FAYT_Challenge MajPv6.zip
    74.7 KB · Views: 200

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
@MajP Narrowing down on the issues with a couple other FAYT combos. I'd like for the FAYT combo to be able to handle the below variations to support my searching. Is it possible?

1) I know the FAYT works using just a field from one table in the query search.
2) Each of the below queries that I use as variations run in the query view.
1584969841778.png


1006 Unknown error when I have a Long Text (Doc_Summary) as part of the search query field. Below visual of query.
1584969941366.png

3071 error. This expression is typed incorrectly or it is too complex to be evaluated. Below visual of query.
1584970014715.png
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
The updated faytcombo class lets you specify a filter field or leave it blank to filter on all text fields. See the last combo in the demo form.
I have not tried it on a long text field, because in my mind a fayt with a memo field does not make much sense to me. Depending on the size of the memo those are likely access errors that will occur.
 

Attachments

  • MajP FAYT V11.zip
    161.9 KB · Views: 196

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
My scenarios are that I have a tables with acronyms with definitions, regulations with long text summary field, codes with how they are used. In many cases, it's longer that the 255 character short text format. It's to help the deeper searches for a specific field when you need more information to make a selection from a list.

long text field
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
My scenarios are that I have a tables with acronyms with definitions, regulations with long text summary field, codes with how they are used. In many cases, it's longer that the 255 character short text format. It's to help the deeper searches for a specific field when you need more information to make a selection from a list
I get needing features to do that search, but not in a multi field combobox. If it is that complex then have a popup form with a FAYT listbox. Can you do a screen shot of what you are trying.
If you are trying to search in any query multiple fields with a like and multiple memo fields the chance of making a query that access cannot evaluate is high. If the search is that complex I would provide a different user interface.
 

dgreen

Member
Local time
Today, 10:52
Joined
Sep 30, 2018
Messages
397
It will be a while. I'm trying to clean up my Not on List code now that I've pulled in your FAYT capability.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
Doing a not in list with the FAYT will may likely get complicated. If you requery the combo you have to use the requery of the class and not the combo. You may be better off with adding a double click to the combos to open a form to allow the user to add a new list item.
 

Users who are viewing this thread

Top Bottom