Recordsource Query Runs with Each Change of Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a subform datasheet. When scrolling down in the datasheet,
each time I move to a new record, "Calculating..." appears in the status bar
and there is a delay of 2-3 seconds (10,000 records in the underlying
datasource). There are no OnCurrent events set and I can't find anything in
code that could be triggering the requery.

Access 2003 with SQL 2000 backend.

Any ideas about what is causing this.

Paco
 
Paco said:
I have a form with a subform datasheet. When scrolling down in the
datasheet, each time I move to a new record, "Calculating..." appears
in the status bar and there is a delay of 2-3 seconds (10,000 records
in the underlying datasource). There are no OnCurrent events set and
I can't find anything in code that could be triggering the requery.

Access 2003 with SQL 2000 backend.

Any ideas about what is causing this.

Paco
Click to expand...

On a large recordset like that Access will only load a few pages of records when
the form opens and then it will retrieve additional pages as you scroll down the
form. This allows the form to be painted quickly when first opened.

The same thing happens with a ListBox or ComboBox with lots of rows. Initially
just a small amount of rows are retrieved and then as you scroll down more rows
are retrieved.
 
It's because each time you move to another record in the main form MS
Access has to requery the dataset for the sub-form to determine which
records to display.

Try creating an index (not unique values) on the link field in the
sub-forms table - it might speed things up. This is also true if the
sub-forms dataset is a query - putting indexes on the joined fields
really speeds things up, especially if there are a lot of linked tables
in the query.

John
 
Thanks Rick. In this case, the "Calculate..." happens with every change. We
have even tested it with just 2 records and the same thing happens. I guess
it could be requerying because it doesn't know what data it has in the page.

The interesting things is that we put a parameter in the recordsource query
for the subform and the query only runs once when the form is loaded, not
when the cursor is moved to another record. So, there must be something else
(besides requerying the recordsource) that is causing the Calculate... to
appear in the status bar.

Any other ideas?

Paco
 
Thanks, John. In this case, the main form is just a container. It doesn't
have a recordsource. The Calculate... happens when the cursor is moved from
record to record in the datasheet view of the subform. We have indexes
(indices?) set for the appropriate columns; besides, we just discovered the
underlying subform recordsource query is not being requeried (see my response
to Rick).

Paco
 
Paco said:
Thanks Rick. In this case, the "Calculate..." happens with every
change. We have even tested it with just 2 records and the same thing
happens. I guess it could be requerying because it doesn't know what
data it has in the page.

The interesting things is that we put a parameter in the recordsource
query for the subform and the query only runs once when the form is
loaded, not when the cursor is moved to another record. So, there
must be something else (besides requerying the recordsource) that is
causing the Calculate... to appear in the status bar.

Any other ideas?
Click to expand...

Well, first off, "Calculating.." does not necessarily mean that the form is
being requeried. Do you have any controls based on expressions? Do you
have any ComboBoxes or ListBoxes?

I have seen forms that say "Calculating.." 100% of the time. I just don't
concern myself with it.
 
The main form is for presentation purposes. The subform is a locked datasheet
view listing the records. The main form has a couple of combo boxes that are
used to filter the records displayed in the subform datasheet view. The
recordsource for the subform is set when the main form is loaded. Otherwise
there are now other events except in the AfterUpdate of the two combo boxes.
 
Might want to check your Link Child Fields and Link Master Fields
settings in your subform in design view.
 
Might want to check your Link Child Fields and Link Master Fields
settings in your subform in design view.
 
Back
Top