Combo lookup of characters anywhere in the name 3

[molly]

I have a form with a combo box used to lookup a Formula Name.

The below works. However, it is rigid and precise. EG if I type Black, then i get all formula names that begin with black.

But i want Black anywhere within the formulaname. EG Black pepper or maybe Pepper ground Black. I would want to see both name to pick from. How can i improve the below After Update?

Private Sub CboFormulaNameFilter_AfterUpdate()

DoCmd.ApplyFilter , _
"[Description] = Forms![frmPriceSpecificMain]![cboFormulaNameFilter]"
Me.CboFormulaNameFilter = Null

End Sub


thanks

 

[jedraw]

I think the issue you have is that when looking for
"Black" anywhere in the test, as compared to the start of the text is a different construct.

If you say, for example,

 textfield ="Black"

, access will compare the first 5 chars of textfield with "Black".

If you say,

textfield like "Black*"

, access will look for records beginning with "Black" and followed by any characters.

If you say,

textfield like "*Black*"

, access will look for "Black" anywhere in the textfield.

 

[molly]

Yes, Jed. I mean the last item that you say ...textfield like "*Black*"

where do i do this? is it in the combo's Row Source line on the criteria line ? if so, how?

thanks alot

 

[molly]

i think i said row source line by mistake. this is still an After Update deal, right ?? sorry for being so confused.
thanks

 

[dhookom]

Try:

Private Sub CboFormulaNameFilter_AfterUpdate()

  DoCmd.ApplyFilter , _
    "[Description] Like '*" & Forms![frmPriceSpecificMain]![cboFormulaNameFilter] & "*'"
  Me.CboFormulaNameFilter = Null

End Sub

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[molly]

Hi Duane. Thanks for the exact tip. It almost works. But I still do not see all my *pepper* products that have the word pepper somewhere within the description.

I still only get the one's that start with pepper.
Below is your code. FYI, the word NULL is in blue just like the Private and Endsub. I don't know if that means anything.

Private Sub CboFormulaNameFilter_AfterUpdate()
'===== 6-16-2006 Lagbolt method ===================
'===== 12-03-2007 Dhookup Duane Like method ======

DoCmd.ApplyFilter , _
"[Description] Like '*" & Forms![frmPriceSpecificMain]![CboFormulaNameFilter] & "*'"
Me.CboFormulaNameFilter = Null

End Sub

Do we need another command to "fire" it ?
thanks if you have time. This is an interesting use of the Combo box I think.

Molly

 

[molly]

Duane - thanks for your ideas. i sent something to UPC in your name in the name of my friend.

Yell if you can get the Like thing to work.

thanks
Molly

 

[dhookom]

I would try just set the filter property like:

    Me.Filter = "[Description] Like '*" & Me.CboFormulaNameFilter & "*'"
    Me.FilterOn = True
    Me.CboFormulaNameFilter = Null

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[molly]

Duane. Tried the new After Update above. Still does not give me record with part of the description.

Could there be a need for an On Change ??

So if in my combo box, i type say, beet
i am only getting records that start with the word beet.

so there is another trick needed.
thanks
Molly

 

[molly]

Just to be sure. Here is an example of the records that i would want when i type in Beet into the combo box.

Beets Pure
Red Beets 4 ounces
Diced Beet Sauce

so i would like to see these 3 items as choices, and then i click on the one wanted.
Molly

 

[GingerR]

Why are you using a combo box (since you are not picking items from a list)? Why not just a text box?

Usually folks use a text box and a button that launches the search code.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

GingerR,
I was just going to ask the same. The combo wizard likes to bind its control source to an ID/primary key value.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[molly]

One time, long ago, i had a On Change Event working along with the After Update event.

I am trying to resurrect it. Don't forget that when i choose the formula Name, i need the entire record to change on the form.

But here is something that i almost have working.
I just don't know how to reference the Qry

Can you tell me how to refer to QryFindFormulaName in the next On Change Event line?

Private Sub CboFormulaNameFilter_Change()
Me.CboFormulaNameFilter.RowSource = QryFindFormulaName
'Me.CboFormulaNameFilter.Dropdown
End Sub

the SQL of this QryFindFormulaName is:
SELECT tblProduct.Description, tblProduct.ProductID, tblProduct.PriceTypeID
FROM tblProduct
WHERE (((tblProduct.Description) Like '*" & Me.CboFormulaNameFilter.Text & "*'))
ORDER BY tblProduct.Description;


I have the After Update Event as:

Private Sub CboFormulaNameFilter_AfterUpdate()
'===== 6-16-2006 Lagbolt method ===================
DoCmd.ApplyFilter , _
"[Description] = Like '*" & Forms![frmPriceSpecificMain]![CboFormulaNameFilter] & "*'"
Me.CboFormulaNameFilter = Null
End Sub


