Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    Unanswered: Running Query and Executing Script For Each Record Found

    This issue came up because of a solution to my earlier thread (http://www.dbforums.com/brilliant-da...debugging.html) and it is certainly worthy of starting a new thread for this question.

    Assume that I want to do the following: 1). run a query and 2). run some script after running the query to each of the records found in the query. My understanding that in the Query Editor, I first define the query parameters (see first attachment). Next, on the ‘Actions’ tab, I have to select ‘Execute a script one time after query is complete’. In order to have the script execute for each of the records found in the query just performed I have to assign a Recordset variable to these records and run a ‘For Each… Next’ loop containing the commands (see second attachment). If this Recordset variable is not defined, the program will only run the script one time (which only effects the first record that is encountered).

    I do not want to run into any issues with the Recordset becoming too large yet it appears to me that regardless of whether I run the script in the as a query or in the form editor that the Recordset variable may become really large. How can I run scripting to the queried results without having a Recordset?

    Edit - Is my interpretation of 'Execute a script for each record' invalid? If I use that instead of 'Execute a script one time after query is complete', will it run the script on the records from the query? If this true, what is the difference in the two ('Execute a script for each record' and 'Execute a script one time after query is complete')?
    Attached Thumbnails Attached Thumbnails query 1.jpg   query 2.jpg  
    Last edited by fireant911; 06-26-14 at 12:07. Reason: added edit statement which is actually another question
    Daryl G

  2. #2
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    My understanding that in the Query Editor, I first define the query parameters (see first attachment). Next, on the ‘Actions’ tab, I have to select ‘Execute a script one time after query is complete’. In order to have the script execute for each of the records found in the query just performed I have to assign a Recordset variable to these records and run a ‘For Each… Next’ loop containing the commands (see second attachment). If this Recordset variable is not defined, the program will only run the script one time (which only effects the first record that is encountered).

    If I understand stand you correctly, you don't need a recordset variable or a loop at all to do that. Everything can be accomplished directly by the Query all on it's own.

    Your Query as you have it right now is perfect as it is. The only thing you need to do is go to the actions tab of the query, tick the box “Execute a script for each record” and populate this with lines 4 and 5 of the code you have displayed in the second image.

    Line 1 of your code can be populated into the Query action tab “Execute a script before running a query”.

    Edit - Is my interpretation of 'Execute a script for each record' invalid? If I use that instead of 'Execute a script one time after query is complete', will it run the script on the records from the query? If this true, what is the difference in the two ('Execute a script for each record' and 'Execute a script one time after query is complete')?
    Think of 'Execute a script for each record' as your ‘For Each… Next’ loop with the advantage being that there is no need to push a list of records the query found into a variable, or the need to actually write a loop as if you populate this option with code, it will do whatever you tell it to do to every single record the query finds.

    'Execute a script one time after query is complete' does not have the ability to make any changes to each record the query finds. It's more suited to working with batches of records for tasks like... move all completed records to a other folder and so on.

    Regards
    James

  3. #3
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    James,
    Thanks. With each small step forward I seem to move eight steps back. I am in process of removing the Recordset variables and replacing them with a queries as you recommended. Half way through my project I come across a query that ABSOLUTELY refuses to run when I include 'Execute query' in my script (actually, it does run but repeatedly says that 'no records were found'). When I run it from the query editor, it does just what it is supposed to do. I have checked and rechecked everything but nothing seems to work. I have also included 'show message' to show what the values of the variables are at the time of running and everything is spot on. What I am attempting to do is to compare a global variable to a field on the screen using 'contains' comparator. There is really not much else that can be changed yet it simply will not produce the proper results. I have spent about six hours alone on this query... only to get the same answer went I run it ('no records were found') although I can easy see that there are 827 record matches for the specified query.
    Daryl G

  4. #4
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi Daryl
    Solving your query problem (not running) might be easier if we had a sample file with just enough data to simulate application.

    Regards
    David

  5. #5
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    If you want to send over your file with steps to follow that recreate the problem, I can take a look.

    Regards
    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •