Form ComboBox AddItem load is slow

Neal · September 9th, 2009, 05:39 AM

Excel 2003

Hi,
I am using the following example code to load various combo-boxes on an excel form. The connection (co) is to an Access back-end database (Set co = New ADODB.Connection).
Each load may take a few seconds to perform. I have noticed that if I select a loaded/loading combo-box drop-down using the mouse, the speed of the load increases.
Is there any VBA code that I can add to assist the loading as I'd prefer that this is done 'behind the scenes' rather than
by USER intervention (I've tried applying SetFocus property on the combo-box but this doesn't appear to work). Any ideas?

Select combo-box to load and calling rotuine

Code:

genericFillDropDown "SELECT Id, ProductCode from qryProducts order by ProductCode", frmSetupBreederFeederInput.cmbCode01, False

Called loading routine

Code:

 
Public Sub genericFillDropDown(sqlString As String, ctrl As Control, allowBlank As Boolean)
'opens the SQl (which must be the bound column (index) and text
'will fail if wrong (GIGO)
Dim rs As New ADODB.Recordset
Dim fieldNo As Integer
Set rs = New ADODB.Recordset
rs.Open sqlString, co, adOpenForwardOnly, adCmdText
'not going to check that there are records as there MUST be
ctrl.Clear
If allowBlank Then
     ctrl.AddItem 0
    ctrl.Column(1, ctrl.ListCount - 1) = ""
End If
Do While Not (rs.EOF)
    ctrl.AddItem rs(0)
    For fieldNo = 1 To rs.Fields.count - 1
        'ctrl.Column(fieldNo, ctrl.ListCount - 1) = blankForNull(rs(fieldNo))
        ctrl.Column(fieldNo, ctrl.ListCount - 1) = rs(fieldNo)
        DoEvents
    Next
    rs.MoveNext
Loop
If ctrl.ListCount <> 0 Then ctrl.ListIndex = 0
rs.Close
Set rs = Nothing
Set ctrl = Nothing
End Sub

Thanks in advance,

sektor · September 9th, 2009, 08:54 AM

If your values to filled are subjects to be changed, then you have better create connection pooling. It means that you open connection only one time (say, when workbook is loading) and store it as a public variable, so that you won't have to destroy your connection.

If values are the same, then why loading them every time you click your drop-down?

Neal · September 9th, 2009, 09:14 AM

Hi,
I do load the basic combobox controls when I initiate the form.

I'm NOT loading the controls when I click. As per the original posting:

Quote:

'I have noticed that if I select a loaded/loading combo-box drop-down using the mouse, the speed of the load increases'

.
This is the case for ALL controls that are loading. If I click on any of the enabled comboboxes, the routine runs faster. I want to know why this is, and can I replicate this via VBA code?

Thanks,

sektor · September 10th, 2009, 12:03 AM

Listen! You posted a routine when you click a drop-down. You use Recordset.Open method - this method IMPLICITLY creates Connection object! Thus, every time you click drop down, you create a connection! That's why I told you to create a connection object on form's load and store it in public variable. The connection will be established ONCE and available to all routines. Your drop downs will be filled much faster. Change your program's logic.