ACCESS FEED: Re: [MS_AccessPros] Slow combo box find as you type

Senin, 30 April 2012

Re: [MS_AccessPros] Slow combo box find as you type

Yup! Took a looong time to figure out if that was needed. And looking back on it .... But as we are dealing with real estate and need to search/sort in a variety of ways and have future flexibility it seemed wise.

thanks!
Connie

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Connie
>
> Wow! You broke out addresses into lists of cities and streets?! That definitely explains a slow-down.
>
> Is there a real need to do that?
>
> Just for fun try putting the address in one table as the street address; city; State/Providence; postal code to see how fast the cbo runs. Index on the street address.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > John,
> >
> > There are currently 502 rows in the HomeInfo table. If I create an index based upon 5 fields, will the query use it automatically?
> >
> > Oh--here's the query SQL for qAddressCity:
> > SELECT HomeInfo.HomeInfoID, IIf(IsNull([HomeInfo].[Nbr]),"",[HomeInfo].[Nbr] & " ") & ([HomeInfo].[Direction1]+" ") & ([Street_List].[Street]+" ") & ([HomeInfo].[Direction2]+" ") & [City_List].[City] AS AddressCity, HomeInfo.TownshipID, HomeInfo.CityID, HomeInfo.ParcelNbr
> > FROM Street_List RIGHT JOIN (HomeInfo LEFT JOIN City_List ON HomeInfo.CityID = City_List.CityID) ON Street_List.StreetID = HomeInfo.StreetID
> > ORDER BY IIf(IsNull([HomeInfo].[Nbr]),"",[HomeInfo].[Nbr] & " ") & ([HomeInfo].[Direction1]+" ") & ([Street_List].[Street]+" ") & ([HomeInfo].[Direction2]+" ") & [City_List].[City];
> >
> >
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Connie-
> > >
> > > How many rows in the underlying table? Do you have an index defined on
> > > AddressCity? That would help.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > ---------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > Sent: Monday, April 30, 2012 8:18 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Slow combo box find as you type
> > >
> > >
> > > Hi all!
> > >
> > > I have a form Listings where the first thing to be entered is the address via a
> > > combobox whose row source is a query that draws data from the HomeInfo table. My
> > > problem is that entry is increasingly slower. I type in 2 or 3 characters and
> > > then have to wait before I can type any other characters. An example would be
> > > typing in 55738 210th St Alberta and after 55738 it stops. Access says it is
> > > calculating, then says it's doing a query. Have to wait several seconds before I
> > > can continue entering. I have tried entering the above several times and
> > > sometimes there's no problem and other times there is.
> > >
> > > ControlSource: HomeInfoID
> > >
> > > RowSource: SELECT qAddressCity.HomeInfoID, qAddressCity.AddressCity,
> > > qAddressCity.ParcelNbr FROM qAddressCity ORDER BY qAddressCity.AddressCity;
> > >
> > > On Not In List Event:
> > >
> > > 'If this property has not been entered yet, open the form for entering. (Not
> > > using properties feature for this because I can not click on the close button on
> > > the form.)
> > > Dim msg, style, title
> > > msg = "This property is not in the database. Would you like to add it?"
> > > style = vbYesNo
> > > title = "Add Property"
> > > Response = Msgbox(msg, style, title)
> > > If Response = vbYes Then
> > > DoCmd.OpenForm "HomeInfo", , , , acFormAdd
> > > End If
> > >
> > > I also have a BeforeUpdate and AfterUpdate event, but those are not being
> > > triggered since I'm just typing and not tabbing or pressing Enter.
> > >
> > > HomeInfo table fields:
> > > HomeInfoID
> > > Nbr
> > > Direction1
> > > StreetID
> > > Direction2
> > > CityID
> > > TownshipID
> > > CountyID
> > > Notes
> > >
> > > Thanks!
> > > Connie
> > >
> >
>