Slow Combo box on Continuous Form

[aron.ridgway]

I have a Continuous form that is used to place orders in my Purchase order Database. I have a combo box with a Textbox over laid, so the users can type in the Textbox and it filters the combo box as they type. This is to search Nominal ledger codes that the current user has set up.

This has been working fine, but now finding issues as people have 400+ Nominal coeds set up. So there is a lag when the user is typing.

Is there a better way of doing this? i have a similar setup to search a supplier but this is not a continuous form and the majority of objects are un bound.

any help or pointers would be great.

My current code is :

Private Sub lstSelect_AfterUpdate()
Me.lstSelect.RowSource = "qryCurrentNominalNew"

End Sub

Private Sub lstSelect_BeforeUpdate(Cancel As Integer)
       
Me.lstSelect.RowSource = "qryCurrentNominalNew"
End Sub

Private Sub lstSelect_Change()


Dim strText As String
Dim strFind As String
Dim i As Integer
Dim strSQL As String

strText = Me.lstSelect.Text
  Debug.Print strText
  
   strSQL = "SELECT  tblNominal.PKnumber, [Name] & ""/"" & [NominalNumber] & ""/"" & [NominalCostCentre] AS Search, tblNominal.NominalNumber, tblNominal.Name, tblNominal.NominalCostCentre, tblUserNominal.UserFK, tblNominal.Company " & _
             "FROM tblUserNominal INNER JOIN tblNominal ON tblUserNominal.NominalFK = tblNominal.PKnumber " & _
             "WHERE [Name] & ""/"" & [NominalNumber] & ""/"" & [NominalCostCentre] Like '*" & strText & "*' AND tblUserNominal.UserFK = " & gUserID & " AND tblNominal.Company = '" & gCompany & "'" & _
             "ORDER BY tblNominal.Name ;"
    Debug.Print strSQL

    Me.lstSelect.RowSource = strSQL
    




Me.lstSelect.Dropdown

End Sub


Private Sub lstSelect_Click()
Me.lstSelect.Requery
End Sub

Private Sub lstSelect_GotFocus()
Me.lstSelect.RowSource = "qryCurrentNominalNew"
Me.lstSelect.Dropdown
End Sub

 

[CJ_London]

why do you overlay a textbox over the combobox?

why not just use the combobox?

And if you assign sql directly to the rowsource, rather than using a query, you can reference the other controls in the form directly rather than having to reset the rowsource on each change

e.g.

WHERE tblValue=[frmString]

 

[aron.ridgway]

I want to be able to search "Hotel" and all the items with that in the text anywhere show up. So as they type its filtering what is shown in the combo?

Can that be achieved with just a combo box then?

thanks

 

[spikepl]

If you assume that people want to type ANY bits from your 3 columns AND the "/" then you don't have any possibilities to improve the performance.

The major thing here would be to exploit the indexing, but that would require you to search the 3 fields separately and each from the beginng, i.e. no "*" on the left-hand side. This would correspond to people searching

([Name] Like strText & "*") OR ([NominalNumber] Like strText & "*") OR ...

"Name" as column name really sucks - you know that, hopefully.