Form lookup button for partial description 2

[molly]

I need to lookup records with a partial name. EG If I type pepper, i would like to see all records that have the word Pepper somewhere in the description.

My current After Update only gives me records that "begin" with Pepper. However, I would like to see my choices for Pepper if the word is in the middle or end of a description.
Anywhere.

You could use the word MOLLY as a test. I made 4 records with the word Molly at the beginning, middle and end of a description.

My sample file is at:

http://www.savefile.com/files/2010220

I could not find an example on our site.
Thanks so much.

 

[Remou]

I tried your code with Molly, and it works.

http://lessthandot.com

 

[molly]

Hi Remou. Thanks for responding to this puzzling question. If I type in the word Molly in the yellow form lookup field, i was expecting to see just 3 records show up when I hit the down arrow. and then i would pick one of the 3 lookup records. It still does not work. Here is what should appear after I type Molly

ProductID ProductFamilyID Description PackTypeID PriceTypeID ProductType Inactive Product
702337 702337 PEPPER BLACK Molly (14 MESH-RESTAUR 710 77 RAW N

707368 707368 PEPPER, BLACK (WATER SOLUBLE) molly 710 77 RAW Y

710001 222222 Molly Red Pepper Dodgers 710 77 RAW N

See how the word molly is at the beginning, the middle and the end of a description. I am only seeing the beginning record. Plus, I do not want to see any other records when i pull down the arrow. just the 3 records to pick from.

Does this clarify? Thanks in advance for looking at the sample file. Molly

 

[Remou]

That is a different thing altogether. What you are getting is three records in your main form. You need to change the row source of your combo if you want to limit the list. I do not have Access available at the moment, but try something like:

strFilter=Me.NameOfYellowCombo.Text
Me.NameOfYellowCombo.RowSource="SELECT ID, Product, Etc " _
& "FROM Products WHERE Product Like '*" & strFilter & "*'"

http://lessthandot.com

 

[molly]

Hi Remou. I had trouble interpreting. Can you send this after update when you get time. thanks Molly.

 

[molly]

Here is what I originally had in my lookup fields row source, shown here in sql format:

SELECT QryProduct.Description, QryProduct.ProductFamilyID, QryProduct.ProductID, QryProduct.PackTypeID, QryProduct.PriceTypeID, QryProduct.ProductType, QryProduct.[Inactive Product]
FROM QryProduct
ORDER BY QryProduct.Description, QryProduct.ProductFamilyID, QryProduct.ProductID;


Here is what i have in the After Update Edit Procedure line:

Private Sub cboDescriptionFilter_AfterUpdate()

DoCmd.ApplyFilter , "[Description] Like '*" & Forms![frmProduct]![cboDescriptionFilter] & "*'"
Me.cboDescriptionFilter = Null

End Sub

So I am hoping to use the forms lookup field to find part of a formula name no matter where the word sits in the formula name. I think this is a hard one for everyone. I haven't seen anything like this need on the internet. Molly

 

[Remou]

Try this instead of after update, it is the change event:

Private Sub cboDescriptionFilter_Change()

strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart - 1)

strSQL = "SELECT q.[Description], q.[ProductFamilyID], q.[ProductID], " _
& "q.[PackTypeID], q.[PriceTypeID], q.[ProductType], q.[Inactive Product] " _
& "FROM QryProduct AS q"

strOrder = "ORDER BY [Description], [ProductFamilyID], [ProductID];"

'This will select all if cboDescriptionFilter is empty
strFilter = "WHERE [Description] Like '*" & strText & "*'"

'Change Row Source
Me.cboDescriptionFilter.RowSource = strSQL & " " & strFilter & " " & strOrder
Me.cboDescriptionFilter.Dropdown
End Sub

http://lessthandot.com

 

[molly]

Remou: You almost have it. You have it good where just the 3 records for the word "molly" appear. I hit the pull down arrow. I received an error message "invalid procedure call or argument". I hit debug. The first row is highlighted in yellow:

strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart - 1)

You are getting close. I attached a file LikeCombo2 as your next test. Thanks. Molly

 

[PHV]

Why not simply replace this:
strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart - 1)
with this ?
strText = cboDescriptionFilter.Text

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

 

[Remou]

The combo has autocomplete, so .text will return the full line with the part that has autocompleted highlighted, which means that using .text will return only one answer if the first letters match an existing entry. My code was tested. I will recheck later.

http://lessthandot.com

 

[Remou]

It would be best to change the Cycle on the forms Property sheet (Other tab) to Current
Record, otherwise you will get disconcerting results from pressing return.

Private Sub cboDescriptionFilter_AfterUpdate()
'Find the selected record
    Me.Recordset.FindFirst "Description='" & Me.cboDescriptionFilter & "'"
End Sub

Private Sub cboDescriptionFilter_Change()

If Me.cboDescriptionFilter.SelStart > 0 Then
    strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart - 1)
Else
    'With a polite nod to PHV :)
    strText = Me.cboDescriptionFilter.Text
End If

strSQL = "SELECT q.[Description], q.[ProductFamilyID], q.[ProductID], " _
& "q.[PackTypeID], q.[PriceTypeID], q.[ProductType], q.[Inactive Product] " _
& "FROM QryProduct AS q"

strOrder = "ORDER BY [Description], [ProductFamilyID], [ProductID];"

'This will select all if cboDescriptionFilter is empty
strFilter = "WHERE [Description] Like '*" & strText & "*'"

