Solved

Stopping execution of code while print previewing reports access 2010

Posted on 2016-11-28
24
24 Views
Last Modified: 2016-11-28
Hello all:

Here is my scenario:

I have a form that has two subforms on it.  These subforms (views as datasheets on the main form) are bound to a temp table that stores the data until I post.  Once I post, it posts to a table and then deletes the data from the temp table.

I also have a report that a user can preview/print when after they post the information. This report takes data directly from the form and has two subreports that queries the same temp table to populate the subform's information.  The issue is when a user clicks on post, it asks the user if they want to preview the report.  If they click yes, the reports appears.  The execution also continues to post the information.  Herein lies my issue.  While the report previews correctly (with all of the subreport's data from the temp table, if the use then prints the report the main report info is there, but the subreport's info is gone (because the execution posted the data and deleted the info from the temp table).

So I either need to do one of two things:  Stop the execution of my code until the user either closes the preview, or take the information directly from the subform (similar to how I am taking it from the main form) to populate the subreport's information.  Not too sure which I prefer, but I just need to have the data still in the report when it is printed.

I tried to add a Do while CurrentProject.OpenReport("reportName").IsLoaded = true, but it never displayed the report and created an infinite loop.  Also tried an If condition to pop up a msgbox asking if the user wanted to continue with the posting, but this didn't work either.

Any thoughts and help would be tremendously appreciated.

Thanks
0
Comment
Question by:Juan Ocasio
  • 9
  • 7
  • 6
  • +1
24 Comments
 
LVL 57
ID: 41904281
<<Any thoughts and help would be tremendously appreciated.>>

 Open the report in dialog mode.  That will stop code from executing until the report is closed.

Jim.
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904301
Thanks Jim:

I've also tried that, however it prevents the user from printing the report if they need to, so I need another solution that will allow it.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41904477
Separate the posting from the preview.  Then they can open the report and print as necessary.  When they are happy with the results, then they can post.  

Do some reading on coupling and cohesion.  It is an old programming concept that explains the problems caused by coupling unrelated actions.  You are taking two actions which are coupled (procedural) and shouldn't be and trying to make them more tightly coupled.
https://thebojan.ninja/2015/04/08/high-cohesion-loose-coupling/
0
 
LVL 18
ID: 41904528
you could put some sort of indicator in the code to signal it is done such as setting a database property or tempvar. Then use loop through and Sleep for x time till the indicator is set. I do not recommend DoEvents in the loop since it is resource-intense. While the system is busy, don't let the user do other things.
'declare Sleep API
#If VBA7 Then             '  Code is running in the new VBA7 editor
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else                     ' Code is running in VBA version 6 or earlier
   Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Public Sub WaitMilliSeconds(nNumMilliseconds as long)
    'pause
    Sleep iNumMilliseconds
End Sub

Open in new window

then you can make a loop to sleep until your indicator is true
dim bBoo as boolean
bBoo = false
do until bBoo = true
   call WaitMilliSeconds(100) 'or however long you want to wait
   bBoo = add code to test indicator
loop

Open in new window

0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904541
Thanks Pat:

Yes, I would love to put a preview button on the form, however the end users do not want that unfortunately; they want to be able to click post and then a message box appears to ask if they want to see a preview.  I know it sound odd and even inefficient, but unfortunately I didn't win that battle :(

Crystal:

I will look into your suggestion to see if it will work in our application.  I was hoping there was some sort of event  I could trigger that would send execution back to the form. I'll have to do a bit more digging as well.

The other alternative is to populate the report with all of the data on the subform, thereby removing the bound subreport data.  Is there a way to do this?  the temp table that is bound to my subform is being queried by my sub reports to populate the data, but if i could look through all of the data on the subform to populate the subreports, that would work as well.

Thanks!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904557
If the button is only to post and the reporting is ancillary, then post first and report from the posted data rather than the pending data.  It seems that the report isn't being used to validate the posting ahead of time so stop tying the two together.  That was the point of the coupling/cohesion reference.  Your problem is the pathological coupling of the two events so separate them.
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904568
Thanks Pat:

I understand your point.  Can't really print the report after because the user may have made many modifications to the one form's data throughout the day and therefore we only need to see the data at that time; this would then entail more complexity to the code base (which is already extremely complicated).  Trust me, the preview was the first thing I suggested as the two events, as you've pointed out, are not truly related and does not rely upon one another, however the end users insist on it being set up this way.  I will go back to our business analyst and see if she can convince them otherwise, but so far the attempts have been futile.  Is there a way I could cycle through the data on the subforms to populate the subreports?  This seems to be the simplest solution (other than the preview button).

