473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to force macro to end based on certain conditions?

We have a Macro that runs many queries and we want to force it to abort based on the values we find in the database. Any ideas on the best method for doing this?

More details: The database is being populated by another system so we want to make sure that their update finished. If we set up a table with a status value in it then we can just read that table and see if their update finished properly. If so then the macro can run all of the rest of the quries in it. If the status show us the update did not finish then we want our macro to skip to the end or just stop.
Jan 26 '11 #1
2 4953
Stewart Ross
2,545 Expert Mod 2GB
Hi. You'd need to convert your macro's steps to the equivalent VBA code to do this, as you cannot perform the checks needed at each step from within a macro.

In VBA code you have full control of the way you check the status value and can prevent the other steps from executing relatively straightforwardly. A skeleton of what I mean is listed below:

Expand|Select|Wrap|Line Numbers
  1. Public Function fRunUpdates() as Boolean
  2.   'Returns TRUE if all actions run to completion
  3.   'FALSE if not
  4.   DoCmd.SetWarnings False
  5.   DoCmd.OpenQuery "qryYourUpdate1"
  6.   if YourStatusValue = 0 then goto exit_function
  7.   DoCmd.OpenQuery "qryYourUpdate2"
  8.   if YourStatusValue = 0 then goto exit_function
  9.   DoCmd.OpenQuery "qryYourUpdate3"
  10.   if YourStatusValue = 0 then goto exit_function
  11.   DoCmd.OpenQuery "qryYourUpdate4"
  12.   if YourStatusValue = 0 then goto exit_function
  13.   ...
  14.   DoCmd.OpenQuery "qryYourUpdate9"
  15.   if YourStatusValue = 0 then goto exit_function
  16.   DoCmd.SetWarnings True
  17.   fRunUpdates = True
  18.   Exit Function
  19. exit_function:
  20.   Docmd.SetWarnings True
  21.   fRunUpdates = False
  22. End Function

All this does is to test the status value after each action query is run. If the status value is not as expected then the function is exited cleanly returning False as its output value to indicate that it did not run to completion.

I do not know what your status value is or how you would access it, so I've put a dummy test in the skeleton. You'd have to work out how to test the value as appropriate for yourself.

-Stewart
Jan 27 '11 #2
Thanks Stewart,
I'll probabaly convert this to VBA at a later date. For now I've found an quick interim solution to controlling macros in access 2007 via the condition column in the macro design page. I put this in the condition column:
[Reports]![StatusRpt]![Status Indicator]="Started"
Which reads a value from a table as defined by the report. If it = Started or if it = Done I can trigger the Macro to do different things. Not as robust as VBA but it'll do for now.... Thanks for responding, John
Jan 27 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Anne | last post by:
I have a report containing multiple subreports (approximately 37 subreports) that are based on individual queries. The query calls to only display the subreport if there is valid data in the table...
8
by: PJ6 | last post by:
I'm having a problem I can't reproduce in a simple example. If I create a class that inherits button and click on it in a web page, if I override the OnClick method, on clicking, any code is...
5
by: Uday Deo | last post by:
Hi everyone, I am looping through 4 nested loops and I would like to break in the inner most loop on certain condition and get the control on the 2 nd loop instead of 3rd loop. Here is briefly...
1
by: chris962 | last post by:
I have very basic knowledge of Javascript and am trying to get a web page to play one of two different sound files based on whether the user has correctly guessed a number. At the moment, all I...
1
by: RSH | last post by:
I have a custom WebControl that I created. The template is used sitewide. I would like to use this template for every page but there are instances when blocks of HTML need to be displayed only in...
2
by: Chris Thomasson | last post by:
I was wondering if the 'SLINK_*' and 'SLIST_*' macros, which implement a simple singly-linked list, will produce _any_ possible undefined behavior: ____________________________ #include...
4
by: lightaiyee | last post by:
Dear Gurus, I would like to implement a function that computes the number of times a certain condition is met in a global array. For example, I have an global array of size 500. float array;...
2
by: daniel2335 | last post by:
Is it possible to add code into the database, so that it monitors certain value and performs an operation when certain conditions are true? I have a table with research in each having a completion...
17
by: MNNovice | last post by:
Calculate net amount based on conditions ________________________________________ I need to calculate a net amount that is based on FedRatio. Normally the FedRatio is set to be 80% but there are a...
0
by: sadhana86 | last post by:
Hi There, I have a excel sheet which is a report, which I run in a SAP application. Now I have so many fields, below is the example of the report: User_iD Action Info_type Sub_Info_Type ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.