Find as you type combos

[AlekseijDR]

Hi, I downloaded the sample database, made by @MajP :

Find (Filter) as You Type Controls (Combobox, Listbox, Form)​

It works very well, but I have a problem on the NotInList event: using the class requery method (as advised to in comments), it works fine on a single form (I put the requery on Activate event). It fails on a continuous form, though.

EXAMPLE:
Before FAYT, I had a code like this:
cmbTownID_NotInList: Docmd.OpenForm "Towns", etc.
form Towns_Close: Forms![Customer]![cmbTownID].Requery

Now, with FAYT I deleted form Towns_Close event and have the following code:
form Customers_Activate: FAYT.requery

It works well if Customers is a single form (although I don't know it's good performance-wise to have a combobox requery every time the form is activated); it fails if Customers is a continuous form.

Thank you very much for your time!

 

[CJ_London]

What does ‘it fails’ mean? You get an error? Nothing happens? Something else?

your code as provided won’t compile anyway

 

[AlekseijDR]

When I open Customers form as a continuous form, I get the 3078 error, on Public Property Let RowSource, at row:
Set RS = Currentdb.OpenRecordset(NewRowSource)

 

[MajP]

I do not understand the activate event. Where and why call the activate event?
The following works for me

Private Sub cmboProduct_NotInList(NewData As String, Response As Integer)
  DoCmd.OpenForm "frmProducts", , , , , acDialog
 'must open acdialog to stop code execution
  Response = acDataErrAdded
 'must requery the class
  faytProducts.Requery
End Sub

 

[MajP]

However, you may have to verify that once you close "Towns" you did in fact enter the new value without modification This is one reason I am not a big fan of the not in list even unless it is a very simple list. If it is a single value then I will normally prompt if they want to add to the list without opening a form. But most of the time there is additional fields that are required. So I often have a way for the user to force "Add to List" manually. Either an add icon or a double click on the combobox. Then I standardize the process throughout the db.

 

[AlekseijDR]

Yes, you're right, I simply forgot that opening a form in dialog mode stops code execution (I created sort of a custom function for dealing with NotInList events years ago and actually never re-edited it since).

Thank you very much for all your time!