Class modules and Events

[malcmail]

I'm looking for an utter idiots guide to user defined events and how to use them in my code. I've found some stuff but, to be honest, as a non-programmer (beyond what I did 30 years ago and years of using VBA since) its double dutch. Really just looking to put together an event to tell me when the last of a number of external data queries have finished refreshing so that I can get the rest of the code going. As the next bit of code relies on the results I can't used background refresh but seeing as there are about 6 queries I don't want to run them one after another either. Hence the problem - and the need for an idiots guide on custom events. Many thanks.

[Andy Pope]

The querytable object has a event you might be able to use.

In order to create the event you need to use a class object.

Class module Class1

Please Login or Register  to view this content.

normal code module

Please Login or Register  to view this content.

When it works you should see the 2 msgboxes in the test routine get executed, whilst the message box in the event will happen once refresh completes.

[malcmail]

Andy

That may be the first time I've made head or tail out of what has been going on with the event handler like this. So presumably I could use the code as given and then loop with a second delay say in my main code until such time as the result is the data has been refreshed?

[malcmail]

Actually ignore that rubbish. Just twigged what was actually going on. Thanks again.

[malcmail]

Andy

Sorry to be a nuisance. Wondered if you could help me a bit further with this - mainly due to my incompetence with it!!

If I have say 5 queries and I need to know when the last one has finished how would I implement that? I have played around with for loops etc but as it sets each connection as qQueryCallback it doesn't work as expected. I've also played around with a lot of other stuff but can't quite implement it as I had hoped. Thanks.

[Andy Pope]

Something like this, where you have a property in the class to determine is completed.
Then you run a routine when the complete event fires. If all have then your done.

Note, I have not tested any of this code.

Standard code module

Please Login or Register  to view this content.

Class module

Please Login or Register  to view this content.

[malcmail]

Cheers Andy

In the TestbackgroundRefresh it runs through connindex OK but the piece of code to refresh in for each callback doesn't appear to run. So I get the "done with this code" msgbox but not the "start in background" ones.

If I run the CheckAllRefreshes code it fails at for each callback as there's no object - not sur if this is down to the first part of code failing or not. As you can guess I'm out of my current knowledge zone to work that bit out!

[Andy Pope]

Sorry forgot to add each callback in to the collection so they would remain in memory

Please Login or Register  to view this content.

[malcmail]

Thanks for your patience here - much appreciated.

That added line fails due to no object or with block.

[Andy Pope]

I think it's the gQueryCallback and qQueryCallbacks. When you type in forum exit box it underlines in red and you can't see the different between q and g

Please Login or Register  to view this content.

I have tested this code and it works for my query test

[malcmail]

I should really have spotted that on the VBA editor itself. Works a treat. Struggling to follow it though. Not sure how the CheckAllRefreshes code starts without apparently having a call to it. Looks like it is to do with the callbacks in gQueryCallbacks but that's about as far as I can work out *dummy*

[Andy Pope]

That routines is being called from the event in the class. So originally it just showed a msgbox, now it calls a routine to check all objects in the collection.

Please Login or Register  to view this content.

[malcmail]

Doh! Didn't look in the class module (like a plum). Not going to pretend I know all of the ins and outs of what is happening there but can see it is doing what I need - now just to wrap it into my code now. If I call TestbackgroundRefresh from my main code to start the refresh routine, how do I stop the rest of the main routine running until the CheckAllRefreshes has finished (never had to work with anything like this before)

[malcmail]

I tried some variations of this but looking at the Queries and Connections panel the refresh never starts and I have to escape the code running, no idea why though as my tiny brain thinks it should work OK.

Please Login or Register  to view this content.

The CheckAllRefreshes instead of giving a msgbox to say it is done sets the flag RefreshComplete to true. What did I get wrong?

[Andy Pope]

Not sure what you are doing.

Perhaps this is the approach you should be doing.

Please Login or Register  to view this content.

You kick off the main_test routine, which starts all your queries.
The once the CheckAllRefreshs is complete you execute the rest of your code.

[malcmail]

Good point that! I was so stuck in the code I had I couldn't see a way round it. Thanks very much for the help with this. Hopefully be golden from here. And you've left me with plenty learning to be getting on with in there too!

[malcmail]

Andy

Sorry to pester you. Was wondering something. Is there any way to get the name of the query that has caused the event to trigger (ie has just completed)? I can get the name easily enough in the TestBackgroundrefresh but not in either the class event or the CheckAllRefreshes. Thanks again.

[Andy Pope]

Pass as an argument when you call

Please Login or Register  to view this content.

You will also need to add argument to the CheckAllRefreshes routine

[malcmail]

Tried everywhere except there with that as a combination - cheers for that.

[malcmail]

Think I'm being a bit dim - added the above to the class module. In CheckAllRefreshes changed the opening line to:

Please Login or Register  to view this content.

Application-defined or object-defined error in the class module at the CheckAllRefreshes MyQuery.Name line. Any ideas?

[Andy Pope]

I had a look, using the locals window, at the MyQuery object and it appears the Name property is not assigned, which causes the error.

Try this.

Please Login or Register  to view this content.

You can also have a look through the object in local window to see if there is another property that would be more useful for you purpose if that name is not.

[malcmail]

That worked like an absolute charm. Thanks for the time and effort. Much appreciated.