Slow Combo Box Problem

[derrickchanson]

I have a combo box in my "lead form" that pulls names from a separate table. The separate table contains roughly 125,000 names. Anyway, the combo box is sorted by alphabetically but will only list the names starting with A-M. Is there a way to generate the entire list faster when looking up a name in the combo box?

Thanks
Derrick

 

[Craig0201]

Derrick,

Combo boxes can only select (from memory) 65000ish records. So not all of the will select. Also it is the sheer number of records causing the slow response.

What about deleting the recordsource and putting a KeyPress event in.

If len(ComboBox.Text) = 3 then
ComboBox.RecordSource = "SELECT * FROM tblNames WHERE Name Like '" & ComboBox.text & "*"
End If

This will set the recordsource once three characters have been selected. Should allow you to access all the records and speed up response time.

Craig

 

[derrickchanson]

Thanks for your help. I understand what you saying, but for some reason it is not working. Here is the code I entered:

Private Sub ComboBox_KeyPress(KeyAscii As Integer)
If Len(ComboBox.Text) = 3 Then
ComboBox.RowSource = "SELECT * FROM tblBuildings WHERE Site Like '" & ComboBox.Text & "'"
End If
End Sub

Do you see anything wrong here?

Thanks
Derrick

 

[Maquis]

Do you have an index on the Name field in your table? This will speed it up tremendously. Maq B-)
<insert witty signature here>

 

[derrickchanson]

Thanks for the Index Tip. That did speed it up, however it still only pulls half of the list.

Thanks again!