Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 9th, 2009, 05:39 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default Form ComboBox AddItem load is slow

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,
__________________
Neal

A Northern Soul

Last edited by Neal; September 9th, 2009 at 05:45 AM..
 
Old September 9th, 2009, 08:54 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default

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?
 
Old September 9th, 2009, 09:14 AM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default

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,
__________________
Neal

A Northern Soul
 
Old September 10th, 2009, 12:03 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow load times with VS local Web Server stevemilton ASP.NET 2.0 Basics 12 June 29th, 2008 11:07 AM
datagrid with too many textboxes very slow to load toshi C# 0 October 4th, 2007 12:35 PM
Initial Page is slow to load bjoneskc BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 July 7th, 2007 11:14 AM
Slow-loading form(s) dhl84 Access VBA 1 January 11th, 2007 01:16 PM
load data from combobox VB6 Perseus Beginning VB 6 0 July 30th, 2005 04:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.