artful at rogers.com
artful at rogers.com
Fri Nov 3 03:06:52 CST 2006
My suggestion would be, Don't populate any of the combos until at least one keystroke has been entered in a given combo. Then populate it. I.e. the user enters "A", then you populate it with only those rows beginning with "A". The user then enters "B"; you repopulate it with those rows beginning with "AB". The user enters "C" and you repopulate the combo beginning with "ABC". This can be very quick. Assuming an Access BE, then the query that populates any given combo would be something like "SELECT ID, Description FROM SomeTable WHERE Description LIKE "ABC*". The way I have handled this in previous apps is to rewrite the source statement, which automatically causes a refill. ----- Original Message ---- From: "McGillivray, Don [IT]" <Donald.A.McGillivray at sprint.com> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com> Sent: Thursday, November 2, 2006 4:21:06 PM Subject: [AccessD] Strategies for populating combo boxes Hello, All I am working on a project where I've been asked to provide an interface that allows users to choose selection criteria from a series of combo boxes and use the results to open one of six reports. This is a fairly simple database - one table with about 20 columns and 1.1 million rows. They have already built the queries and reports; all they want me to do is provide an easy way to specify selection criteria for the reports. Since there is only a single table, building rowsources for the combo boxes requires running summary queries against the various columns for all 1.1 million rows. With 6 to 10 combo boxes on the form, that creates quite a bit of hang time waiting for the combos to populate. I'm considering strategies for reducing the delay, one being to populate a series of temp tables at DB open that can be used to populate the combos. That way the big delay happens only once upon DB start, and the UI should respond more quickly. I also thought of populating a single temp table with two columns - one to identify the combo box in which the values will be used, and the other with the values themselves. I seek guidance from the experts. Are either of these ideas worthy of pursuit? Is there a better way to get there from here? Any and all comments, critiques, ideas welcome. Thanks! Don McGillivray -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com