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

[dgreen]

Here's my existing code. Thoughts?

Private Sub Define_ID_NotInList(NewData As String, Response As Integer)
'https://datatofish.com/how-to-create-to-a-yes-no-message-box-in-ms-access/
'Enhanced Message Box Replacement
'http://blog.nkadesign.com/2008/05/20/ms-access-enhanced-message-box-replacement/#comment-28080

    Dim ClickResult As VbMsgBoxResultEx
    ClickResult = Dialog.RichBox("The acronym/term doesn't appear to be in the database.  Would you like to add it?", vbYesNo + vbCritical, "Not on list", , , 0, False, False, False)
    If (ClickResult = vbYes) Then
    
    'VBA Command to be executed if Yes is selected
    'Removes the text you typed in that wasn't on the list and deletes the row that was just created
        Me.Undo
        
        'https://access-programmers.co.uk/forums/showthread.php?t=132051
        'Closes the form you had tried to add a value to that failed.
        DoCmd.Close acForm, "f_Key_Events", acSavePrompt
        'Opens the form you are going to re-search for the value before adding it as a new record.
        DoCmd.OpenForm "f_Definition"
       
        'https://docs.microsoft.com/en-us/office/vba/api/access.form.setfocus
        Forms!f_Definition!textsearch.SetFocus
        ClickResult = Dialog.RichBox("Please check for the acronym/term again before adding a new record", vbOKOnly, "Check before adding", , , 0, False, False, False)
        'Turn off the NotOnList Error
        Response = acDataErrContinue

    ElseIf (ClickResult = vbNo) Then
    'VBA Command to be executed if No is selected
    'Highlights the text you typed in since you're going to try and figure out what the real abbreviation is.
        Response = acDataErrContinue
        Me.Undo
        Me.Define_ID.SetFocus
        Exit Sub
    End If
End Sub

 

[MajP]

I just never use the not in list event. 99% of the time the thing I show in the combo is not the thing I am storing in the field. Because I am storing a FK. So the new data is worthless to me. I need a new PK. If using a not in list event this can get real complicated. You add a new record "Dog" but does not have a PK yet. So you have to comeback to the calling form, then search for dog and see if there is a new PK for dog. But when the user went to the popup form they decided the real value is Brown dog. It just becomes a huge PIA. If you get it to work great. I normally just have an add icon next to my listbox or a label that says double click to add. That way the add and close the pop up. Then pick the new value from the list. That is just my personnel opinion on how I like to do it and just simpler for me. With the not in list you can get into circular logic problems.

 

[dgreen]

I'm learning. My thought was that having some sort of a workflow with popups, undos and taking people to the next form to filter for the value again might help reduce the # of duplicate records in the database and reduce what I have to clean up.

 

[dgreen]

Also would you have expected the FAYT to work with a query that is pulling from 2 connected tables? Back in a previous post, I showed where I was having issues with a joined table field.

3071 error. This expression is typed incorrectly or it is too complex to be evaluated. Below visual of query.

 

[dgreen]

Screen capture of a time code look up, where the description would be key to figuring out which code to use. The description field is long text and the base table doesn't have a short title.

Can you do a screen shot of what you are trying.

 

[MajP]

Also would you have expected the FAYT to work with a query that is pulling from 2 connected tables?

Yes it should work, but there may be an issue with two nulls. In the concatenation add to the end
& ""
This way if a null is returned it concatenates an empty string

Can you export me a table with the timecode description that you are showing above. I want to see if I can generate the error. Include the fields in the image above.

 

[dgreen]

stand by.

 

[dgreen]

Same error of it being too complex.

Here's a sample dataset. Instead of using the time codes, I think this one would illustrate the challenge better. 2 tabs in Excel. 1st are various Document Types (Primary Key is DOC_Type_ID). 2nd has a couple documents that have both the DOC_Type_ID as a grouping AND a long text field (Doc_Summary).

I'm trying to get the FAYT to allow me to 1) show the name of the document and the document type and 2) allow me to search thru the document summary to find my results. Even if one of these is solved, it's a great improvement.

Yes it should work, but there may be an issue with two nulls. In the concatenation add to the end
& ""
This way if a null is returned it concatenates an empty string

 

[MajP]

Looks like the doc summary was truncated to 255 characters. That may be because it is a rich text field.

 

[dgreen]

Since this is just a proof of principal, just add a couple extra characters to get it over the 255 limit. Easier to do it on your end.

 

[MajP]

If you cannot get to export. I will try with notional data. I will concat a short text with long text and see if there is an issue.

 

[dgreen]

Please do.

I will try with notional data.

 

[MajP]

I tried with notional data and I also fail on the concatenation. I get a different error "Unknown 1006". However if I add Doc_Abr and Doc_Summary to the rowsource and search on all fields (with the new version) it works.

 

[dgreen]

And how about pulling values from 2 different tables in order to search?

 

[MajP]

works fine for me as long as you add to separate fields and do not concatenate them

 

[dgreen]

It's working on this end. Thanks for revising the code to allow for this flexibility.

search on all fields (with the new version)

 

[dgreen]

I took the complete class module (FindASYouTypeCombo) from v11 and put it into the database that we're troubleshooting on the other thread.

13 Type mismatch when I allow the following to run on form load.
faytManagerNameID.InitalizeFilterCombo Me.Manager_Name_ID, , anywhereinstring, True

I changed the data source to not have any concatenations but that didn't seem to make a difference.

SELECT t_Contacts.Name_ID, t_Contacts.Last_Name, t_Contacts.First_Name, t_Organization.Organization FROM t_Contacts LEFT JOIN t_Organization ON t_Contacts.ORG_Child_ID = t_Organization.Org_Child_ID;

I mirrored the code you had on your example removing the specific field to search and the extra ,true/false.

Can you troubleshoot this one?

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.