?
Solved

ComboBox Value Lists Slow to Load

Posted on 2004-04-16
12
Medium Priority
?
887 Views
Last Modified: 2012-06-27

I'm using Access 2002 with ADO and am using unbound forms and unbound comboboxes based on static lookup tables.  I'm designing it for frontend/backend use to later migrate to an .adp with perhaps SQL Server.  On the Form_Load event, I populate the combo boxes based on static lookup lists by setting their RowSource properties to a string with the semicolon delimited value list.  This works fine, except when the number of rows becomes somewhat large, and then it takes one to two seconds for that particular combobox to load.  It's also slow to scroll.  Rather annoying.  

As an example, I have a Countries lookup table with three fields (RecordID, CountryName, and TelCode) with 238 rows.  The code fundamentally looks something like this snippet (though the string is built differently from the simple example below):

strRows = "1;Afghanistan;930;2;Albania;355;"      ' string goes on for all countries
cboCntry.ColumnCount = 3
cboCntry.ColumnWidths = "0;2000;0"
cboCntry.BoundColumn = 0
cboCntry.LimitToList = True
cboCntry.AutoExpand = False
cboCntry.AutoExpand = False
cboCntry.RowSourceType = "Value List"
cboCntry.RowSource = strRows

1)  Is the slowness in loading and scrolling long value lists in fact typical behavior for a combobox using a RowSourceType = "Value List"?

2)  If yes, then a thought I've had is to set the RowSourceType = "Table/Query" and set the RowSources  to respective local tables or queries in a separate front-end mdb holding nothing but static lookup tables.   Does that make sense?  If so, how would I create a separate ADO connection to it for the comboboxes' RowSource property?

Thanks,
Wyatt
0
Comment
Question by:WyattRidesAgain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 1

Expert Comment

by:Lou Dufresne
ID: 10845776
Hi Wyatt,
    This is a wild guess. Add an index(s) to the table or combo box list. This may make it run faster.

Lou
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10845862
go with your second thought, store the data in a local table.

You can use the currentproject.connection as the connection object for the recordset you are using for the combobox
0
 

Author Comment

by:WyattRidesAgain
ID: 10845934
Hi Stevbe,

Can I use a recordset as the combo box rowsource?  I don't mean by looping through its records to either build the rowsource string or to use .AddItems one record at at time, which I understand.  Rather, I mean can you directly set the rowsource equal to the recordset, like you would with a table or query source?  I tried doing that (setting the recordset) and I got a 'type mismatch' error.

Wyatt
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:stevbe
ID: 10845989
I don't have my reference material here with me but ... yes, you can set the source of a combobox to be a recordset. I think that you can only do this with an ADO recordset, what object library are you using to build your recordset with?

Steve
0
 
LVL 39

Accepted Solution

by:
stevbe earned 1000 total points
ID: 10846032
yup ... just set it up and tested fine.

Steve

Option Compare Database
Option Explicit


Dim mrst As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
    Set mrst = New ADODB.Recordset
    mrst.Open Source:="SELECT * FROM tblContact", _
              ActiveConnection:=CurrentProject.Connection, _
              CursorType:=adOpenKeyset, _
              LockType:=adLockOptimistic
    Set Me.cboADO.Recordset = mrst
End Sub
0
 

Author Comment

by:WyattRidesAgain
ID: 10846035

That's encouraging.  I'm using ADO 2.7 library.  Can you send a quick n dirty code snippet to illustrate?  I'm guessing I have the wrong combinations of combo box properties that rejects the recordset.

Wyatt
0
 

Author Comment

by:WyattRidesAgain
ID: 10846044

Ooops.  I replied before I saw the code.  Sorry.  Lemme give it a try.

Wyatt
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10846096
not that it will make much of a difference but you can set the locktype to readonly which will speed things up a hair.

  LockType:=adLockReadOnly


the real key is the cursortype being set to keyset which is also what you need to do when binding a form's recordset to an in memory recordset.

Steve
0
 

Author Comment

by:WyattRidesAgain
ID: 10846173

Steve,

It works great!!!!  Fast and clean.  Perfect.

I didn't know to look for the recordset property for the combo box object.  Learned something good today.

Many thanks!  Points are coming.
Wyatt
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10846262
You are welcome, glad I could help.

Steve
0
 

Expert Comment

by:JonoB
ID: 11201314
Well, this is exactly what I have been trying to do for a while, but I am still struggling.

Any idea of what ADO library you need for this to work?

I am using A2000, with the Microsoft ADO 2.6 library and I get a "Method or data member not found" error on the me.cboADO.Recordset line.

Any suggestions?

Thanks for the help.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 11205500
This is a closed question, you should open your own. The method we used to solve this question will not work in Access 2000.

Steve
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

801 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