Senin, 30 April 2012

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

 

John and Bill,

I'm wondering if I should commit the ultimate No no...Would it work to create a field in the HomeInfo table (or a separate table) that was the combined street or streetCity and index that? And anytime the data is updated force an update of that field? As you would guess that info is used in MANY places.

Hoping you are not going to excommunicate me :-)
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
> >
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

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
> > >
> >
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] BeforeUpdate in subform cancels update when Close button on main form is clicked

 

Good afternoon or evening :-),

If the user is in the middle of entering data on a subform and decides to close the form using the Close button on the main form, the BeforeUpdate event of the subform gives the message that the something needs to be changed and cancels the update, but the DoCmd.Close continues so the form is closed with no opportunity to change the data.

Don't think you need it but here's the code behind the BeforeUpdate event.

'Prevent double entries for Me.Ordr = 1
If Me.Ordr = 1 Then
' Point to this database
Set db = CurrentDb
'Open ListingContacts to find priorities for this listing that are the same as Me.Priority
Set rstP = db.OpenRecordset("SELECT * FROM SalesBuyers " & _
"WHERE SaleID = " & Me.SaleID & " And Ordr = " & Me.Ordr)
If rstP.RecordCount > 0 Then
Msgbox "Another contact has this order." _
& vbNewLine & "Change the other contact's order and then enter this contact"
Me.Undo
End If
End If
'Clean Up
rstP.Close
Set rstP = Nothing
Set db = Nothing

Thanks!
Connie

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

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

 

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
> >
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

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

 

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
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] table design

 

Elizabeth-

Better to properly normalize the design. Putting a string of date fields in the
Client table would violate first normal form (repeating groups).

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 glcass58
Sent: Monday, April 30, 2012 9:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] table design

 
Yes, my question is on the attributes. Is it better to have a narrow table of
client_id and client name with one to one relationships by client_id to many
subject-matter attribute tables? Or is it better to have one wide attribute
table? thanks

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Elizabeth-
>
> So the related table is Client? That should give you a 1-M relationship to
this
> table as long as you include ClientID. Each client will have multiple events
> consisting of an EventID and the date the event occurred.
>
> 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 glcass58
> Sent: Monday, April 30, 2012 8:45 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] table design
>
>  
> The dates are when forms are sent to clients and received back from clients. I
> was going to have them in one table
> ID
> Event_ID (key to the event description, i.e. Form "A" Sent Date)
> Date
>
> The attributes are about the client. Do you need more info? Thanks for your
> help.
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Elizabeth-
> >
> > Need more info. What are these "dates" related to? Why aren't they in their
> > related table(s)? One-one joins are a pain the patootie.
> >
> > 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 glcass58
> > Sent: Monday, April 30, 2012 5:36 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] table design
> >
> >  
> > Hi there-
> >
> > I have many, many attributes and a factless fact table (dates of events). My
> > first thought is that I'd like to put the attributes in separate tables by
> > subject matter and have one to one joins. But then I thought I better ask
you
> > all if you thought Access would run faster if the attributes were in one
wide
> > table.
> >
> > Thanks,
> > Elizabeth
> >
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:

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
>

__._,_.___
.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)