Solved

Acess 2010 - Pause Until Part Of Event Completes

Posted on 2016-11-28
14
27 Views
Last Modified: 2016-12-01
Hi Experts,

I know I asked a similar question before but I can't find it in EE.

I have a Form Event that Extracts data to a table and then runs a report using the extracted data from the table.  On slower PC's the report runs before the data is created and so thinks there is no data.  I don't want to 'pause' the processing for a specific period of time since I can't guarantee the PC's speed.

I think the answer before suggested doing a loop and 'checking' something that would indicated if the data extraction had completed.

I'm open to any suggestions.

Thanks,
Bob C.
0
Comment
Question by:Bob_Collison
  • 8
  • 5
14 Comments
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 150 total points
ID: 41904827
Bob, please post back to the original thread ... perhaps there is information we can give you about  a better approach.

There is no way to trap completion of a process without programming an indicator.  Your other post said you modified/deleted records that the form was based on and then, of course, you couldn't see them any more.  Let's take a deeper look at what you want to accomplish and help you with a better way ... just ask ~
0
 

Author Comment

by:Bob_Collison
ID: 41904888
Hi Crystal,

The simplest way to look at this is that I want to be able to not move from one section of Form Event Code to another without completing the first section.

Example
Part 1 Extracts data to a table which may take a while since it may involve a number of lookups and / or a large number of records.

Part 2 Runs an Access Report that has an Access Query as its data source.  The Access Query needs to read ALL data extracted to the table in Part 1or it won't have all data to say, sort with.

I don't want Part 2 to start before Part 1 completes.

Thanks,
Bob C.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904940
Last time I checked, code execution was linear.  Are you shelling out to other applications?  Please post the code and indicate where Access skips ahead.
0
 

Author Comment

by:Bob_Collison
ID: 41904995
Hi Pat,

It is linear.  I am assuming (incorrectly?) that the time lag is the issue.

The primary reason for this is that it works perfectly on a powerful PC (32 Gig RAM) but intermittently on a much smaller PCs.  To me this indicates that issue isn't with the code itself.  Therefore the fundamental question is why does this work on a high powered PC and not on one with much less power.

I am attaching the code.  A couple of words of explanation.

The Parameter Table contains a single record that contains the Parameter Values that are used by the Query.  The creation of the Parameter Record is a Common Function that I use in many instances without any issues.

At one time I used to delete the Parameter Record immediately after the Report ran.  It was suggested that I take this out and only run it before creating the Parameter Record and during general clean-up.  i.e. When a User Logs Off the System.

The Report Query uses 13 Tables to extract all of the data to support the report.

To provide the actual application issue code to you is quite a task.

Hopefully this will clarify the issue and the coding sufficiently.

Thanks,
Bob C.
R-10-210---Member-Info-Report---Maxi.txt
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41905162
Are you saying that - Call AddParameterDataTableRecord01 - doesn't complete prior to - DoCmd.OpenReport stDocName_R10210, acPreview?  You didn't post that code.

Did you try doing as Crystal suggested and set a variable in the called procedure that you check prior to opening the report?
0
 

Author Comment

by:Bob_Collison
ID: 41905657
Hi Pat,

Sorry I didn't post the Parameter Code as I use it extensively and it has never been problem so I didn't supply it to avoid cluttering up the issue.  I have attached it now.

I haven't added the Check Processing Status Variable Crystal suggested to the code yet but I will try it today and let you know the results.

Thanks,
Bob C.
AddParameterDataTableRecord01.txt
0
 

Author Comment

by:Bob_Collison
ID: 41907775
Hi Experts,

I have put the Check Processing Status Variable in the Code and the Parameter Record does always get created prior to running the Report.

However, today on the same PC as I was encountering the original issue (I am the only one using it and just for the testing) I can't get it to fail as it was originally.  I have tried a dozen times and it works successfully every time.

I did mention that the Report Query accesses 13 Tables.  Is this potentially an issue with performance and if so will a Report try to display before all of the data in the query has been extracted?

One possible solution I have though of would be to extract the data from the 13 tables into two tables and then change the query to only read the two tables.  Is this be a better approach.

Thanks,
Bob C.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41907950
Are you saying that the original version isn't failing or that the version that checks the complete flag isn't failing?  Isn't "isn't failing" good?