Thanks!
0
 
LVL 57
ID: 41904601
I would keep life simple and put the app to sleep until their done with the report as Crystal suggested.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904609
No.  Forms do not store data.  Tables store data.

When you choose to print a report that is open as a preview, Access reruns the query and that is what is failing because the data has already been removed from the table.  You might be able to get around the problem by leaving the old data but marking it as deleted.  Then your process when it starts, can delete the flagged data so it doesn't accumulate.  That would allow you to reprint at will until you run the process from the beginning again.

You might be able to add a button to the report to print.  That would give you the printing option when the report is opened in dialog view.  To activate the button, you would not be able to use print preview, you would need to use acViewReport rather than acViewPreview to open the report.  This activates events for controls but it doesn't do page breaks or run code so it still might not work for you.  You could still try the button and see what happens in printPrieview.  It might work.
0
 
LVL 18
ID: 41904614
thanks, Jim. I see I made a mistake in the WaitMilliSeconds code ... should be nNumMilliseconds in the code body too
Public Sub WaitMilliSeconds(nNumMilliseconds as long)
    'pause
    Sleep nNumMilliseconds
End Sub

Open in new window


> "acViewPreview"
there are several places that Report View does NOT run code -- there are very few places it does ...
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904636
Thanks all:

Pat:  I understand the form does not store the data, however I am populating the data on my report using unbound fields (labels) that get populated by what's on the form.  So my main report is getting the data directly from the form itself, while the sub reports are bound to the same table as my subforms.  So I was wondering if there was a way to populate the subform from the controls on the sun form.  My suspicion is there is not.

I have proposed the print preview again and explained the complexity of end users request.

Crystal:

I like the idea of the wait, however not sure if I can implement this by adding a new field in the table.

Thanks
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904697
No.  Forms do not store data.  Tables store data.  Unbound controls only exist while the form is open.  Bound control values persist only in the table.  They do not survive on the form once the data is deleted.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904713
Thanks again, Pat.  Are you saying no being able to populate the subreport from the subform?  You keep saying that, but are unclear as to the reference.  As I mentioned, I understand that the form does not persist the data, however the data is visible as long as the information is still on the form (and as I mentioned I am pulling the data off of the main part of the form).

Anyway thanks.  I am still waiting to see if the end users will accept the print preview.

Thanks again for all of your help.
0
 
LVL 18
ID: 41904739
forms are a convenient way to add and edit data STORED IN TABLES.

a form is just a way to look at data differently

analogy: you have someone on speed-dial so all you have to do is press a quick number.  That, however is not their phone number. The real phone number is looked up and dialed.

The RecordSource property of a form tells you where the data comes from. In the Design View of the form (or report), turn on Properties if they are not showing (Alt-Enter), click in the upper left where the rulers intersect to select the form (or report), and you will find RecordSource on the DATA tab. Perhaps the RecordSource is a query or SQL statement ... these, also, do not store data.

> "So I was wondering if there was a way to populate the subform from the controls on the sun form"

sun form --> sub form

yes, this is possible ... but why would you do that? It is so much easier to bind controls than loop and assign them.
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904798
Thanks Crystal:

Yes, that is all understood. Originally, I bound all of my report controls to the underlying TempTable that is bound to the form.  I changed this behavior on the main part of the report because I wanted to make sure the data persisted in the report, even after the data was deleted once updating the actual table used to store the data.  This was needed because when a user clicked on the post button, by the time the report was displayed and the user selected a  printer,  and clicked to print, data was gone.  This is working exactly as necessary and desired.  The other reason why I've gone this route is because there are several controls on the form that are not bound to a table, so the only way to receive this information on the report was to reference the form's controls collection to populate the necessary report controls.  My issue is with doing the same with the subforms on the form populating the subreports on the report.  Didn't quite know how to cycle through to pull all of the data collected on the subforms (which is viewed in datasheet view), so then decided to try to pause execution of my code why the report was still open.

I realize this has become quite complicated, so hopefully I'll be able to add the print preview; it was shot down before, but I'm awaiting a response from the business analyst.

Many thanks!
0
 
LVL 18
ID: 41904810
instead of temp tables:
1. why cannot you use queries on the actual data?  
2. Do you have the ability to modify structure and add fields?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904819
I'm assuming that the subforms are continuous or DS view.  If they were single record view there would be no reason at all to display them as subforms if all they ever showed was a single record.  I'll say it again.  That data IS NOT stored in the form.  It is stored in the table that the form is bound to.  Forms/subforms/reports/subreports that show multiple records are always bound.  They may be bound to a table or a query or to a recordset opened using ADO but the underlying data ALWAYS comes from a table.  Once you delete the data from the temp tables, IT IS GONE and that is that.