I feel that i am getting close to this by just using ONE combo box for a formula name lookup filter which then pulls up the appropriate record in my form.

Anymore hints? i wanted to try the On Change idea before trying your button method.
Molly

 

[PHV]

You may try this :

Me!CboFormulaNameFilter.RowSource = "SELECT Description, ProductID, PriceTypeID" _
 & " FROM tblProduct WHERE Description Like '*" & Me!CboFormulaNameFilter.Text & "*' ORDER BY 1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

First, you have "=" and "Like" in the same statement. Should be just LIKE, with no "=".

Also your statement won't carry to the second line because you don't have an ampersand. It should be:

DoCmd.ApplyFilter , "[Description] Like '*" & Forms![frmPriceSpecificMain]![CboFormulaNameFilter] & "*'"

Private Sub CboFormulaNameFilter_AfterUpdate()
'=====  6-16-2006 Lagbolt method ===================
  DoCmd.ApplyFilter , "[Description] Like '*" & Forms![frmPriceSpecificMain]![CboFormulaNameFilter] & "*'"
  Me.CboFormulaNameFilter = Null
End Sub


Private Sub CboFormulaNameFilter_Change()
dim strSQL as string

strSQL = "SELECT Description, ProductID, PriceTypeID FROM tblProduct WHERE Description Like '*" & Me.CboFormulaNameFilter & "*' ORDER BY Description;"

Me.CboFormulaNameFilter.RowSource = strSQL

End Sub

I just tried this and it works for me. It's not my favorite as far as functionality goes (per the user's point of view), but it works.




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[molly]

Ginger - i am open to a better way.

I just tried your method. Everything works. I even added a criteria of 92 into the Change Event.
But when i run it, and type in BEEF,
i only get records that Start with Beef. EG the word Prime Beef will not show up because beef is the second word. but i wanted prime beef too to show in the pull down.

so there must be something else. in your test, did you put your test word as the 2nd or 3rd word too?

you are getting real close.
Molly

here is what i have so far on the name CboFormulaNameFilter events:

======== AFTER UPDATE EVENT ==========================
Private Sub CboFormulaNameFilter_AfterUpdate()

'2007-12-04 Ginger Method
DoCmd.ApplyFilter , "[Description] Like '*" & Forms![frmPriceSpecificMain]![CboFormulaNameFilter] & "*'"
Me.CboFormulaNameFilter = Null

End Sub


====== ON CHANGE EVENT ==========================

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

'2007-12-04 GingerR Method Tek-Tips
Dim strSQL As String

strSQL = "SELECT Description, ProductID, PriceTypeID FROM tblProduct WHERE (((tblProduct.Description) Like '*" & Me.CboFormulaNameFilter & "*') AND ((tblProduct.PriceTypeID)=92)) ORDER BY Description;"

Me.CboFormulaNameFilter.RowSource = strSQL

'figure out next, later on if needed or not
'Me.CboFormulaNameFilter.Dropdown
'need way to close the dropdown after i picked a description record.

End Sub

 

[GingerR]

What is this?

((tblProduct.PriceTypeID)=92))

This will only show the record where the PriceTypeID is 92. Why do you want to do that?

And yes I put in three records where the keyword was first (of two words), in the middle of two other words, and last (or two words). It worked.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[molly]

Hi Ginger. So your small model did work. Maybe my 2,000 records do something odd. I will try making a small model as a test and then use your code and see what happens.

The 92 is a code for my products. A product is either a 77 or a 92. I want to see only the products with this special code, when i am doing the lookup.

i will try to make time tomorrow to see if a small model with simple names works. and then i will throw in a few real descriptions, a little at a time.

Molly

 

[GingerR]

Size doesn't matter. Your WHERE clause is

Like *BLACK*

which means it should find things with BLACK anywhere in the phrase.

Is there any other code you have some place else?
As a test, make another cbo box, and in it's rowsource put

SELECT Description, ProductID, PriceTypeID FROM tblProduct WHERE (((tblProduct.Description) Like '*BEEF*') AND ((tblProduct.PriceTypeID)=92)) ORDER BY Description;

hardcode a word in there like BEEF and see if it returns the results you want. This shouldn't be this difficult.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[lespaul]

and in case you missed jedrow's explanation:

LIKE AC*

finds all records where the relevant field STARTS with AC
ACcount
ACcent

LIKE *AC

finds all records where the relevant field ENDS with AC
lilAC
prozAC

LIKE *AC*

finds all records where the relevant field CONTAINS AC
ACcount
ACcent
lilAC
prozAC
bACk
blACk
snACk

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com