Solved

populate a combobox faster

Posted on 2001-06-05
16
443 Views
Last Modified: 2008-02-01
Please see this code and tell me if is it the best way to populate a combobox with a table that have about 2000 records.
Becouse it is slow method.
Thanks

Public Sub Enche_Combo_Empresas()
Dim aSQL As String
On Error Resume Next

    CmbRemDest.AddItem "A"
    CmbRemDest.Clear
If Err.Number > 0 Then Exit Sub
    aSQL = "SELECT empresa.nome_emp FROM empresa order by empresa.nome_emp"
            Set RSetEmp01 = New ADODB.Recordset
            Set RSetEmp01.ActiveConnection = CnnSoc
            RSetEmp01.Open aSQL, CnnSoc, adOpenForwardOnly, adLockBatchOptimistic
With RSetEmp01
    Do While Not .EOF
        CmbRemDest.AddItem !nome_emp
        .MoveNext
        CmbRemDest.Text = CmbRemDest.List(0)
    Loop
    .Close
End With
Set RSetEmp01 = Nothing

End Sub
0
Comment
Question by:asimoes
  • 4
  • 2
  • 2
  • +8
16 Comments
 
LVL 7

Expert Comment

by:John844
Comment Utility
first thing to try is change adlockbatchoptimistic to adLockReadOnly  then I would try using an array to hold contents of recordset.

dim aryNome()
dim i as integer
aryNome = rsetemp01.getrows()
for i = 0 to ubound(aryNome,1)
       CmbRemDest.AddItem aryNome(0,i)
       CmbRemDest.Text = CmbRemDest.List(0)
next

I have not executed code, so there might be syntax errors to debug.
0
 
LVL 1

Expert Comment

by:morgan_peat
Comment Utility
Yes, it is a slow method.  Is there any reason why you are setting the .Text property each time?

Try:

cboCombo.Clear

<get RS>

Do While Not oRS.EOF
    cboCombo.AddItem oRS("<field")
    oRS.MoveNext
Loop

<close RS>
cboCombo.Text = cboCombo.List(0)

0
 
LVL 7

Expert Comment

by:Joe_Griffith
Comment Utility
I would also suggest that you set the combo box's visible property to false while you are filling it and then back to true when it is full.  This can have a dramatic effect on apparent speed.  If you are sorting I would also set this to false until it is full so that it doesn't have to resort after every additem.

I don't think putting 2000 items in a combo box is a good idea to start with.  Is the user supposed to scroll through them all to find the one he/she/it wants?  Maybe you should rethink the whole thing and include some sort of secondary grouping rather than one monolithic list.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Example:

How to speed up database access

Here is a trick to loop through a recordset faster.  Often when looping through a recordset people will use the following code:

Do while not records.eof
    combo1.additem records![Full Name]
    records.movenext
loop      

The problem is that everytime the database moves to the next record it must make a check to see if it has reached the end of the file. This slows the looping down a great deal. When moving or searching throuch a large record set this can make a major difference.  Here is a better way to do it.

records.movelast
intRecCount=records.RecordCount
records.movefirst

for intCounter=1 to intRecCount
    combo1.additem records![Full Name]
    records.movenext
next intCounter

You should see about a 33% speed increase.
0
 
LVL 3

Expert Comment

by:wpsjr1
Comment Utility
Adding records with API is faster than VB methods:

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Const CB_ADDSTRING = &H143

Public Sub Enche_Combo_Empresas()
Dim aSQL As String
Dim lHwnd As Long
On Error Resume Next

   'cmbremdest.AddItem "A"
   'cmbremdest.Clear
If Err.Number > 0 Then Exit Sub
   aSQL = "SELECT empresa.nome_emp FROM empresa order by empresa.nome_emp"
           Set RSetEmp01 = New ADODB.Recordset
           Set RSetEmp01.ActiveConnection = CnnSoc
           RSetEmp01.Open aSQL, CnnSoc, adOpenForwardOnly, adLockBatchOptimistic
With RSetEmp01
   lHwnd = cmbremdest.hwnd
   Do While Not .EOF
       'CmbRemDest.AddItem !nome_emp
       SendMessage lHwnd, CB_ADDSTRING, 0, ByVal !nome_emp
       .MoveNext
   Loop
   .Close
End With
With cmbremdest
  If .ListCount > 0 Then .Text = .List(0)