Forms with unbound controls can only contain a single instance of a the ControlSource regardless of how many rows are shown.  Prove this to yourself by creating a form showing an unbound control.  Open the form in continuous or DS view.  Enter a value in the field and notice that EVERY visible instance of the row shows the same value.  Change the value.  ALL the rows change.  Change the color of the text - all the rows change.  What you are seeing is that there is only a single instance of form properties regardless of how many rows are visible.  Certain properties can be impacted by the Continuous formatting property but the ControlSource isn't one of them.  So, the bottom line is that an unbound form/subform/report/subreport can only ever show the data for a single record.

Did you look into deleting the data "before" rather than "after" as you are doing currently.  Refer to one of my earlier suggestions.  Rather than copying the data to the permanent tables and deleting it from the temp tables, just update it in the temp tables to flag it for delete.  That will allow you to report on it until it is deleted.

When a form or report display data, you are not looking at the bytes as they exist on the hard drive, you are looking at a recordset stored in memory of data that was retrieved using the table/query specified as the RecordSource of the form/report.  So while the form/report is open in memory, the data exists in two places - on the drive and in memory - BUT ONLY WHAT IS VISIBLE - not the entire recordset.  Access actually updates the recordsets bound to forms so if you left the form open long enough, it would eventually show #deleted# rather than actual data.  Reports don't automatically refresh but if you cause them to paginate, you will get the #deleted# effect because Access goes back to the original query and tries to retrieve more information and it is gone.
0
 
LVL 18
ID: 41904838
is this also you?

https://www.experts-exchange.com/questions/28985920/Acess-2010-Pause-Until-Part-Of-Event-Completes.html

ask! Perhaps we can help with a better approach to solve what you need to do in a way you have not thought of.  Tell us more about what you need ... perhaps temp tables are not the best way
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904850
There are valid reasons for working with temp tables to build sets of data.  The key is understanding when you are truly done with the data and it is OK to delete it.  That seems to be the disconnect in this process.  The data is being deleted before the user is actually done with it.  That is why I move my delete code to either a weekly "cleanup" or to the start of a new transaction.  That leaves the data in the working tables until the user starts a new transaction.  It also makes it ever so much easier for debugging purposes.
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904865
Thanks for the info Pat. Again, I understand what you're saying: all I'm suggesting is I am referencing the form's controls to populate the report. Whether that data is in the form or bound to the database doesn't really help with my problem.  With all due respect, and you have been very helpful, I am not trying to figure out how the underlying architecture in Access is displaying the data on the form, where the info is stored, etc, I'm just trying to figure out if there is a way to reference the data visible on the form subforms from the report, which is displayed as a datasheet. and if not is there a way to pause the execution while the report is open.

Crystal: no that post was not mine.  This application was here way before me, so sadly the architecture, along with its  hundreds of forms, reports queries, modules (probably close to 500-600-700, not sure how many objects) was in place.  The temp table is there because prior to the store procedure kicking off and and inserting into our SQL Server, we're doing some other things with it.

Many thanks for all of the help provided.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904933
I've told you what I do to avoid the problem and made other suggestions also.  The short answer is don't delete the data until you are done with it.   Good luck.
0
 
LVL 14

Author Closing Comment

by:Juan Ocasio
ID: 41904956
Thanks again for your assistance.  The business analyst is allowing for the print preview to display to the users.
0
 
LVL 18
ID: 41904967
Juan ... or Bob ... you did not respond to "is this also you?" ~ if not, a thousand apologies. To really help, it helps to have more information.
0
 
LVL 14

Author Comment

by:Juan Ocasio
ID: 41904976
Hello Crystal:

I did respond respond above..  I am not Bob.  I looked at his question, and although it appears similar, it looks like he is doing something totally different.  While I tried to be as concise as possible, perhaps I didn't explain myself as clearly as I should've.  You did help me on an original post I had about this same report.  This is when I created two sub-reports but it was duplicating information (https://www.experts-exchange.com/questions/28984574/Report-not-displaying-all-of-the-data-from-2-sub-reports-in-Access-2010.html).

Pat has also helped me, yep on the same report, by telling me to create the sub reports instead of trying to separate the query on the same report with different criteria (https://www.experts-exchange.com/questions/28983796/How-to-create-multiple-groups-on-Access-report.html).

Thanks!

Juan
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

14 Experts available now in Live!

Get 1:1 Help Now