Search as you type afterupdate error

[powerblade]

Hey,

Have a combo box Search as you type (VBA) ... In after update ( setfocus ) I get message: You can only refer to a property or method for a control if the control has the focus.

Well I have searched long for a solution. It might have something to do with .text but 've tried different things , but do not get out.

Here is the working code (search as you type combo) :

Private Sub lala_Change()

Dim strText As String, strFind As String, i, strSQL As String

strText = Me.lala.Text

If Len(Trim(strText)) > 0 Then
strFind = "NaamProduct Like '"
    For i = 1 To Len(Trim(strText))

If (Right(strFind, 1) = "*") Then
    trFind = Left(strFind, Len(strFind) - 1)
        End If

    strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"

Me.lala.RowSource = strSQL

Else

strSQL = "SELECT Producten.ProductId, Producten.NaamProduct, [Prijsex]*1.21 AS Prijsincl, Producten.Prijsex, IIf([Klantid] Is Null,producten!korting,poafatih!korting) AS Kortingg " _
& " FROM Producten LEFT JOIN poafatih ON Producten.ProductId = poafatih.productid " _
& " WHERE (((poafatih.Klantid) Is Not Null Or (poafatih.Klantid) Is Null)) " _
& " ORDER BY Producten.ProductId, Producten.NaamProduct, Producten.Prijseenheid, poafatih.[prijs op afspraak];"
Me.lala.RowSourceType = "Table/Query"
Me.lala.RowSource = strSQL
Me.Recalc

End If

Me.lala.Dropdown

End Sub

And here is the afterupdate VBA:

Private Sub lala_AfterUpdate()

Me![Prijseenheid] = Me![lala].Column(3)
Me![Tekst17] = Me![lala].Column(3)
Me.Hoeveelheid.SetFocus
End Sub

Thank you in advance!

 

[powerblade]

No one who has an idea????

 

[pbaldy]

Saw the original post but wasn't sure of the answer. Certainly you can't use the .Text property of a control that does not have focus, but I only see you using that in the change event, which is fine. Does the change code work if you get rid of the after update code?

I don't see how the code could work, as I don't see you setting strSQL in the If block (it is set in the Else).

 

[powerblade]

hi thanks for replying at all

The code works perfect whitout the after update event. especially when we drop the setfocus part.

if i begin to write letters, the code begins to work and search begins and if i see the desired product name and i enter " TAB " the after update event works without error en setfocus to the next field is working too...

If i hit "ENTER" , the change and afterupdate events with setfocus works like a charm again.

BUT, if i see the desired product while typing, and i click with my mouse, then while jumping to setfocus field, i got the error.

If i delete the setfocus of the afterupdate event, and click the desired productname, the combo let me see only the selected product as dropdownlist and that is the only product i can see in the dropdown. When i click again, again i see only the same 1 product with dropdown list is active and so on.....

It seems i cannot select with mouse click, only enter or tab so the combo is maybe thinking, i am busy with searching maybe, but after click, it done, thats the product which selected and operation is done, code must stop and i must go to the next field.

very strange....

Maybe the code is not ended when i use mouseclick when i see the right productname that should be...

 

[pbaldy]

Have you tried without the SetFocus line? It should not be needed if that's the next control in the tab order.

 

[powerblade]

If i delete the setfocus of the afterupdate event, and click the desired productname, the combo let me see only the selected product as dropdownlist and that is the only product i can see in the dropdown. When i click again, again i see only the same 1 product with dropdown list is active and so on.....

Yes then the error disappears, but then after selecting or clicking with mouse, i cannoot select, after selecting, the cursor doesnt jump to the next field....

 

[pbaldy]

Are the selections in the combo so numerous you need the find-as-you-type? I typically just use the DropDown and let the autocomplete setting move the selection as the user types. That's even with a "streets" combo that has almost 10k records.

 

[powerblade]

no its not. you must select product names, and there are allmost a couple of 1000 products and sometimes the names are too long, and they want to write some words and search must start showing all products containing the typed value...

 

[powerblade]

attached you can see photo of the combo while typing and after clicking there is only 1 product and if you click with mouse, it stays second photo. only when enter TAB or ENTER then it selects the product, you cannot select with mouseclick.

 

[powerblade]

here is an sample database, maybe you understand better if you see live. ( by the way, i use access 2003 )

The combo which i have problems is the "Prod name".

thanks in advance..

 

[powerblade]

Ok,
finally i figure it out. now i have another problem:

Private Sub lala_Change()
On Error GoTo CleanFail

Dim strText As String, strFind As String, i, strSQL As String

strText = Me.lala.Text


If Len(Trim(strText)) > 0 Then
   strFind = "NaamProduct Like '"
    
    For i = 1 To Len(Trim(strText))
       If (Right(strFind, 1) = "*") Then
            
            strFind = Left(strFind, Len(strFind) - 1)
        End If
       strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    
    Next
    strFind = strFind & "'"
    
       
    strSQL = "SELECT ProductId, NaamProduct, [Prijsex]*1.21 AS Prijsincl, Prijsex, producten!korting AS Kortingg FROM Producten WHERE  " & _
    strFind & " ORDER BY NaamProduct;"
    
    Me.lala.RowSource = strSQL
 
    

    
Else
    
strSQL = "SELECT Producten.ProductId, Producten.NaamProduct, [Prijsex]*1.21 AS prijsincl, Producten.Prijsex, IIf([Klantid] Is Null,producten!korting,poafatih!korting) AS Kortingg, [prijs op afspraak]/1.21 AS [Verk Ex btw incl %] " _
& " FROM Producten LEFT JOIN poafatih ON Producten.ProductId = poafatih.productid " _
& " WHERE (((poafatih.Klantid) Is Not Null Or (poafatih.Klantid) Is Null)) " _
& " ORDER BY Producten.ProductId, Producten.NaamProduct, Producten.Prijseenheid, poafatih.[prijs op afspraak];"
Me.lala.RowSourceType = "Table/Query"
Me.lala.RowSource = strSQL
Me.Recalc



End If

Me.lala.Dropdown

End Sub

if i begin to type the first letter of nameproduct and second letter etc, it doesnt filter all products containing only typed...

What do i wrong...

thnx in adv..:banghead: