Solved

VBA code fails to pause when report is opened in acDialog window mode

Posted on 2010-09-23
27
731 Views
Last Modified: 2013-11-28
I've got a command button on a form that is supposed to open a report.  

Private sub cmdReport_Click

    DoCmd.CopyObject , "rpt_Temp", acReport, "rpt_dates"
    'run some code here that adds controls to the temp report based on the value of some controls in the form
    DoCmd.OpenReport "rpt_Temp", acViewPreview, , , acDialog
    if currentproject.allreports("rpt_Temp").isloaded then docmd.close acreport, "rpt_Temp"
    DoCmd.DeleteObject acReport, "rpt_Temp"

End Sub

But there is some code in that buttons Click that I don't want to fire until the report is closed, which will delete the report.  I was under the impression that using acDialog window mode would halt execution of the command buttons Click event code until the report was closed, as it does with a form, but that is not happening.  Instead, as soon as the report is open, the code continues to run, closing it and deleting the object.

As a workaround, I've removed the line of code that checks to see whether it is open, and if so, close it, and replaced that with the following.

    While CurrentProject.AllReports("rpt_Temp").IsLoaded: DoEvents: Wend

However, this is extremely CPU intensive (100%), and I would prefer to figure out why the code is not pausing as it does when a form is opened with acDialog.  I know I could move the code that deletes the temporary report from the button click event to the form_Close event, but again, would like to know whether this is a bug, or is a "feature".
AddingControlsToReportDuringExec.mdb
0
Comment
Question by:Dale Fye (Access MVP)
  • 9
  • 6
  • 5
  • +3
27 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 350 total points
ID: 33750508
It could be perhaps because a form has user interaction therefore it pauses whereas a report is just a view of data. Setting modal property (acDialog is like setting report to Modal and Popup yes) just ensures it stays on top of other windows


by the way, maybe fix your error handler code here

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    On Error GoTo ProcError
   
    Me.Box1.Width = Me.Duration / DateDiff("d", Me.dtStart, Me.dtEnd) * 6 * 1440
    Me.Box1.Left = 5760 + 6 * DateDiff("d", Me.dtStart, Me.AbsDate) / DateDiff("d", Me.dtStart, Me.dtEnd) * 1440
   
    Exit Sub
ProcError:
    If Err.Number = 2100 Then
        Me.Box1.Width = Me.Box1.Width - 1
        Resume
    Else
        MsgBox "a" & Err.Number & vbCrLf & Err.Number
    End If
   
End Sub

give you a clue, its prefixed with "a"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33750603
As Expert LSMConsulting always reminds us...
Making design view changes via VBA will decompile the application.
(Besides the fact that this code may not even run if you develop Runtime apps for clients/users)

So you had better be sure you are doing the following on a regular basis:
Compling the code
Running the Compact/Repair utility
Backing up the database

;-)

JeffCoachman
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 33750650
Jeff,

Good point.  Maybe I could copy the report to a temp.mdb file, make the modifications to it, and run it from there.

I'd have to modify the query that the report is based upon, or export that as a table into the temp mdb as well.  I'll have to take a look at that in the AM.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33750754
fyed!

Is that you?

I did not even notice (as I rarely look at member names)

Whew... good thing I was my normal charming self...
(Not "grumpy" Jeff)

;-)

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33750793
there's a grumpy jeff? I havent seen that emoticon from you :P

Not sure what fyed has decided then. By the last post I assume code will be left as is but will now deal with creating reports outside main database.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33751013
>:(
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33751044
lol
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 33774743
rocki/Jeff,

Neither of these responses answers the initial question of why the acDialog window mode does not function the way in a 2007 report as it does in a 2007 form, and I just confirmed (see attached) that it halts processing of the command button code in Access 2003 pending the report being closed.  Because neither of your responses resolves the issue, I'm going to request that this be elevated to a neglected question, to solicit other input.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 33774887
oops, forgot the attachment.

Interestingly, I just created a very simple 2003 mdb (in Access 2003), and it worked as expected.  When imported the form, report, and table into a clean 2007 accdb, it also worked as expected.  

So this may have more to do with Jeff's comment about decompiling the application than I originally thought.  I then added a msgbox line, similar to the one in these new attachments immediatly after the OpenReport method on the original form, and it still failed to pause execution of the click event code, despite the use of acDialog in the OpenReport method.
acDialogInOpenReportMethod-2003-.mdb
acDialogInOpenReportMethod-2007-.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33775892
<I'm going to request that this be elevated to a neglected question, to solicit other input.>
The more the merrier...
;-)

(sometimes if you want a specific Expert, you can check their last comment and post a comment there asking for their assistance, then post the link to this Q.)

Jeff
0
 
LVL 28

Expert Comment

by:omgang
ID: 33810541
As a workaround can you move the processes that you want to fire only after the report is closed to the report's Close event?
OM Gang
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33810929
Adding controls has to be done in Design view, so I would recommend making the report copy, opening it in Design view, adding the controls, then closing and saving it.  After that, you can open the report with the new controls and do what you want with it.

Also, it might be possible to create a temp table with the lines you need based on the selected dates, and use it as a subreport, instead of adding controls to the report in code.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 33811176
omgang & Helen,

I actually got this report working via a variety of methods, including moving the code that was supposed to firing to the close event of the form and a couple of other methods.

The point is why isn't the acDialog working to pause the code in the original file, when it works properly in the two samples that I posted on the 27th.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 28

Expert Comment

by:omgang
ID: 33811277
I have no idea.  The code works in Access 2003 and in a fresh 2007 Access app but not in your original app.  Haven't we all experienced circumstances of applications that for some unknown reason begin behaving in an unexpected manner?  Last year I posted up a Q re. an app I inherited that would crash every time ANY design change was made to a particular form.  The form had quite the multitude of controls and was a pain to recreate but that's what I ended up doing.  I never figured out what was causing the problem.  Very frustrating.

Have you performed a decompile and recompile on the app and then tested immediately after?  Jeff may very well be onto it.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 33811567
Looking at your process the only thing I wonder about is the function call to delete report lines issues an OpenReport command to open the rport in design view and delete lines.  Later, in the button click event of the form, another OpenReport command is issued to open the same report in preview.  I know this SHOUlD call the same report object that is already open and swith to preview mode.  Nothing else stands out.


A quick search on the web re. DoCmd.OpenReport turned up this article in which Allen Browne responded.
http://www.tech-archive.net/Archive/Access/microsoft.public.access.reports/2007-09/msg00052.html

One of his suggestions from the above post to attempt to recover a corrupt form is to export to a text file and then recreate from the text file.  Maybe worth giving it a shot.
Save as text / Load from text
http://allenbrowne.com/ser-47.html

OM Gang
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 150 total points
ID: 33812123
Hi Jeff,

you don't need to recompile or repair your database.

The problem is the code in "Detail_Format" in your rpt_Dates report. If you go through it with single step debugging you will see that it creates an overflow error and in the end a "control is too big" error. You should not assign an untested value to a control, first calculate it and then validate if it can be really assigned to the control (not only in this case, of course, you should always do that when changing object's properties which are not yours).
If you remove the code from this sub, your report opens as expected in dialogue mode.
The problem is that reports are handled different from forms, they are handled like a kind of "virtual printer". The code you apply to a form runs while line by line will be "printed". At this time you can of course manipulate things with VBA, but in very often cases you cannot interrupt this process or catch errors with a message box (better write errors into a property to read it out later or inside of a report global public variable). Programming reports is a lot harder than programming forms where you expect different events and user interactions. Reports can of course be made interactively, but the "printing" process to create the preview must be a very complicate thing inside of Access. I often run in problems when changing things with VBA in reports (as a simple example: You must use the Report_Open event instead of Report_Load to change record sources - in a form you would do that in Form_Load...).

You must be especially careful to use "/": It is very easy to get a 0 as result for the second part of this term so you must make sure that there will be no division by zero - for the calculation AND for the try to assign such a value to a report property.

Is it a bug? Yes and no...
No: If your code is clean, acDialog works.
Yes: It is really hard to find out such errors in a more complicate report, reports are really hard to test if they contain code. Often you need to find errors by disabling parts of your code.

Cheers,

Christian
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33814248
<Hi Jeff,>
?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33814314
That means hello Jeff

Christian, he is an MVP now so you have to address him as Jeffrey :)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33814320
I somehow missed fyed's post with it working in 2003. Hmmm. Let me look at that later today.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33814434
Sorry...I meant of course fyed, was late last night...:-)

Oh, in that case Sir Jeffrey?...;-)

Cheers,

