Loading Combo Boxes Quickly

[PSUIVERSON]

I have written some applications in VB supported by Access. I load two combo boxes -

1) Account Name
2) Account Number

The list is a recordset of 34,000+ records. So what I currently have occurring on load is the following:

Do Until rst.EOF
If IsNull(rst!desc) = False Then
Combo1.AddItem rst!FUND & "-" & rst!AREA & "-" & rst!ORGN
Combo2.AddItem rst!desc
End If
rst.MoveNext
Loop

* My question is how can I do this faster? My app loads in like 15 seconds. Is this just unavoidable? Am I doing it bulky? Any other suggestions? Catching in a control array seems even worse. Any help would be great.

Thanks

 

[vbajock]

You might try loading the data in a table elsewhere in your app where the delay will not be so noticable to the user and then basing the combo on the table

 

[MrMajik]

This may help:

Dim cboItems as Variant '(this could be As String)
Do Until rst.EOF
If IsNull(rst!desc) = False Then
cboItems = rst!FUND & "-" & rst!AREA & "-" & rst!ORGN
Combo1.AddItem cboItems
Combo2.AddItem rst!desc
End If
rst.MoveNext
Loop

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[PSUIVERSON]

Worth a shot but same load time. Thanks Majik.

vbaJock - I'm not 100% sure how you mean. Currently I have an ACCESS Table that has 34,000 records. Here's the whole FORM LOAD procedure:

Private Sub Form_Load()

Dim adoConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Dim connectString As String

' Create a new connection
Set adoConnection = New ADODB.Connection

' Creat a new Recordset
Set rst = New ADODB.Recordset

' Build connection string
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\SOM Files\FINSYS WORK\SOMM_ConversionProcess.mdb"

adoConnection.Open connectString

' Get account Description
rst.Open "Zorg_pk", adoConnection

Do Until rst.EOF
If IsNull(rst!desc) = False Then
Combo1.AddItem rst!FUND & "-" & rst!AREA & "-" & rst!ORGN
Combo2.AddItem rst!desc
End If
rst.MoveNext
Loop

rst.Close

adoConnection.Close

Set rst = Nothing
Set adoConnection = Nothing

End Sub

 

[vbajock]

Instead of using additem for such a data set, why don't you just base your combo box on the table itself in the Rowsource property of the combo? You can use a SQL to keep out unwanted data.

 

[BlackKnight]

Hi,

You may consider not using a combobox to hold 34k items. Another solution may be is to allow the user to enter the account# or account name in part or in full. Have your app run a select query using the LIKE operator and then return the first 20 accounts in a subform like:

User enters: 1067
DB Table has: 1067,10675, 10676, 10677, 10679, 11000, 11001

In subform show: 1067,10675,10676,10677,10679...
and any pertinent account info.

User can then double click on the desired acct to select it.
Abd assign the acct valuse to textboxes if you are using the form for data entry.

Have a good one!
BK

 

[PSUIVERSON]

Both good tips. Let me work with it and get back to you. Thanks guys...still learning...

 

[PSUIVERSON]

Alright vb jock - I can't figure that out. Searched the web for a 1/2 hour and don't get it.

I tried to create a DataCombo control on my form and figure out the RECORDSOURCE/ROWSOURC properties but could not. How does this work in code?

This is the best tips I could find but could not execute:

http://support.microsoft.com/default.aspx?scid=kb;en-us;189682

 

[Bullsandbears123]

try using the table lookup wizard from the Combo box. It will write the sql for you.

don't try to edit the recordsource or recordset at ALL use the rowsource property instead.

example of code

dim sql as string

sql= SELECT field1, field2 FROM table1

cbobox.rowsource=sql

me.refresh

 

[PSUIVERSON]

Method or Data member not found

* Do I need to reference something?

 

[PSUIVERSON]

Got it! Thanks guys. I just created the ADODC object on the form - linked up two separate DataCombo boxes and now it loads in 3 seconds. A little herky jerky when I go to select but it works much faster.

Thanks guys. Only way to learn is to get pushed in the right direction...appreciate it.

[note: I referenced pg.112 if the WROX VB6 Database Programming book I have as well]

 

[scriverb]

BlackKnight, you took the words out of my mouth. I have used a similar process, having the user as you said enter either part or the whole value they are looking for and then make visible a combobox, updating their RowSource with a Select query that uses the Like operator to load and return only those records that meet the Like expression. What was entered into the origianl search textbox is assigned to the combobox and the cursor sent to the end of the string so the user can continue to move down the string by entering additional data. It is almost invisible to the user as he enters in the text box and then all of a sudden he is entering in a combobox with the dropdown initiated. Just a small subset of the large original recordset then has to be dealt with. It is important that the original table be indexed on these two fields so that the query process is very fast.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???