Connie-
Because you're using an expression to generate the display field in the combo
box, an index won't help at all. You could make the query slightly more
efficient by putting an index on Nbr, Direction, Street, Direction2, and City
and then do an ORDER BY on those fields rather than the expression.
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 9:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
Tidak ada komentar:
Posting Komentar