Slow Combobox Results

[pfm721]

I have been updating a database that I did not design but have been tasked
with updating. We need to add a new year of prices to the database. I have
all of the new info in already and it works fine. However the way that I
changed the form has slowed it down significantly.

The form has several cascading combos and the last one is slowing the form
down.

cboFilterState
cboFilterYear
cboFilterLocality

the last combo is the one with the problem it is based off of the
information in
tblLocality
ID - Auto
State - text
Location - text
Year - Number

The data in the combo is based on a query which narrows down location by the
year and state selected in the first two combos. It is taking 15-20 seconds
for the box to populate. Is there something that I can do to speed this
process up?

 

[pfm721]

Just a note that there are only 295 records in the table that the query is
limiting.

 

[pfm721]

One more thing I forgot to mention is that it was much quicker before I had
it limit by year. When it was just limiting by state there was almost no
delay.

 

[Mike Painter]

Is the year indexed?
It should not make a diffrence in such a small set of records but who knows.

 

[BruceM]

How are you limiting by year? That is, how are you changing or setting the
cboFilterLocality Row Source based on year?

 

[pfm721]

I indexed the state field didn't make a difference. Thanks for the idea.

 

[pfm721]

I just modified the query that cboLocalityFilter was based on. Before the
query had

Locality
State - Forms!frmAPCSearch!cboFilterState
Year

Now it has
Locality
State - Forms!frmAPCSearch!cboFilterState
Year - Forms!frmAPCSearch!cboFilterYear

When it was just the state it ran fine with almost no noticeable delay. When
I added the Year it slowed considerably.

 

[BruceM]

This link describes how to use cascading combo boxes:
http://www.databasedev.co.uk/filter_combo_boxes.html

Here is another one:
http://www.mvps.org/access/forms/frm0028.htm

It seems you are using Year as a field name. "Year" is a reserved word, so
that could be a problem, but I don't know if it would affect performance.

What is the Row Source of cboFilterYear, and what is the bound column? Do
you get better results if you change the criteria for the Year field to a
number such as 2008 rather than a reference to cboFilterYear?

 

[pfm721]

I think I may have narrowed it down a little more.

Here is the Row Source for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08 WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER BY
tblAPCLocality08.Location;

However I know think that a piece of code in the on change event in
cboFilterState is causing it. When I blocked the code out everthing ran fine.
The code is

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

I need this code or something similar so that when someone changes the state
and year the locality list will change to match the new selections.

Is there a better way to code this that won't slow the form down so much?

 

[BruceM]

I wonder about your database structure if you have a table specifically for
2008 data. There should be one table, with the recordset filtered as
needed.

That being said, a few points:
1) The After Update event would be the better choice, as the Change event
runs every time the data is changed
2) You need to requery the next combo box, not the current one. If you need
to narrow down the cboLocalityFilter list, requery cboLocalityFilter:
Me.cboLocalityFilter.Requery
3) I see no need to Refresh

The latter part of the second link I sent in the preceding post described
how to use a stored query to limit the records in a combo box. In it you
will see the use of Requery, and a description of what needs to be requeried
(the second combo box, not the current one, and the Requery occurs in the
first combo box After Update event.

I think I may have narrowed it down a little more.

Here is the Row Source for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08
WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER
BY
tblAPCLocality08.Location;

However I know think that a piece of code in the on change event in
cboFilterState is causing it. When I blocked the code out everthing ran
fine.
The code is

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

I need this code or something similar so that when someone changes the
state
and year the locality list will change to match the new selections.

Is there a better way to code this that won't slow the form down so much?

This link describes how to use cascading combo boxes:
http://www.databasedev.co.uk/filter_combo_boxes.html

Here is another one:
http://www.mvps.org/access/forms/frm0028.htm

It seems you are using Year as a field name. "Year" is a reserved word,
so
that could be a problem, but I don't know if it would affect performance.

What is the Row Source of cboFilterYear, and what is the bound column?
Do
you get better results if you change the criteria for the Year field to a
number such as 2008 rather than a reference to cboFilterYear?

 

[pfm721]

I think I may have narrowed down the problem a little bit.

Here is the RowSource for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08 WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER BY
tblAPCLocality08.Location;

However I think this code which is located in the on change event for
cboFilterState is the problem

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

When I blocked that code out everything ran much faster. However I do need
that code to change the contents of the cboFilterLocality when the state is
changed. Is there a better way to code this step that would speed things up?

Thanks
Patrick

 

[pfm721]

I think I've narrowed it down a bit.

Here is the Rowsource for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08 WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER BY
tblAPCLocality08.Location;

I think that a piece of code in the on change event in cboFilterState is
causing the slow down.

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

When I block it out everything runs fine. I need the function that this code
provides however because if the user changes states I need the list in
cboFilterLocality to change with it. Is there a better way to code this
function?

Thanks
Patrick

 

[pfm721]

I think I've narrowed it down a bit.

Here is the Rowsource for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08 WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER BY
tblAPCLocality08.Location;

I think that a piece of code in the on change event in cboFilterState is
causing the slow down.

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

When I block it out everything runs fine. I need the function that this code
provides however because if the user changes states I need the list in
cboFilterLocality to change with it. Is there a better way to code this
function?

Thanks
Patrick

 

[pfm721]

That worked thanks for the help.

How are you limiting by year? That is, how are you changing or setting the
cboFilterLocality Row Source based on year?

 

[BruceM]

Why do you keep reposting this message?

I think I've narrowed it down a bit.

Here is the Rowsource for cboFilterLocality

SELECT tblAPCLocality08.ID, tblAPCLocality08.Location,
tblAPCLocality08.State, tblAPCLocality08.YearCal FROM tblAPCLocality08
WHERE
(((tblAPCLocality08.State)=[Forms]![frmAPCSearch]![cboFilterState]) AND
((tblAPCLocality08.YearCal)=[Forms]![frmAPCSearch]![cboFilterYear])) ORDER
BY
tblAPCLocality08.Location;

I think that a piece of code in the on change event in cboFilterState is
causing the slow down.

Private Sub cboFilterState_Change()
'Me.Refresh
'DoCmd.Requery
End Sub

When I block it out everything runs fine. I need the function that this
code
provides however because if the user changes states I need the list in
cboFilterLocality to change with it. Is there a better way to code this
function?

Thanks
Patrick

This link describes how to use cascading combo boxes:
http://www.databasedev.co.uk/filter_combo_boxes.html

Here is another one:
http://www.mvps.org/access/forms/frm0028.htm

It seems you are using Year as a field name. "Year" is a reserved word,
so
that could be a problem, but I don't know if it would affect performance.

What is the Row Source of cboFilterYear, and what is the bound column?
Do
you get better results if you change the criteria for the Year field to a
number such as 2008 rather than a reference to cboFilterYear?