DCount too slow

I have a suggestion, you can place all Your DCount function in the Form_Load Event.

Remove from the text box ControlSource and try in Form_Load Event.

just try

like this

Private Sub Form_Load()
Text4 = DCount("*", "Table1", "( [Table1]![Data]< 10)")
End Sub

Not sure if this will help. You can place this in a public module and then call it like DCount. It may not speed things up but then again I've always found it to be quite fast. You can tailor it to filter the the parameters but generally just follow the DCount rules.  Hope it helps.

Use:
MyDCount("*", "Diabetes_Master", "Total_C<4.0")

'***************************
Public Function MyDCount(MyField As String, MyTable As String, MyCriteria As String) as long

Dim MyDataBase As Database, MyTable As Recordset
Dim strSQL As String

strSQL = "Select " & MyField & " from " & MyTable & " WHERE ” & MyCriteria

Set MyDataBase = CurrentDb()
Set MyTable = MyDataBase.OpenRecordset("strSQL")          ' Open Recordset.
MyTable.MoveLast

MyDCount = MyTable.RecordCount

MyTable.Close
Set MyTable = Nothing
MyDataBase.Close
set mydatabase = nothing

End Function

You can check out:
http://www.mvps.org/access/modules/mdl0012.htm

Do some or all of the text box counts come from the same table? You may be able to combine a few of the above answers to speed things up even more.

for example if you have 5 text boxes to fill with counts from different fields in the same table.  Leave the text boxes unbound, open a recordset with the 5 fields and every record. Loop thru the recordset, filling the 5 variables with the count, put the counts in the text boxes at the end.

You would only open one recordset, and only loop thru it once.
eddiej

Oops! I found a small error in my function.  I created a duplicate definition with 'MyTable as String'. Should Be:

Public Function MyDCount(MyField As String, MySearchTable As String, MyCriteria As String) as long
Dim MyDataBase As Database, MyTable As Recordset
Dim strSQL As String

strSQL = "Select " & MyField & " from " & MySearchTable & " WHERE ” & MyCriteria

Set MyDataBase = CurrentDb()
Set MyTable = MyDataBase.OpenRecordset("strSQL")          ' Open Recordset.
MyTable.MoveLast

MyDCount = MyTable.RecordCount

MyTable.Close
Set MyTable = Nothing
MyDataBase.Close
set mydatabase = nothing

End Function

I also found something very weird while testing the function.  You can't use an upper case 'WHERE' when concactenating the strSQL statement.  Also, the function is very fast:

Change this:0
strSQL = "Select " & MyField & " from " & MySearchTable & " WHERE ” & MyCriteria

To this:
strSQL = "Select " & MyField & " from " & MySearchTable & " where ” & MyCriteria

It's weird but Access keeps appending a (") at the of the first statement where 'Where' is uppercase. Very strange.


************************************
Get a count of Customer that start with the letter C:

MyDCount("*", "tblCustomers", "fldConsignee Like '*C*'")
**********************************************

Get a count of Customer named Joe Blo:

MyDCount("*", "tblCustomers", "fldConsignee='Joe Blo'")
**********************************************

Dang, I'm pretty sloppy today.  Here's another correction:

If Not IsNull(MyCriteria) Then
    strSQL = "Select " & MyField & " from " & MySearchTable & " where " & MyCriteria
Else
    strSQL = "Select " & MyField & " from " & MySearchTable
End If

Arji,

What's the difference between the function you posted and the original one that I posted?  Seems like the same basic idea of using recordsets and dynamic SQL to retrive the result.

Mike

Just less processing to keep it as fast as possible. Your Set rstLookup = .... statements will slow things down.  Instead of using a Count field and then reading the results, I simply read a property of the recordset(RecordCount). It's much faster because there is no field processing(Count).  I just open the recordset and grab the recordcount.  DCount is still probably faster but it's always worth a try. It also depends on whether the criteria field is indexed or not. That is probably the only way to speed things up significantly.

Aha...I posted the wrong function!  I meant to post a different function that uses the same methodology as yours Arji.  Looks like I need to update my source code library.

Yeah, I always try to use properties of recordsets if I can. RecordCount is a valuable one.

One tip for your function though.  Before using the MoveLast method, you should check to make sure the recordset isn't empty.

If Not MyTable.BOF and Not MyTable.EOF Then
   MyTable.MoveLast
   MyDCount = MyTable.RecordCount
Else
   MyDCount = 0
End If

Actually if it was empty then RecordCount would be 0. In Access 2003 you really don't have to jump the end to get an accurate count.  I think there is a problem with early versions though. So this would be good too:

if MyTable.RecordCount > 0 then
   MyDCount = MyTable.RecordCount
Else
   MyDCount = 0
end if

I'm in the habit of checking BOF and EOF from earlier versions.  Trying to go to the last record using MoveLast would generate an error if there were no records.  That's been fixed in 2003?  Good to know.

verdante,
Thanks for the points.  Did it work any faster than DCount for your situation?

Arji
Sure did! Was looking at a database with 10K records. Thanks a heap,
Verdy