Speeding up Combobox Loads

[bitwise]

I am having a 5-30 second delay when my forms open. I am loading about 4-5 combo boxes on the forms. I use stored procedures on SQL2000. The data being retrieved is very small. Each box only has 2 - 100 records each. In fact only 1 of them has around 100 the other 4 are more like 3-10 records. (only 3 fields per record). The actual retrieval of data is quick...it's getting paused loading the combobox.

I have used both methods I found here.
Do while not EOF and MoveLast get record count then a For statement. Both are equal in speed for me. What else can I look for???
Here is the code I'm using to load the box. It is public because other forms need to load the same data. I used a regular SELECT statement here to show what I'm pulling...it is a stored procedure on my system

Public Sub g_LoadCompany(ByRef r_cboCompany As ComboBox)
' PURPOSE: To load a combo box with the Company information
' INPUTS : r_cboCompany = the combo box to load
' ASSUMES: None
' RETURNS: None
' EFFECTS: r_cboCompany

On Error GoTo ErrorHandle

' Local Constants
Const c_strProcSig As String = mc_strModuleName & "g_LoadCompany"

' Local Variables
Dim cnHR As ADODB.Connection
Dim rstCompany As ADODB.Recordset
Dim strCode As String
Dim strName As String
Dim intIndex As Integer
Dim intRecordCount As Integer
Dim intLoop As Integer
Dim strCompany As String

Set cnHR = modGlobals.g_cnConnection

strCompany = "SELECT Co_intID, " & _
"Co_strName " & _
"FROM tblCompany"

Set rstCompany = New ADODB.Recordset
rstCompany.Open strCompany, cnHR, adOpenStatic, adLockReadOnly, adCmdText

'Here is where the pause occurs, runs very fast up to this point
rstCompany.MoveLast
intRecordCount = rstCompany.RecordCount
rstCompany.MoveFirst

For intLoop = 0 To intRecordCount - 1
intIndex = rstCompany.Fields("Co_intID").Value
strCode = rstCompany.Fields("Co_strName").Value
r_cboCompany.AddItem strCode
r_cboCompany.ItemData(r_cboCompany.NewIndex) = intIndex
rstCompany.MoveNext
Next intLoop

rstCompany.Close
Set rstCompany = Nothing

cnHR.Close
Set cnHR = Nothing

Exit Sub

ErrorHandle:

g_objError.RaiseError Err, c_strProcSig

End Sub

[dcarlson]

I can't see anything that should be slowing it down. I do the same sort of thing all of the time with many combos and list boxes without any sort of slowdown.

In my opinion, (if you want it ) when the recordset only has a small amount of data you should probably use a do while not eof instead of the for loop because it'll take more time to move to the last record then it does to check for eof. I use the for next method when I have a large recordset to loop through. Generally you won't see much of a difference between to two.

This probably won't help you much though.

[Babbalouie]

One thing that COULD be slowing it down...if the Sort property of your combo boxes is set to True. If so, add an Order By clause to your SQL statement and set the Sort property of the combo boxes to False

[bitwise]

Sorry, should have stated this in original post. I am using the MSForms 2.0 Combobox...not the standard one...don't know if that makes a big difference. I just needed the columns...since I'm displaying multiple columns this combo box is just easier to work with. There is no "sort" Property in the MSForms.Combobox.

[Borry]

Hello bitwise,


here's an example which will speed up your cbo box load very much !

Please, let me know the result !

ps : this is for ado and ms forms 2.0 cbo box

hth,

Bjorn

Private Sub FillArticle()

Dim strArticleRs As String
Dim ArticleRs As New ADODB.Recordset

Call Connection

'--------------------
'Get all the Articles
'--------------------
strArticleRs = "SELECT Article_ID,ArticleName " & _
"FROM tblArticle " & _
"WHERE Supplier_ID =" & CboSupplier & " " & _
"ORDER BY ArticleName;"

With ArticleRs
.Open strArticleRs, Conn, adOpenForwardOnly, adLockOptimistic

If Not .EOF Then
With CboArticle
.Clear
.ColumnCount = 2
.ColumnWidths = "0 cm;2 cm" 'put first column to 0 to hide your primary key
.Column = ArticleRs.GetRows 'Give array to combobox.list to automatically fill it
End With
Else
lstArticle.Clear
End If

.Close
Set ArticleRs = Nothing
End With

Call ConnClose

End Sub