Is the query updating the underlying data but the report is still showing the old data?  We still haven't seen all the code you are running (and what we have seen is very difficult to read being all upper case and not indented or spaced out well).

13 tables or 2 shouldn't matter since you have to get the 13 down to 2 before you can use them.

The report runs it's own query.  You don't need to run it first.  In fact, the easy way to determine if the report has data is to use the NoData event of the report to either put something in a box and print one page or display an error and not open the report at all.
0
 

Author Comment

by:Bob_Collison
ID: 41908054
Hi Pat,

The original code (accde) that was failing is now not failing and I haven't changed the code or the data!  The report is showing the correct data. and not indication 'NoData' which was the original problem.

As mentioned it is very difficult to provide a complete copy of the code for this report along with the tables, data etc.  That is why I haven't provided it.  Sorry about the format of the code but obviously its not something I can change quickly.

I have the NoData Event coded in this and all my reports and it was what triggered me to the fact that there was a problem.  I display a No Data Error Message in a Message Field on the form from which it is run.

Currently the query is the Record Source for the report. The Parameter table is simply another table populated as required to pass criteria to the query.  My question was around whether having 13 tables linking together in a query was significantly less efficient than loading two tables with the required data using VBA Code and then having a simple query as the Record Source for the report.

At this point I don't know where to go with this as it is working so I can't really do anything else to try to get to the bottom of it.

By the way the 'new' pane displaying 'Suggested Solutions' I terribly irritating.  You might pass that on to EE Development.

Thanks,
Bob C.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 350 total points
ID: 41908907
To address the last comment first - I'm not sure I see the same view as you do.  Being an expert removes some of the more annoying aspects of the interface.  If you find something really annoying, create a new question, preferably with pictures.  If no one in authority responds within a day, press the request attention button to get a moderator to specifically look at your question.

It is very difficult to track down intermittent problems.  And not having the database, makes it pretty much impossible.  If the BE is SQL Server or some other RDBMS, it might be that your network is dropping a connection and that is interrupting the flow of data to the report.

Making temp tables causes bloat so I don't recommend it.  In the apps where I import and manipulate data from other systems, I always import to a "template" database so that each time I run the process I can start with an empty template and never have to worry about deleting and then compacting to recover the orphaned space.  So the first step in the process is to copy the template from the "templates" directory into the local directory and overlay the old version that was there previously.  Then I run the rest of the code that either imports the temporary data or creates the temp tables.  The only benefit of creating temp tables is if you do a lot of aggregation or complex calculation and multiple processes can make use of the intermediate results.  Otherwise, stick with the query you have or if your BE is SQL Server, you might consider making views to pull together some of the more commonly joined tables.
0
 

Author Comment

by:Bob_Collison
ID: 41908960
Hi Pat,

Thanks for your comments / suggestions.

I assumed that creating Temp Tables creates bloat.  The tables I am referring to making would be permanent BE Tables that I load with data,  as you suggested containing often used data from multiple tables,  use and them empty as you suggested.

All of my BE Tables are on the same PC as the FE Application.  I don't use SQL Server.

I also appreciate your suggestion to stick with Queries regarding performance.

At this point since I can't duplicate the problem any more I'm closing the case.

Thanks,
Bob C.
0
 

Author Closing Comment

by:Bob_Collison
ID: 41908963
Thanks for your assistance.  Although your commnets suggestions didn't solve the issue they provided additional insight into best practices.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41908973
Temp tables fall into two categories.
1. Make table queries that make a new table each time.
2. Permanent tables that you run delete and append queries against.

Both cause the same bloat issue.  Access cannot recover free space unless you compact a database so if you add a million rows and delete them and add a million more, the table space occupies 2 million rows until you compact.  The append does not overlay the  deleted original contents.

The second option is better than the first because you can define indexes which help with other processes but the best solution is the one I described earlier where the "temp" table is in a separate BE and you use a fresh copy of the BE each time you start the process.  The template database contains whatever tables you need along with any indexes you want.  make sure to empty the tables and compact the database after you build it.  That way it always starts as small as it can be.

Sorry I couldn't solve the actual problem.
0
 

Author Comment

by:Bob_Collison
ID: 41909046
Hi Pat,

Thanks for the additional information.

Thanks again,
Bob C.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now