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

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
>