Speed up Dcount

[buratti]

I have a form that calculates and displays inventory of certain items on hand for each day of the week. The procedure is in a do-while loop that calls a function 24 times (with different criteria sent each time) and assigns the value each time to a different unbound field in that form.

Everything is working just fine in means of calculating and asigning everything correctly, but there is an issue with speed! It takes about 2 minutes to load the form and calculate all the values. The function that is called those 24 times contains several Dcount() functions. I'm looking for something to replace those Dcount() functions with something faster.

I found the Tcount module and replaced all Dcount() with Tcount() and not much speed difference, and also got some errors with Tcount(). I'm thinking maybe something along the lines of recordsets. I haven't used them much and dont know all the uses I can do with it but maybe someone here can assist me with doing it. My current code is:

Function SetformState()
Dim i As Integer
Dim n As Integer
Dim a(1 To 12) As String
a(1) = "Monday"
a(2) = "Tuesday"
a(3) = "Wendesday"
a(4) = "Thursday"
a(5) = "Friday"
a(6) = "Saturday"
i = 1
Do While i < 25
    Select Case i
        Case 1 To 6
            Me("text" & i) = Availableboxes("10 yard box", a(i))
            i = i + 1
        Case 7 To 12
            Me("text" & i) = Availableboxes("15 yard box", a(i))
            i = i + 1
        Case 13 To 18
            'i varaible gets out of range for using it in array declare new array value...
            n = 1
            Me("text" & i) = Availableboxes("20 yard box", a(n))
            i = i + 1
            n = n + 1
        Case 19 To 24
            Me("text" & i) = Availableboxes("30 yard box", a(n))
            i = i + 1
            n = n + 1
    End Select
Loop
 
End Function
 
 
 
Function Availableboxes(Sizelookup, Day As String)
Dim TotalforSize As Integer
Dim Numberout1 As Integer
Dim Numberout2 As Integer
Dim TotalNumberout As Integer
Dim ScheduledforDelivery As Integer
Dim ScheduledforPickup As Integer
Dim Size As String
Dim Str As String
 
 
Debug.Print Sizelookup
TotalforSize = DLookup("[quantity]", "[Box Inventory]", "[Size] ='" & Sizelookup & "'")
Debug.Print TotalforSize
 
Numberout1 = DCount("*", "[Scheduled Dumpsters Local]", "[Delivery Day] = 'past'" _
& "and [Pickup Day]  <> 'past'" _
& "and [Size] = 'Concrete Load'" _
& "and InStr(1,[Description], """ & Left(Sizelookup, 2) & """)>0" _
& "or [Size] = 'stumps'" _
& "and InStr(1,[Description], """ & Left(Sizelookup, 2) & """)>0")
Debug.Print Numberout1
 
Numberout2 = DCount("*", "[Scheduled Dumpsters Local]", "[Delivery Day] = 'past'" _
& "and [Pickup Day]  <> 'past'" _
& "and [Size] ='" & Sizelookup & "'")
Debug.Print Numberout2
 
TotalNumberout = Numberout1 + Numberout2
Debug.Print TotalNumberout
 
ScheduledforDelivery = DCount("*", "[Scheduled Dumpsters Local]", "[Delivery Day] <> 'Past'" _
& "and [Size] ='" & Sizelookup & "'")
Debug.Print ScheduledforDelivery
 
ScheduledforPickup = DCount("*", "[scheduled Dumpsters Local]", "[Pickup Day] = '" & Day & "'" _
& "and [Size] ='" & Sizelookup & "'")
Debug.Print ScheduledforPickup
 
Availableboxes = TotalforSize - TotalNumberout - ScheduledforDelivery + ScheduledforPickup
 
End Function

Maybe something like:
dim rs as recordset
set rs = currentDB().openrecordset("[scheduled Dumpsters Local]")
rs.recordcount......
But I dont know how to filter the recordset and then count the number in that filter.
i would need to use the criteria of each of the current Dcount() functions in each .recordcount procedure.
How would i do that, and is using recordsets going to be faster? If not what else can I do to speed up this process?

 

[HiTechCoach]

unfortunately you did not post the most important part. We really need to see all the VBA including the part that has the DCount().

 

[buratti]

unfortunately you did not post the most important part. We really need to see all the VBA including the part that has the DCount().

I'm sorry, but I did include the VBA function/code that includes all the Dcount()'s... Unless there was an error in the post or I am missing what you meant by this response, but I can see all the Dcount()'s on my screen.

I have the form set up as follows:
The form that contains the code is an unbound form that is basically just a navigational form that mostly contains buttons to open other forms and reports along with displaying the data with the calculations in question.

On open of the form i call the function SetformState with no need to pass variables or values to it.

The SetformState function loops a call to the AvailableBoxes function, sending different values/criteria with each loop and returning the value to a filed named text1, text2... text24 with each loop.

The AvailableBoxes function is what contains the multiple Dcount()'s

All of the Dcount() functions are counting from the same query named [Scheduled Dumpsters Local], but each one with different criteria

Before going any further, if opening the query [Scheduled Dumpsters Local] as a recordset and manipulating/counting data from there be any faster??? If so, then lets continue, but if not, then just let me know, and I'll find a way to deal with the speed issue.

 

[HiTechCoach]

Sorry about that. For some reason there were not any scrollbars on the Code windows. I have had lots of issue with that here at AWF.

I can see all the VBA code now!

After taking a peak at your Code, I would agree that using the DCount() is not very efficient. I would definitely do it different.

If at all possible, I would use a bound form as a sub form on you main form to display the records in a continuous or data sheet view.

Off the top of my head, I would use a series of Append query that appends a record data into a table for each stat. (after deleting that current data). This would allow you to use a bound form as a sub form..

I would put the code in a function that can bee called form a command button that will allow you to refresh the data as needed.


Basic Steps

1) run query to empty table
2) run all the append querys to get the data into the table
3) requery the bound form

The are many advantages to using a table over unbound text boxes.

 

[buratti]

Thanks for the suggestion. In all honesty you totally lost me with that. But don't worry about explaining further. I was reviewing the code and although everything was working, my calculations were incorrect on how to count the inventory anyway. So I ended up rewriting the whole thing and the correct formula for calculating values turned out to be much simpler anyway. I also removed the tracking for each day (the 24 unbound text boxes) and just made a summary of each. Sorry if I wasted your time.

 

[vbaInet]

Your initial thought about using a recordset for counting records was a good one. They are actually much faster. What you could do is build a recordset count function in a module that takes the SQL string as its argument and returns the count. Also, for the purposes of just counting, use a Snapshot.

 

[HiTechCoach]

Thanks for the suggestion. In all honesty you totally lost me with that. But don't worry about explaining further. I was reviewing the code and although everything was working, my calculations were incorrect on how to count the inventory anyway. So I ended up rewriting the whole thing and the correct formula for calculating values turned out to be much simpler anyway. I also removed the tracking for each day (the 24 unbound text boxes) and just made a summary of each. Sorry if I wasted your time.

Glad you got a working solution.

Sorry if my table based method was hard to follow. I actually use it a lot as a dashboard that has drill-down abilities.

 

[Galaxiom]

Each time a Domain function is used is essentially a query on a recordset. While they can be useful to grab a single value for a textbox they should be used sparingly.

Where multiple values are required it is far better to construct a query as the recordsource of a subform to feed into bound textboxes as Boyd explained above.