Wild card search on combo box with multiple rowsources

[misuser2k7]

I have 3 combo boxes on a form. The first combo box’s rowsource is a list of regions such as North, South, Midwest, West …… The second combo box’s rowsource is dependent on the selection of the region in the first combo box, for example, if 'Midwest' is selected in the first combo box, a query called MidwestSalesPerson becomes the rowsource for the second combo box. The MidwestSalesPerson query has just one column which is basically a list of the full names of the Sales personnel. The rowsource for the third combo box is a list of products which the sales person has been assigned to sell. This means that when a sales person is selected in the second combo box, the third combo box displays the list of products that sales person has been assigned to sell. This set up works as intended but the users want the ability to type in a random part of the first name or last name in the second combo box to have the full name displayed i.e. if they type ‘dav’ in the second combo box, only the Sales personnel with the first name or last name having letters d-a-v like David or Davis or Davies should be displayed in the second combo box.

I found this helpful page on this website:

https://www.tek-tips.com/faqs.cfm?fid=6295

The solution works great if I use one rowsource but I am having trouble customizing it for 5 rowsources. Also, it is written for having wild card between the letters typed i.e. if the user types ‘dav’, it displays all names which have the letters d-a-v (in that order) in them.
The dependent rowsources set up is implemented by the following if-then statement on the first and second combo boxes:

If Me.combo1 = "North" Then
Me.Combo2.RowSource = "NorthSalesPersonQuery"
Else If Me.combo1 = "South" Then
Me.Combo2.RowSource = "SouthSalesPersonQuery"
Else If Me.combo1 = "East" Then
Me.Combo2.RowSource = "EastSalesPersonQuery"
Else If Me.combo1 = "West" Then
Me.Combo2.RowSource = "WestSalesPersonQuery"
Else If Me.combo1 = "Midwest" Then
Me.Combo2.RowSource = "MidwestSalesPersonQuery"
Else
Me.Combo2.RowSource = “”
End If

Is there a way to adapt the solution presented on

https://www.tek-tips.com/faqs.cfm?fid=6295

so that it can allow users to type a limited number of letters in the names of sales staff and get only the names they are looking for in this manner?

Thank you all for your help.

 

[MajP]

May want to look at this version, it is pretty advanced in capabilities. Also you may want to search the forum on this topic. A lot of people have used the code and have done different things with it.
Faq702-6304
That code will work with your 5 queries and requires no additional code.