Query extremely slow after adding VBA function PlusWorkdays

[penguino29]

Hi sseto23,

Ok, I've churned out some codes that should do the job - I am no VBA expert so my code is a bit crude and not fancy, but it should show you the speed improvement that you are looking for.

I've kept it simple, the idea is to:

• create a one-dimensional array,
• loading the holidays table and storing the dates into the array
• create a function to search in the array for a given date

Then I modified your function, instead of using Dlookup, now it uses the search function to find the holidays from the array.

Try out the codes and report back please. Thanks.

 

[sseto23]

Thanks Penguino29. It will be a couple of days before I will get the chance to try out the code since I will be out of town, but I will definitely try it out when I get the chance and report back to you.

With regards to using PlusWorkdays + 1, I used to calculate my dates that way but I was told by someone at my previous company who was supposed to be somewhat of an Access expert that you shouldn't do it that way, and that you should use a function like DateAdd instead.

Thanks for all your patience and help!

 

[gemma-the-husky]

with respect to adding whole numbers to days, rather than dateadd, there is no problem with this at all.

eg, given a date, and i want to find the next sunday, say. i tend to

while weekday(somedate)<>vbsunday
somedate=somedate+1
wend

i am sure there are quicker ways, but this is only a handful of clock cycles, and i can't be bothered.

 

[sseto23]

Okay, I'm looking at the sample code that Penguino29 provided, and I think I'm missing something

Where/how do I call the subroutine initVar() to initially create and load the array with the holidays from the holiday table?

I assume I would need to do this once up front somewhere before the query to generate the report is run, since the query calls the function PlusWorkDays() to calculate the next call date based on business days.

What am I missing???

 

[penguino29]

Hi sseto23

Nice to see you are back.

You can call the InitVar() when the print report button is pushed (but before the date calculation begins)

In fact you can call it anywhere (say at the switchboard). As long as you call it at least once, and after that you can then run your calculations.

 

[vbaInet]

In fact you can call it anywhere (say at the switchboard). As long as you call it at least once, and after that you can then run your calculations.

Just to add to this, call it only when you want to run the report. Next time you run the report call it again ONLY IF the array contains something, so one way would be UBound(ArrayName) <> 0.

 

[sseto23]

Thanks Pengiono29....yes I'm back from vacation but I can't say that it's nice to be back since I went from sunny and 24 degrees Celsius back to rainy and 12 degrees Celsius (brrrrrrrr).

Okay, this is where I'm at now....

My setup for the application's reports is that we use a frame (frame1) which contains option buttons for all the reports, say around 15 of them. There are 2 command buttons called Preview Report and Print Report. When you click on either command button it executes a particular section of a macro called Print which looks something like this:

Macro Name Condition Action

Preview [Frame1] = 1 OpenReport
[Frame1] = 2 OpenReport
[Frame1] = 3 OpenReport
. .
. .
[Frame1] = 15 OpenReport
Print [Frame1] = 1 OpenReport
[Frame1] = 2 OpenReport
[Frame1] = 3 OpenReport
. .
. .
[Frame1] = 15 OpenReport

The only difference between the OpenReport command in the Preview section versus the OpenReport command in the Print section is the view type in the action arguments ie. either print preview or print.

Right now to use the date calculation code you provided, I did some stuff that might be a bit unnecessary or convoluted

I changed the subroutine InitVar () to a public function, and then for each of the reports that I needed to do the business date calculations, I created a macro which contained 2 steps, the first step is RunCode InitVar() and the second step is the original OpenReport command, and then in the Print macro I changed the action from the OpenReport command to the RunMacro command. It works, but is that overkill???

I thought about putting the call to InitVar() once at startup as you mentioned, but I'm not sure how to do that ????? Would I put a RunCode command in the Autoexec macro or when I load or open the first form???? If I did this, since there are multiple users using the single version of the database (no, we haven't done any database splitting yet) what would that mean if one user is running the report with the business day calculations and someone else opens the database and causes the startup to run the loading of the array again?

Thanks to everyone for helping me get to this point because I would never have made it this far without all your great ideas!

 

[penguino29]

Hi sseto23,

Hope you enjoyed your vacation!

Your plan using the macros all seems fine. Nothing is overkill. The initvar() is not such a big deal as long as it is called beforehand. Frankly it *can* be called multiple times and its not an issue.

If you wish to just call it once like you have figured - you can put it in the Autoexec. Yes you can use runcode which is perfect.

Effectively, each user is loading the holidays into an array which sits in the memory of each PC during the code execution. There's nothing wrong with this in a multiuser situation: unless there is a change of the dates in the table - then initvar() must reload/refesh its array content to reflect the new changes.

Therefore it might be advisable to run initvar() just prior to the report being calculated - so that the pc fetches the latest holidays info prior producing the reports. Your 2-step macros method is doing this so its ok!

Let's get it working and see if it does speed things up!

 

[sseto23]

Yes! Things did speed up tremendously! The user reported that when she initially ran her testing it took over 15 minutes, but now it's down to under 1 minute

Thanks for all your help!!!! I couldn't have done it without you! I really think this forum is great!

 

[penguino29]

Hi sseto23!

Great to have the news and it has worked! Thanks for the feedback and we are glad to help! Now you deserve to have another vacation.

I would like to thank vbaInet, gemma-the-husky, CBrighton and spikepl who also gave their input to this topic.

 

[vbaInet]

Now you deserve to have another vacation.

Don't forget to add that to your Holidays table

 

[sseto23]

LOL! Most definitely. Thanks again everyone!