Populating a Combo Box only after 3 characters or so are typed in 3

[dlsd619]

I have a combo box on a form that gathers data from a query, however, there are several hundred thousand or so records in there. I would really like to speed this process up by somehow doing the following.

Maybe after a few characters are entered in the combo box, then it can populate with a fraction of the records it would normally have. Does anybody have any ideas that can get me started? Thank you

 

[RuralGuy]

MVP Allen Browne has a page for this issue:
Combos with Tens of Thousands of Records

http://allenbrowne.com/ser-32.html

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[maxhugen]

Set the combo's rowsource to blank. In the combobox's OnChange event, you could check the length of the text that has been entered using Len(). Once it equals 3, you can set the combo's blank rowsource to your SQL, with a where condition that looks for entries:

"SELECT myField FROM myComboQuery WHERE myField >= '" & [myCombo] & "'"

Max Hugen
Australia

 

[TheAceMan1]

How are ya dlsd619 . . .

[blue]RuralGuy[/blue] has provided an excellent reference! and I was going to post the same as [blue]maxhugen [/blue] with just one hitch . . . the following would be a much better filter:

[blue]"SELECT myField FROM myComboQuery WHERE myField Like '" & Me![myCombo].Text & "*'"[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[dlsd619]

Okay, I have done the following. I basically copied Allen Browns code and replaced it with different names. I need help with the "row sources".

Here's the code:

Option Compare Database
Option Explicit

Dim OrderNumberStub As String
Const conOrderNumberMin = 3

Function ReloadOrderNumber(sOrderNumber As String)
Dim sNewOrderNumber As String
sNewOrderNumber = Nz(Left(sOrderNumber, conOrderNumberMin), "")

If sNewOrderNumber <> OrderNumberStub Then
If Len(sNewOrderNumber) < conOrderNumberMin Then
Me.cbrderNumber.RowSource =
OrderNumberStub = ""
Else
Me.cbrderNumber.RowSource =
End If
End If
End Function



Private Sub cbrderNumber_Change()

Dim cbo As ComboBox
Dim sText As String

Set cbo = Me.cbrderNumber
sText = cbo.Text
Case " "
cbo = Null
Case Else
Call ReloadOrderNumber(sText)
End Select
Set cbo = Nothing

End Sub


Private Sub Form_Current()
Call ReloadOrderNumber(Nz(Me.cbrderNumber, ""))
End Sub


Here is the row source query of my combo box now. How can I incorporate it into this code?

SELECT tbl_Entities.EntityID, [OrderNumber] & "-" & [EntityNumber] AS OrderEntityNumber FROM tbl_Entities GROUP BY tbl_Entities.EntityID, [OrderNumber] & "-" & [EntityNumber] HAVING ((([OrderNumber] & "-" & [EntityNumber]) Is Not Null)) ORDER BY [OrderNumber] & "-" & [EntityNumber];