'Change Row Source
Me.cboDescriptionFilter.RowSource = strSQL & " " & strFilter & " " & strOrder
Me.cboDescriptionFilter.Dropdown

End Sub

http://lessthandot.com

 

[molly]

Dear Remou and PHV. This works great. I will donate.

But I have a 2 questions.

1 - I am not a VBA person. I see that there is a "where" statement missing. maybe something like this:
WHERE (((QryProduct.PackTypeID)=710) AND ((QryProduct.PriceTypeID)=77))


I assume that a similar statement needs to get into the "change" statement. Can you do? I cannot.

2 - Which opens up a newer quesiton.
Instead of SQL, can that section be made to refer to QryProduct? something like a recordsource thing. Just wondering. If this refers to a qry, i can handle myself later since i don't understand vga much.

I will donate now. Maybe you can get this criteria inside the statement and secondly make those statement look to a QryProduct for my simplicity ?
thanks MOlly

 

[Remou]

That should be:

Private Sub cboDescriptionFilter_AfterUpdate()
    'Unique product ID column numbering from zero.
    Me.Recordset.FindFirst "ProductID=" & Me.cboDescriptionFilter.Column(2)
End Sub

http://lessthandot.com

 

[molly]

Remou - that was great. It works nicely. I will gradually learn it. Attached is LikeComboBox3 for reference.

This is a major thing that you did. You should write a whitepaper or something.

Molly

 

[molly]

To Remou. I found a glitch. Can you still help me?

I discovered that if I type in RUB that I get all products that have RU within their name. But I only wanted products that have RUB somewhere within their name.

So for some reason, the "B" is being ignored.
Anything to do with that minus 1 that I saw in the VBA ?

I put LinkComboBox4 onto savefile. You could use RUB as the same lookup name. I am using frmProduct1 the best one.

Thanks an awful lot. This is a good study for you. I have never seen anyone ever program this need before.
Molly

 

[Remou]

If you have an idea, it is always worth making a copy and trying it out.

Private Sub cboDescriptionFilter_AfterUpdate()
    'Unique product ID column numbering from zero.
    If Not IsNull(Me.cboDescriptionFilter.Column(2)) Then
        Me.Recordset.FindFirst "ProductID=" & Me.cboDescriptionFilter.Column(2)
    End If
End Sub

Private Sub cboDescriptionFilter_Change()

If Me.cboDescriptionFilter.SelStart > 0 Then
    strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart)
Else
    strText = Me.cboDescriptionFilter.Text
End If

strSQL = "SELECT q.[Description], q.[ProductFamilyID], q.[ProductID], " _
& "q.[PackTypeID], q.[PriceTypeID], q.[ProductType], q.[Inactive Product] " _
& "FROM QryProduct AS q"

strOrder = "ORDER BY [Description], [ProductFamilyID], [ProductID];"

'This will select all if cboDescriptionFilter is empty
strFilter = "WHERE [Description] Like '*" & strText & "*'"

'Change Row Source
Me.cboDescriptionFilter.RowSource = strSQL & " " & strFilter & " " & strOrder
Me.cboDescriptionFilter.Dropdown

End Sub

http://lessthandot.com

 

[molly]

To Remou. This looks real good. You are so smart and practical. I will donate again. Because I am thrilled at your perserverance. PHV too. You solved an interesting combo box. No one on the internet ever solved it.
I can now lookup parts of any product description. I bet that I could someday apply this to another area.

So I think you accomplished it all. Now you can let your brain rest.


LikeComboBox5 is on savefile for you to keep.

Regards
Molly

 

[molly]

I should have posted to this earlier post which seemed closed but a new issue popped up. It does not work in my real file. It worked in the sample file though that i had attached here.

I am getting an error about Focus in my lookup combo box:

"You can't reference a property or method for a control unless the control has the focus."

Look 9 lines below where i remark, "'next row is yellowed in the debug". The vba is from an earlier tek-tips request which worked fine in a dummy model but boms out in my real model. I don't understand selStart.

Molly

 

[molly]

I apologize for messing up this post. Here are the combo box code lines that i forgot to attach.
About 10 rows down is the yellow vba error that says:

If Me.cboDescriptionFilter.SelStart > 0 Then
Molly

The complete code is:


Private Sub cboDescriptionFilter_AfterUpdate()
'Unique product ID column numbering from zero.
If Not IsNull(Me.cboDescriptionFilter.Column(2)) Then
Me.Recordset.FindFirst "FormulaID=" & Me.cboDescriptionFilter.Column(2)
End If
End Sub



Private Sub cboDescriptionFilter_Change()

If Me.cboDescriptionFilter.SelStart > 0 Then
strText = Mid(Me.cboDescriptionFilter.Text, 1, Me.cboDescriptionFilter.SelStart)
Else
strText = Me.cboDescriptionFilter.Text
End If

strSQL = "SELECT q.[Description], q.[ProductFamilyID], q.[ProductID], " _
& "q.[PackTypeID], q.[PriceTypeID], q.[ProductType], q.[Inactive Product] " _
& "FROM QryProduct AS q"

strOrder = "ORDER BY [Description], [ProductFamilyID], [ProductID];"

'This will select all if cboDescriptionFilter is empty
strFilter = "WHERE [Description] Like '*" & strText & "*'"

'Change Row Source
Me.cboDescriptionFilter.RowSource = strSQL & " " & strFilter & " " & strOrder
Me.cboDescriptionFilter.Dropdown

End Sub

 

[molly]

Remou - I made a shorter sample file. Your method works in the sample file but not my real file. Something to do with the FOCUS.

Molly