DCount

[garyholc]

I have a few fields on a form which count the number of records in a table based on certain criteria. They work fine however it can take up to 30 seconds to display the values.

Is there any way of displaying text for example saying "loading" somewhere next to the field while the figures are being counted? Ive tried doing this based on the "on Change" and "after update" events but these dont seem to work.

Ive also tried creating another field next to the dcount field saying that if the dcount field is null then display "loading", but the dcount seems to activate before this field has chance to evaluate the dcount field and display the text "loading".

Any ideas?

Gary

 

[namliam]

Are you by chance counting some stuff in different ways in one and the same table?

If that is so you can probably do it faster...

If you have your DCounts as "values" or "Source" of your textboxes then they get auto activated and indeed you cannot (like you found) display "loading or somesort"

If instead you use code in your i.e. "on current" event or somesort you can indeed display something like "loading" while filling the textboxes after doing the queries in code to find your counts.

Feel free to ask more if you dont understand my mud.

 

[KenHigg]

I think I'd try to figure out a better way to come up with the values rather than using the dcount...

 

[garyholc]

Hi

Yes the dcounts all refer to the same table just with different criteria. Not sure how I can do what you say though... can you give me an example please?

 

[MarkK]

- Or you can index the table fields on which you base your criteria, and you'll find your DCount() will execute way faster.
- If your criteria leverage fields that are not indexed, Access needs to sort these fields from scratch and sorting is a non-trivial computing problem. If the fields are indexed the sort order is constructed on indexed fields when the record is saved, so there's the cost of indices, is it takes longer to save a record. This cost is not high if you save records one at a time, but if you commonly run update queries that change large numbers of records, your update queries will run much slower if each updated record requires multiple indices to be updated as well.
- So there's a little cost-benefit analysis of indexes.
- To index a field, open the table in design view, select the field, and on the General tab there is an Indexed property. Set it to 'Yes (Duplicates OK)'

 

[namliam]

you can make recordsets in a "on current event" to count your stuff in a query.

you can do all kinds of different counts on the same table in 1 query, instead of doing different DCounts on one table.
The query only accesses the table once, while each DCount does it once too.

You can probably imagine how 1 time access will beat 2 or more times accessing.

I dont know "how" you must do it... because a lot depends on what you exactly need...
but it will look something like

Dim RS as dao.recordset
set rs = Currentdb.Openrecordset ("Select query here")
Me.MyRecordcount1 = rs!MyRecordcount1
Me.MyRecordcount2 = rs!MyRecordcount2
...
me.MyRecordcount14 = rs!MyRecordcount14
rs.close
set rs =  nothing