Is there an event triggered after a text box calculation?

[Cavman]

Hi,

I have a DCount in an unbound text box. When the form is opened this takes about 30 seconds to update as there is a lot of data to query!

I'd like to display an 'updating data' message underneath the text box that disappears once the box has updated, but I can't find an event that is triggered at this point. Can anyone suggest a way of doing this?

Thanks,

Simon

 

[spikepl]

30 seconds for any operation is a looooooooooong time. Can you show the DCOUNT parameters?

 

[Cavman]

There are actually 4 DCounts. They pull back totals of various account types from an accountbase of about 18000 records. The database is shared and updated by about 20 people at any one time. The DCounts work perfectly, just take a little while to populate when the form is opened.

=DCount("[SAPNumber]","tbl_AccountBase","[Segmentation] <> 3 AND [ProspectAccount] <> True")
=DCount("[SAPNumber]","tbl_AccountBase","[Segmentation] = 3 AND [ProspectAccount] <> True")
=DCount("[SAPNumber]","tbl_AccountBase","[ProspectAccount] = True")
=DCount("[SAPNumber]","tbl_AccountBase","[SAPNumber] is not Null")

 

[spikepl]

30 sec for 4*18000 records is still loong. Do you have an index on all the criteria? If not can you put an index on them?

 

[Cavman]

Sorry, not too sure what you mean by 'index' on the criteria. Sorry if I'm being a bit slow!

 

[spikepl]

If you have a phone book (before search engines on the net), the entries are ordered alphabetically, so you easily can find john smith. If they werent ordered like that, you'd have to search the entire book for each entry. It is the same thing with indexes for data - so things take miliseconds instead of minutes or hours.

http://office.microsoft.com/en-001/...index-to-improve-performance-HA010210347.aspx

 

[Cavman]

I've just moved the DCounts into the On Current event rather than putting them in the text boxes. They now load instantly!

 

[spikepl]

OK - but you should still sort out the indexing , since data amounts normally tend to grow.

 

[Cavman]

Sorry, yes, every table in the database has an index field. It seems that running the DCount from the text box causes it to calculate a lot slower.

 

[spikepl]

Sorry, yes, every table in the database has an index field.

Huh? that is irrelevant. The relevant thing is that all the fields that are used in search criteria are indexed. Did you read the link I posted in #6?

 

[Cavman]

Thanks for that. That's the problem with being self-taught in a hurry - you miss some of the basics! I've now indexed all the fields in the accountbase that are used in searches. I'll keep that in mind in future.