Form Slow Requery (1 Viewer)

[tobypsl]

Hello

I have a form with 2 subforms set as Datasheet View and several text box controls.

The Text Box controls were displaying information from a Query based on DCount calculations. There are around 9 controls each with a DCount Datasource with the exception of the first control which Dcounts a field directly from a Table.

The ontimer of the form is set to requery both subforms and then the form itself (using me.requery) - thus refreshing the data.

When the form is refreshed the subforms repopulate quickly but the other controls are very slow with the exception of the first which DCounts directly from a table.

SO .....

Is it the queries that are slowing the form down ? Originally I was using 1 query and populating the DCount expression with criteria. As this was slow I created seperate queries so the DCount expressions did not require their own criteria. If anything this has made things slower.

The wait is around 15 to 20 seconds for the form to refresh. This is not unworkable but makes a 30 second form update less appealing as the form will then be empty (recalculating) for more time than it shows data.

Are there any suggestions for speeding this up ?

5 seconds would be reasonable.

I have looked in the forum already, the following suggestion:

go to TOOLS/OPTIONS then go to the GENERAL tab and uncheck all of the NAME AUTOCORRECT. This will speed up your forms opening up.

did not yield a result when opening the form and anyway, the form does not close it simply requeries the underlying data sources.

 

[KeithG]

Is this database setup as an FE BE? Is the data local data or linked from another db?

 

[tobypsl]

The BE data all lies within a different DB which is Paradox. I have linked lots of tables from there and query them in Access to create reports etc.

The form is simply a means of presenting a 'live' snapshot of work in progress throughout the day.

I have many many reports generated using the same linked tables etc and they are no where near as slow.

 

[tobypsl]

moving the row source property

I read in another thread that moving the rowsource property may speed up the form. That is, moving rowsource properties to an EventProcedure.

I tried this by removing the form's me.requery in the event procedure but get the following error

Compile Error
Method or Data Memebr could not be found

I had simply put the following to refer to a text box called text18

Text18.RowSource = DCount("[fieldname to count]", "queryname")

It is the RowSource that is highlighted as the error,

I then tried

me.Text18.RowSource = DCount("[fieldname to count]", "queryname")

and get the same error.

Is there a way of transferring the RowSource property for a text control to the event procedure and might this speed up the querying / refreshing of the form ?

 

[tobypsl]

I have emptied the recordsource for each of the text boxes and used a statement like:

me.textbox.value=DCount("[fieldname]", "queryname")

This does not speed the calculation up but it does mean the text boxes are not empty whilst the DCount executes, thus the form can be refreshed more frequently that when I used me.requery method which blanks the text boxes during the calculation.

If anyone knows of a genuine method to speed the calculations up I would be interested to know of it.