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
> >
>
Tidak ada komentar:
Posting Komentar