Dan Waters
dwaters at usinternet.com
Thu May 15 10:13:07 CDT 2008
John, Somewhere I learned this way to help comboboxes with long lists. In the form's load event, enter this line lngCount = cbo.ListCount You don't care what the actual count is. But the behind the scenes activity of counting the list makes the list much more 'usable'. I suppose that there is some amount of time to count the list as above, but you'll have to try this out to trade off form opening time vs. combobox behavior to see what works well. I haven't used very long lists like you're mentioning, but I don't notice any real delay with a 200 line list, as an example. Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, May 15, 2008 9:15 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] using ADO to populate combos - was RE: Error3048Cannotopen any more databases Drew, I finally got around to tweaking a callback widget I created long ago while trying to get a generic callback that could be used by any combo. The method uses a class which holds the actual callback method as well as a collection to hold arrays loaded from queries. Basically I set up a query names qSomeCboName, qSomeOtherCboName etc. This allows me to use the combo name from inside of the callback function to get at the data required for that combo. Thus my rowsource can be cboCallBack() for any combo which has a stored query named in the proper manner. It works. It is not speedy. In fact it is slow. For small lists like states it is quite acceptable, a very slight hesitation as you pull down the list or type in the name of the list item and have the combo "follow" your typing. On large lists, for example a city table with 10555 cities, it locks up the form for minutes as it opens. It also locks up the form for a long period as you type in something like Racoon (an actual city name in the table!). In fact performance gets marginally better the second time you do such a "search" through the combo, apparently due to caching in memory? But in general it is unusable for "large" lists. I am going to have to do some research for what "large" is. BTW, the same table loaded directly into a combo using the query itself runs at a quite acceptable speed, barely noticible hesitation as the combo "finds" racoon. I assume the difference is interpreted VBA callback vs optimized C++ or even assembler callback "behind" the combo using the recordset. This still leaves plenty of opportunity to use the callback though since I have dozens of combos which use small lists. Getting these converted to use a callback MIGHT make a significant difference in some of my complex form load times. We shall see. I just thought you might be interested to know that I at least got it working. John W. Colby www.ColbyConsulting.com Drew Wutka wrote: > That's not necessary, to write a function for each control. One of the > arguments of that control is the control itself. > > So you could create one function, in a module, that would be your > 'global' callback function. What I would recommend would be to create a > class that would handle recordset objects, and in your callback function > pull the appropriate recordset from that class based on the control > argument that is passed to the callback function. > > Drew > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Wednesday, March 05, 2008 9:17 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] using ADO to populate combos - was RE: > Error3048Cannotopen any more databases > > Long ago I tried using call back functions and the only way I could > figure > out to do it was write a callback for each object. That just doesn't > work > when you want to implement this easily and automatically system wide - > five > hundred different combos. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka > Sent: Wednesday, March 05, 2008 10:06 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] using ADO to populate combos - was RE: Error > 3048Cannotopen any more databases > > Use a callback function. > > Drew > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Wednesday, March 05, 2008 8:23 AM > To: 'Access Developers discussion and problem solving' > Subject: [AccessD] using ADO to populate combos - was RE: Error 3048 > Cannotopen any more databases > > I was under the perhaps naive belief that with Access 2000 and greater, > a > combo could be populated by an ADO recordset, i.e. it's rowsource > property > could be an ado recordset. When I go out and look, I am running into > methods that build up a string and the row source type is set to value > list. > That is a rather useless solution in the majority of cases since the max > limit of characters in the list is around 2000. > > Is it in fact possible to make the rowsource an ADO recordset or am I > delusional? If possible, how is it done? Example code would be nice. > > John W. Colby > Colby Consulting > www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com