End With
Set RSetEmp01 = Nothing
End Sub


Enjoy!  :)

Paul
0
 
LVL 2

Accepted Solution

by:
niklausj earned 250 total points
Comment Utility
first
either set visible to false or lock windowupdate
second one avoids flicker
scond

use API-Sendmessage to populate ( u can see sample in prev message so I don't repeat it here )
third
use fieldpointer to speed up access to recordset
or use getrows to array the loop through array
fourth
remove window updatelock or set visble again

all together:
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg
As Long, ByVal wParam As Long, lParam As Any) As Long
Const CB_ADDSTRING = &H143
Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

Sub FillCBO(cbo as ComboBox,oRS as ADODB.Recordset, FieldName as string)
    Dim lHwnd As Long
    Dim objFldText As ADODB.Field

    cbo.Clear
    Set objFldText = oRs.Fields(FieldName)
    lHwnd = cbo.hwnd
    Call LockWindowUpdate(lHwnd)
    Do While Not oRs.EOF
        SendMessage lHwnd, CB_ADDSTRING, 0, ByVal CStr(objFldText.Value)
        oRs.MoveNext
    Loop
    Call LockWindowUpdate(0&)
End Sub


tokk about 3 seconds to fill a combo with 20000 yes 20k entries on a p3-750mhz machine
with getrows to local array it took about 5 seconds
0
 
LVL 3

Expert Comment

by:wpsjr1
Comment Utility
Fields pointer sounds like a good idea.  But I'm partial to SendMessage with WM_SETREDRAW for preventing the window from updating.  LockWindowUpdate tends to flicker the entire desktop when you release it.
0
 
LVL 1

Expert Comment

by:viperlin
Comment Utility
holding 2000 records in memory is not a good idea.

do not load a combo box with that much data, your database server will thank you for the redesign.

do not store data in combo boxes beyond 20 - 30 records.  no one wants to scroll through all the choices and find what they want.

and do not use a combo box instead of an array!  
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:bhh
Comment Utility
Listening...
0
 
LVL 3

Expert Comment

by:wpsjr1
Comment Utility
With that many items in the combo I'd use SendMessage with CB_FINDSTRING triggered by the change event of the text portion which sets the listindex.  Then its easy to move through.
0
 
LVL 15

Expert Comment

by:ameba
Comment Utility
Last 10 Grades Given: A C B A A C B C B A        :-(

>order by empresa.nome_emp
I bet there is no index on nome_emp.

btw, hi, wpsjr1  :)
0
 
LVL 3

Expert Comment

by:wpsjr1
Comment Utility
hey ameba :)
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
ping..
0
 

Author Comment

by:asimoes
Comment Utility
Thanks for the code, it works fine.
Anyway can you explain what does it mean the
-Const CB_ADDSTRING = &H143-
 and
-SendMessage lHwnd, CB_ADDSTRING, 0, ByVal CStr(objFldText.Value)-

1?
The purpose of the combobox is to find (as I write) the records of a table. But I must press F4 to open the combobox and make the search. Is it possible to don't need to press F4 and serches as I write in the combobox and the record apears?
This happens in the access environment.

2?
Imagine that instead of having a combobox I have a flexgrid with a textbox bellow it where I can (as I write) find in the flexgrid the record I'm writing in the textbox.

3?
Does API from windows have advantage of visual Basic?

thank you so much

0
 
LVL 2

Expert Comment

by:niklausj
Comment Utility
0?
The SendMessage is a universal function, it can send messages for may purposes, they are distigueshed by the messageID wich is a unique long value, all windows defined standard messages have a prefix like CB_ for ComboBox-Messages since VB doesn't have the WindowsMessgaes defined you have to use the numeric value in this case Hex 143
define here as const.
1?
You could bind the Recodset to a DBList and filter the recorset with the value entered in to textbox.
2?
Could also work.
3?
Yes or No, Since VB does a lot of check's and other internal stuff before actually calling the API,
( sendmessage for text to listbox need LB_ADDSTRING constant, and so on )
in VB you can say ListBox.AddItem or ComboBox.AddItem,
thus VB first Checks The Type of control the uses correct API.
This checks are performed at each assignement which slows down programm. You'll see the difference ussually only with large loopcounts.
Somtimes API could even be slower.
Somtimes the only way to do things ( API could drop the Combo for you.)
0
 

Author Comment

by:asimoes
Comment Utility
Thanks for everything
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now