Christian
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 350 total points
ID: 33814720
Ok, looking at this it does seem something to do with the detail_format. In my first post I mentioned the error handling. I corrected the error handling on that and ran it again. Picking two dates the same and you will get divide by zero errors.

So you need to handle that.

Then I commented oiut the whole code in detail format and your temp pause via while statement and that worked. Then I put it back in and it still worked.

Then I remembered what I did in your form code. And I think that was it because I had commented out DeleteMonthLines.

in your report click event

you create a new report then call the method to delete control lines. In that you open it up in design view but no where do I see you closing that.
I added the code to close   DoCmd.Close acReport, "rpt_Temp", acSaveNo
before I reopened it for preview and that seemed to make it go dialog

so perhaps the way you are opening and switching from design to preview without saving may have something to do with.

    DoCmd.CopyObject , "rpt_Temp", acReport, "rpt_dates"
   
    DeleteMonthLines "rpt_Temp"
    DoEvents
    CreateMonthLines "rpt_Temp", CDate(Me.txtFromDate), CDate(Me.txtToDate)
    DoCmd.Close acReport, "rpt_Temp", acSaveYes
   
    DoCmd.OpenReport "rpt_Temp", acViewPreview, , , acDialog
    MsgBox "d"
    DoCmd.Close acReport, "rpt_Temp", acSaveNo


The messagebox d comes up after I close the report


Next the detail format code. You need to be a little more defensive. No where do you check for possible divide by zero and the possibility that Me.Box1.Width is 0 (will fail in your error handler)

slight mod to detail_format - obviously you need to correct as appropriate

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim iDiff As Integer
   
    On Error GoTo ProcError

    iDiff = DateDiff("d", Me.dtStart, Me.dtEnd)
   
    Debug.Print "Duration", Me.Duration
    Debug.Print "DateDiff", iDiff
   
    If iDiff = 0 Then
        'Set defaults to what???
        Me.Box1.Width = 1
        Me.Box1.Left = 1
    Else
        Me.Box1.Width = Me.Duration / iDiff * 6 * 1440
        Me.Box1.Left = 5760 + 6 * DateDiff("d", Me.dtStart, Me.AbsDate) / iDiff * 1440
    End If
   

    Exit Sub
ProcError:
    If Err.Number = 2100 Then
        If Me.Box1.Width > 0 Then Me.Box1.Width = Me.Box1.Width - 1
        Resume Next
    Else
        Debug.Print "C:" & Err.Number & vbCrLf & Err.Description
    End If
   
End Sub



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33814800
Forgot to ask, why do you call DeleteMonthLines? I commented it out as I felt it wasnt needed and from that I came to the conclusion I just posted.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33815183
Ok, was out and thought popped into my head. How said eh.

Anyways I think it must have something to do with the way access opens the form. Perhaps you are switching views so that it ignores acDialog.because you are moving from design to normal.
Like with datasheet view which you cannot open in acDialog, its probably a similar feature.
But open that form when it is closed it will open as dialog.

Same principle with forms. Give it a try, open a form in design view then open in acDialog then add the msgbox and that msgbox appears.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 33818516
omgang,

The line to delete report lines was a remnant of an earlier version which was using the same report over and over again.  Eventually it ran into the limit of the number of controls that can be added to a report or form, so I had to abandon that idea.

Christian,  I'm taking a look at your suggestion.  Will get back to you.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33818577
fyed my apologies I missed some of the comments having revisiting this after a while. I didnt read all of Christians comment having seen Jeff's response first and funny comment response to that. My bad for not completing the read.

I went thru all the comments again. I knew there was a issue with detail format, I just highlited the correction required in the code so you would of pick up on that. Nevertheless, had I read the comments I would of realised that omgang has told you about the form opening and switching and Christian about division by zero. My last posts were in good faith without realising it was already posted.

So as a result, please ignore my comments as the issues you have with your code have already been highlighted before my investigations.

Teaches me to make sure I read the comments before wasting time on something already suggested lol.



0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 33818912
Thanks for all the help.  There were a couple of issues, the CreateMonthLines was generating an error which was not handled properly, and I ended up modifying the detail_format event to test the left + width properties of the box prior to setting them.  This meant that rather than handling the error, I was testing for the error first.  The combination of these changes has resolved the issue, and the acDialog is now doing what it should be.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
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…
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…

746 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

18 Experts available now in Live!

Get 1:1 Help Now