Find as you type Combo issue (1 Viewer)

[MajP]

Can you clearly state how you want the not in list to work. The reason is that this should be coded in the class module which I never actually added. In general you want to avoid using the methods of the control and instead use the methods of the class. For example the class has a requery which is how you should requery and not use the controls method because there is a whole lot more going on behind the scenes of the class.

To keep it simple I would have a popup form to add information. The user would supply the form name when instantiating an FAYT and the class would do the rest. This way the user does not have to write any code for the not in list event. I will see if I can do a demo of that.

 

[sxschech]

Thanks MajP. That would be great to have a demo as I haven't really coded with class modules. Read some articles, but hasn't sunk in (yet).

To keep it simple I would have a popup form to add information.

That is what we already have. However, due to the combo issue, when the user enters a name in the Employee Name search combo box, if the name is not found, they need to click on the add new employee form button which closes the main form, opens the popup for adding new employee form and then upon close of the popup reopens the main form.

Will your revision also resolve the other issue which is that if the user picks a name via the mouse, they get error 2185, whilst if they use keyboard there is no error?

I have attached a new copy of the stripped down db. I removed most of the data and added some sample data, and deleted the reports since they are branded and aren't relevant to the discussion. If you need further clarification, please let me know.

 

[GinaWhipp]

Well, that took a minute. Your big problem is you have too much going on. Things are set when the Forms open that are interfering with what Access naturally does. Once I removed the On-Load event of that main form and a few other things the Not_In_List event of that combo box works perfectly. Biggest problem there was you set the FindAsYouType On_Load and it causes the Not_In_List event not to fire properly.

So, you need to rethink what you are doing. You can't have all your cool stuff in one place or everyday routines will misfire. If you want to use this a Search Form great but perhaps make them go elsewhere to add a new employee. And yes, your add new Employee would also work but again the FindAsYouType On_Load is also interfering with that. Just realize some Functions do not play nice together.

 

[MajP]

I will provide a fix tonight. I added the feature to requery the FAYT. It is not that the FAYT could not be requeried but it was setting it back to the original recordset once it requeried. I created a notinlistevent into the class. Now if you provide a form name it will do the following. It will fire the not in list event and open the form. You can then add new employee and it will requery. The only issue is that when you requery it will lose the value in the combobox. The value will be in the list, but you need to start over. The mouse error is a very strange issue that has to do with a subform. I have to go look for the fix because I forgot what the solution was. What happens is that if the subform has no records the combo loses focus for some reason and you cannot set the focus in code.

 

[sxschech]

MajP:
Thanks for taking time updating your code to have more capabilities. Hope it isn't too much trouble for you.

GinaWhipp:
Thanks for your suggestions.

perhaps make them go elsewhere to add a new employee.

This is what I'm currently doing as mentioned in several of the previous replies.

if the name is not found, they need to click on the add new employee form.

We like using the Find as You Type because it has cut down on thinking that people are not in the db as we update the training via "Sign in Sheets" where people attend a class and hand write/sign their name so if we are not familiar with that person's handrwriting and they are less legible, the FAYT lets us try various partial names to see what's nearby. I have suggested they use preprinted sign in sheets, but apparently was told many of trainings happen off-site and they don't know ahead of time who will be attending due to various shifts/schedules and for on site, if it is a large group, they put out several sheets in a free-for-all, otherwise they would be having to print out too much paper with everyone's name on it and then for those doing the data entry, would be a trade-off in time because would have to flip through a lot of extra packets in order to consolidate all the names.

 

[MajP]

Try this update. The issue is that the class module made it impossible to requery the combox. When you instantiate the FAYT it saves the original recordset of the combo. Then it filters and unfilters that recordset. When you requery I need to force it to get a new recordset, if not it applies the original recordset (the rs when you open the form) . So I added a requery procedure to allow you to requery the FAYT. The only issue is that you HAVE to requery the FAYT class and not your combo. Then I added this event to your form

Private Sub cboNameSearch_NotInList(NewData As String, Response As Integer)
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "New Employee")
    If i = vbYes Then
        DoCmd.OpenForm "frm_dt_EmployeesNew", , , , , acDialog
    End If
    'Use the class requery method
    FAYTnames.Requery
End Sub

This is a little weird because the idea of the custom class is to encapsulate all functionality. Here some of the functionality is in the class, the not in list event is with the control, but the requery is with the class. I am debating if I should put this all in the class. The other issue was solved with a line to set focus prior to filtering (mCombo.SetFocus).

 

[MajP]

To me the FAYT features are worth it when you have a large number of records. The following really demos the power. I can find any person by multiple fields in a second. Even with 10k records. But the real power of coding like this is that the user only has to write two lines of code and gets all of that functionality.

Private Sub Form_Load()
  Set FAYT_List = New FindAsYouTypeListBox
  FAYT_List.InitializeList Me.lstAllRecords, Me.txtSearch, "Full_Name", AnywhereInString
End Sub

 

[sxschech]

MajP, thanks for the code revisions. I downloaded and opened the file and received error 9 subscript out of range. Form Opens after clicking OK.

