How combo box can Drill Down results as i type a Name (1 Viewer)

[penwood]

How can i do the following? In a combo box, how can i type in say PEPPER and see every formulaName that contains Pepper in their description. I would get back Red Pepper, Orange Pepper, Black Pepper, Pepper, to choose from. Notice that Pepper can be anywhere inside the name.

I would like the choices change as i type. In other words, if i type BL, i would start to see choices like Black Pepper, Blue Pepper, Roger Blueboy, Green Bloak or any formulaname containing BL.

I presently have the following combo box. if i start typing BL and hit the down arrow, i will see formulas starting with BL. unfortunately, furtherdown, i also see all the other thousands of formulas in the base; meaning I see things that start with C, D, E etc.

I was hoping that i would see every formula containing the letters that i type, As I Type. so if i went further and typed BLAC, the formulas that almost made the cut, disappear from view, leaving me with things having BLAC in name, anywhere in the formulaName. Is this possible? I kind of want to DRILL down. when i finish typing black, i would have all formulaNames that have the word Black somewhere.

here is the combo box after update code:
----------------------------------------------
Private Sub CboFormulaNameFilter_AfterUpdate()
If Me![CboFormulaNameFilter] = "<All>" Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[FormulaID] = Forms![frmFormulaMain]![cboFormulaNameFilter]"
End If
End Sub
------------------------------------------------

============================
Here is the row source sql code:

SELECT DISTINCT tblFormulaMain.FormulaName, tblFormulaMain.FormulaID, tblFormulaMain.Description, tblFormulaMain.FormulaStatus
FROM tblFormulaMain
GROUP BY tblFormulaMain.FormulaName, tblFormulaMain.FormulaID, tblFormulaMain.Description, tblFormulaMain.FormulaStatus
ORDER BY tblFormulaMain.FormulaName;
===============================

FormulaID is a text field.

thanks
Penwood

 

[Laurentech]

I would probably use a separate unbound text box for the search term, then in the Keypress event change the filter of the combo box to something like

me.mycombobox.filter= "*" & me.textboxname & "*"
me.mycombobox.filteron=true

This first line will set the filter to *Black* for example, which will show any entries with black anywhere in the name.
The second line will cause the filter to be refreshed.

Larry

 

[penwood]

Larry - i must have done something wrong. i get all the records after typing in Black in the new field first.

i made a text box field named cboNameSearch.

Then i made the VBa change. I probably wrote something wrong.

==================================================
Private Sub CboFormulaNameFilter_AfterUpdate()

If Me![CboFormulaNameFilter] = "<All>" Then
DoCmd.ShowAllRecords
Else

'Next2 lines 6-16-2006 from Larry.
Me.mycombobox.Filter = "*" & Me.cboNameSearch & "*"
Me.mycombobox.FilterOn = True

DoCmd.ApplyFilter , "[FormulaID] = Forms![frmFormulaMain]![cboFormulaNameFilter]"
End If

End Sub
=================================================

too bad that i have to click down on the CboFormulaNameFilter field.

Penwood

 

[MarkK]

You could try using the Change event and referencing the .Text property of the control. This example only filters the FormulaName and Description fields, but you get the idea...

Private Sub cbo_Change()
  Me.cbo.RowSource = _
    "SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
    "FROM tblFormulaMain " & _
    "GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
    "HAVING FormulaName LIKE '*" & me.cbo.text & "*' " & _
       "OR Description LIKE '*" & me.cbo.text & "*' " & _
    "ORDER BY FormulaName;"
  Me.cbo.Dropdown
End Sub

Oh, and set the AutoExpand property of the control to false

 

[penwood]

Hi Lagbolt. I got an error with the last code.
did i code this correct?

my combo box with the arrows is called cboFormulaNameFilter.

was i supposed to continue Larry's idea using a 2nd box called cboNameSearch which has no arrows?

===================================================
Private Sub CboFormulaNameFilter_AfterUpdate()

Me.CboFormulaNameFilter.RowSource = _
"SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
"FROM tblFormulaMain " & _
"GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
"HAVING FormulaName LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"OR Description LIKE '*" & Me.CboFormulaNameFilter & "*' " & _
"ORDER BY FormulaName;"
Me.CboFormulaNameFilter.Dropdown

End Sub
======================================================


penwood

 

[penwood]

Lagbolt. i now notice your autoexpand note. i changed it from Yes to No.
interesting. when i tried BLUE (and it does not exist in my base), i get a nice error message telling me that blue does not exist. i like that.

so maybe you fellows can help fix my last post.
penwood

 

[MarkK]

Penwood:
You need to reference the "Text" property of the control as in the red bit below.

    "SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
    "FROM tblFormulaMain " & _
    "GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
    "HAVING FormulaName LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
       "OR Description LIKE '*" & Me.CboFormulaNameFilter[COLOR="Red"].Text[/COLOR] & "*' " & _
    "ORDER BY FormulaName;"

Give that a whirl.
Ya, the AutoExpand will do it's own little auto selection and then this routine will search for what that provided, so kill it.
Let me know if this works OK. Never tried it before, so curious if you can make it work.

 

[penwood]

thanks for the note Lagbolt. i see now. you want me to replace the code. this is not working yet. I can pick a formulaname that only starts with my choice eg BLA for black. but the record i picked is not pulled up. plus, if i try to repick, i only get records with black bean and does not give me all my records back.

