Quicker processing of a Form

[Rick Vooys]

Hello All,

I have created a form, which has a calendar I create using labels, a label for each day. When I load the form I run code to assign each label a day, and gather the information for that day. In our database we have a shift, there is a shift on each day, and in each shift there are orders, and in each there are order items/cases. So each time I load a day's information I loop through each order and loop through each order item to count the cases. Each shift can have 25-30 orders. So you can see it can be looping through 25 days, 25 orders on each day, and anywhere from 1 - 60 cases on each order and it takes it's time. Not to mention it is over a network too. And add to it the data on this form must be refreshed, so I gather the information each time they use any of the features on the form. It is fairly useless the way it is. Any ideas on how to approach this another way or to speed up the queries.

-Rick

 

[Nikos Yannacopoulos]

Rick,

1.Declare a public variable dte.
2.Use the on click event of each date label to assign that
variable the requested date value (you must be doing
something similar anyway).
3.Use a function to return that variable:
Function req_date()
req_date = dte
end function
4.Make a totals query that sums the cases for a particular
date, and in the date field put req_date() in the criteria
line (this will get the date from the public variable).
Alternatively, you could run the query as an SQL string in
the SQL argument part of an openrecordset action, and get
the result from the pertinent field in the first record
(there will be only one anyway). In the latter case (SQL
query) you don't need the function, you can use the public
variable dte directly.
5.Have your code run this query and get the result
instead of nested looping through records (unnecessary if
you choose the SQL / recordset option).

It should be quite faster.

HTH,
Nikos

-----Original Message-----
Hello All,

I have created a form, which has a calendar I create

using labels, a label for each day. When I load the form I
run code to assign each label a day, and gather the
information for that day. In our database we have a shift,
there is a shift on each day, and in each shift there are
orders, and in each there are order items/cases. So each
time I load a day's information I loop through each order
and loop through each order item to count the cases. Each
shift can have 25-30 orders. So you can see it can be
looping through 25 days, 25 orders on each day, and
anywhere from 1 - 60 cases on each order and it takes it's
time. Not to mention it is over a network too. And add to
it the data on this form must be refreshed, so I gather
the information each time they use any of the features on
the form. It is fairly useless the way it is. Any ideas on
how to approach this another way or to speed up the
queries.