The good:
I copied the class into my database and am now able to use the mouse to select people - works great and no error.

The not so good?
I copied the Not In List code and tried adding a new person, once the person is added and I close the Add New form, the pop up returns saying not in list. After clicking ok, it does show the newly added person in the combo, however, selecting that person in the combo does not display their record on the training form. Is there another requery that needs to take place to sync the combo with the form?

In case I missed a step, I also tried adding a new person in the demo file and the same thing happens.

For now, not to trouble you further we will continue using the current method of adding new employees by clicking the button rather than use the Not In List event. Appreciate your help and getting the combo to work using the mouse to select someone.

Should I mark as resolved or shall we explore this a bit further?

 

[MajP]

The subscript out of range is in your compact BE code.
The reason you do not see the new persons information is because you have added the person to the underlying table, but you have never requeried the forms recordset. In the afterupdate event of the combo add a me.requery to requery the form. Do not but the me.requery in the notinlist code because that will put you in an endless loop.

 

[Dreamweaver]

I use A number of fuctions for my not in list


This is redundent As if the list is empty the notinlist will never fire

 'Exit this sub if the combo box is cleared     If NewData = "" Then Exit Sub

This is how I call my function and deal with the results

Response = FillListsOne(NewData, "LtblOrderMethodLookup", "OrderMethod")
If Response = acDataErrContinue Then
RunCommand acCmdUndo
End If

You don't need to Requery the list using this method


I think you will find at least one of these fuctions in my employees example here:
https://www.access-programmers.co.uk/forums/showthread.php?t=303671


Look in Modgeneral for fuction FillListsOne() Use the find to go to the calling forms


hope it helps


mick

 

[sxschech]

MajP, thanks, I forgot to remove the compact code since it was customized for our network.

Ok, I think I got it working. I tried your suggestion about the requery and still had an issue. After stepping through, I couldn't see the cause as the popup happens after the sub ends. Anyway, before giving up, I remembered JDraws suggestion from Post#3 and unlike before, now with your FAYT class modification it worked.
-Removed the Not In List Event
-Modified the combo properties List Items Edit form
-Added Forms!frm_dt_employees.FAYTnames.Requery to the Close Event of frm_dt_EmployeesNew
-Created a Tag property to conditionally Requery the main form rather than everytime the after update event is called.
-Added Forms!frm_dt_employees.cboNameSearch.Tag = "Added" to the Close Event of frm_dt_EmployeesNew
-Added code to the AfterUpdate Event of the combo

If Me.cboNameSearch.Tag <> "" Then
	Me.Requery
	Me.cboNameSearch.Tag = ""
End If

-Added Me.cboNameSearch.Tag = "" to the Load event of the form

-----------------------------
MickJav, thanks for your post, it looks like your code is more for entering the specific data from the not in list without a form, whereas we are needing to open a form to add several data items.

 

[Dreamweaver]

Hi it can work that way just with a few lines of code I'll post the filllisttwoitem in the morning it's very simple I do it with my books database categories and classes

 

[MajP]

Hi it can work that way just with a few lines of code I'll post the filllisttwoitem in the morning it's very simple I do it with my books database categories and classes

Not with the FAYT; it will not work. The FAYT keeps a pointer to the original recordset (that is how it works), so you have to use the requery method of the FAYT or you will never update the FAYT.

After stepping through, I couldn't see the cause as the popup happens after the sub ends

If you were to use the not in list event and do not want to see the message you have to add the response = acdataerrorcontinue. The response is what determines the message and I had taken it out for testing. It would be something like.

Private Sub cboNameSearch_NotInList(NewData As String, Response As Integer)
    Dim i As Integer
    Dim Msg As String
    
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "New Employee")
    Response = acDataErrContinue
    If i = vbYes Then
        DoCmd.OpenForm "frm_dt_EmployeesNew", , , , , acDialog
        FAYTnames.Requery
    Else
      cboNameSearch.Value = Null
      cboNameSearch.Undo
      FAYTnames.unFilterList 'need to make this a Public method in the
class. It is private now.
    End If
End Sub

However, I am also not a fan of the not in list event. I usually have an icon next to my combo similar to what you have to enter values. With the not in list you can get into a circular logic.

 

[sxschech]

Good to know was a one line command. I'll continue with the non-code version so as not to worry about the issues brought up if I were to use the not in list event.

I took a look at your other download on multi columns and that got me thinking about a different project utilizing this type of search. Can the multi column search multiple cols via combobox rather than having to specify the col? That is to say, if person wanted to search by both full name or email in the same search. Or is that too complicated to implement?

 

[MajP]

Can the multi column search multiple cols via combobox rather than having to specify the col?

Yes. I attached a FAYT listbox. I never coded the multicolumn combobox because it did not seem like something I would use and would not be intuitive as a control IMO. However, it would be simple to code since it is the same code as in the listbox. If using multi columns I would go with the listbox, IMO a better interface. Click the button search all fields. You can put in a date, number, or text. Also the fields are sortable.