i put Auto Expand put back to Yes.

i am now working with just the one combo box cboformulaFilter.

i start to type say Bla (for black) and hit the down arrow. i am taken to records that start with bla.
if i picked one, nothing happens. if i try to change my choice to say Gold, i am stuck with only the BLA formulanames.
so something is not kicking in to either come on the screen or to let me change it. i have a REset button that always worked but it also did nothing now. so this is some kind of refresh type of issue yet.

the after update code as it stands now:

Private Sub CboFormulaNameFilter_AfterUpdate()
'I set Auto Expand to Yes.

Me.CboFormulaNameFilter.RowSource = _
"SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
"FROM tblFormulaMain " & _
"GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
"HAVING FormulaName LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"OR Description LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"ORDER BY FormulaName;"

Me.CboFormulaNameFilter.Dropdown
End Sub

when i pick BLA, i get to the black bean formulanames. but i see the next set of products which do not not use BLA. i only want to see anything with BLA anywhere in the name. a different example: say i had blue car, dark blue car, red car and yellow car as formulanames. when i type in blue, i only want to see the blue car and dark blue car formulaname. not the rest of the cars. is that possible?

maybe we can do this programming in stages and build up to my ultimate goal.
penwood

 

[MarkK]

Did you try this under the Change event of the combo, not the AfterUpdate event?

 

[penwood]

OOOHHH wee! i think it is working now.

i am sorry for not understanding the need for ON Change event. i never used it before. also, it sounds to me that you are saying that your code is supplemental to my initial threat vba. correct?

in summary, here is what i have in just 1 combo box. not 2.
AFTER UPDATE EVENT SECTION OF cboFormulaFilter:
========================================

Private Sub CboFormulaNameFilter_AfterUpdate()

If Me![CboFormulaNameFilter] = "<All>" Then
DoCmd.ShowAllRecords
Else

DoCmd.ApplyFilter , "[FormulaID] = Forms![frmFormulaMain]![cboFormulaNameFilter]"
End If

End Sub
============================================



Now the ON CHANGE Event section that Lagbolt did:
=======================================

Private Sub CboFormulaNameFilter_Change()
'I set Auto Expand to Yes.

Me.CboFormulaNameFilter.RowSource = _
"SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
"FROM tblFormulaMain " & _
"GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
"HAVING FormulaName LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"OR Description LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"ORDER BY FormulaName;"

Me.CboFormulaNameFilter.Dropdown

End Sub
==================================

i use my RESET button to get all my records back for a new search. it still works. that's good right, to use the Reset button, Lagbolt??

i will test some more.

penwood

 

[penwood]

This procedure is almost perfect. You get my Thomas Edison award tonight.

You definitely followed my need. it does drill down to any formulaname containing the typed in characters. anyware: start, middle or end of a formulaname.

the only problem now is: say i typed in Sca. i get 5 records which have these 3 letters somewhere in the name. good so far. then add the letter "f". so now i have Scaf. and there is the one and only record that is needed. good so far. i click on the row. in comes the record. good so far.

i hit the Reset button to start a new search. good so far.

I cannot get rid of the Scaff record in the combo box. it hangs around preventing me from doing a new search. As Chico Marx of the Marx Brothers used to say. "that's a no good".

here is my Reset button if you think it is the culprit.

Private Sub cmdRESET_Click()
Me.FilterOn = False
End Sub

so i need a way for the combo box to get cleared out for a new search.
You are a gold mine answer to my request.
penwood

 

[MarkK]

I don't know what the after update event you have does. I'm not clear how the combo could ever = "<All>". Oh, you're applying a filter to the form.
So once that's done, set the value of the control to null, so you can use it again clean.

Private Sub CboFormulaNameFilter_AfterUpdate()
  DoCmd.ApplyFilter , _
    "[FormulaID] = Forrms![frmFormulaMain]![cboFormulaNameFilter]"
  Me.cboFormulaNameFilter = null
End Sub

I got best results with AutoExpand = false. You want the user to supply the characters for the search, not Access.

 

[penwood]

Lagbolt. I think you did it.

i changed the autoExpand to NO as you said. it did look better.
so for any other readers of your great piece, here is a summary for them.

Private Sub CboFormulaNameFilter_AfterUpdate()
'=====6-16-2006Lagbolt method ===================

DoCmd.ApplyFilter , _
"[FormulaID] = Forms![frmFormulaMain]![cboFormulaNameFilter]"
Me.CboFormulaNameFilter = Null
End Sub


'============================================
Private Sub CboFormulaNameFilter_Change()
'I set Auto Expand to NO.

Me.CboFormulaNameFilter.RowSource = _
"SELECT DISTINCT FormulaName, FormulaID, Description, FormulaStatus " & _
"FROM tblFormulaMain " & _
"GROUP BY FormulaName, FormulaID, Description, FormulaStatus " & _
"HAVING FormulaName LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"OR Description LIKE '*" & Me.CboFormulaNameFilter.Text & "*' " & _
"ORDER BY FormulaName;"

Me.CboFormulaNameFilter.Dropdown
End Sub
'===============================================

and i have a REset button:

Private Sub cmdRESET_Click()
Me.FilterOn = False
End Sub


thanks so much Lagbolt. It looks like i am in good shape thanks to you.
Penwood

 

[MarkK]

You're welcome Pen.