Printing multiple reports with one button

[ahuvas]

Ive have seen a number of threads on this but I cant seem to get my button going.

Basically the structure of my forms/database is that there is a switchboard on which there are two buttons, ones to a form called "Data Entry View" each of which has a button for each form that when clicked opens a pop up f(filter) orm where a person can enter a number which will filter the form for me when a button is clicked on this filter form.

THe other button on the switchboard is a "Report View" that opens an identical form (to Data Entry View) only in this case the filter form causes filtering of a report (there is one report for every form) not a form.

So farthis system works very well with me. However I want to include a single button on the "Report View" form that will allow me to print off all the available (6) reports for a particular ID.

I thought I could extend the code that I used for the Filter report form but it didnt work:


Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantDetails", _
"ParticipantID = Forms!frmOpenRpt!txtSearch") > 0 Then

DoCmd.OpenReport "rpt1", acPreview, , _
"ParticipantID = Forms!frmOpenRpt!txtSearch"


DoCmd.OpenReport "rpt2", acPreview, , _
"ParticipantID = Forms!frmOpenRpt!txtSearch"

DoCmd.OpenReport "rpt3", acPreview, , _
"ParticipantID = Forms!frmOpenRpt!txtSearch"

DoCmd.Close acForm, "frmOpenRpt"

Else
MsgBox "No records found - Please check your Participant ID "

End If

End Sub


...and so on...


Anyone have any ideas?

 

[Brianwarnock]

What actually happened, it didn't work doesn't give us much help.

Brian

 

[ahuvas]

I apologize for being so vague.

Okay here is my problem.

When I set a single button on form to use the entry in a text box on that fomr to OPEN and filter (multiple) reports it works fine - as so:

 Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantDetails", _
"ParticipantID = Forms!frmPrintAllReports!txtSearch") > 0 Then

DoCmd.OpenReport "rptAthensQuestionnaire", acPreviewl, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

DoCmd.OpenReport "rptEdinburghQuestionnaire", acPreview, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"
       
        DoCmd.Close acForm, "frmPrintAllReports"

  Else
    MsgBox "No records found - Please check your Participant ID  "

  End If
  
End Sub

If I use this code to filter and PRINT a SINGLE report it also works fine (although the docmd.close PrintAllReports form does not work).

 Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantDetails", _
"ParticipantID = Forms!frmPrintAllReports!txtSearch") > 0 Then

DoCmd.OpenReport "rptAthensQuestionnaire", acViewNormal, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"
     
        DoCmd.Close acForm, "frmPrintAllReports"

  Else
    MsgBox "No records found - Please check your Participant ID  "

  End If
  
End Sub

When I try to use this code to filter and print SEVERAL DIFFERENT REPORTS using the user's entry into the text box it will only filter and print the FIRST report and does not print the other report(s) - I really have six I am just showing you the first 2.

 Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantDetails", _
"ParticipantID = Forms!frmPrintAllReports!txtSearch") > 0 Then

DoCmd.OpenReport "rptAthensQuestionnaire", AcView, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

DoCmd.OpenReport "rptEdinburghQuestionnaire", AcView, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"
       
DoCmd.Close acForm, "frmPrintAllReports"

  Else
    MsgBox "No records found - Please check your Participant ID  "

  End If
  
End Sub

I wouldnt have thought changing acPreview for acViewNormal would make sure a big difference. It an filter, open and print preview multiple reports, it just cant filter, "open", and print multiple reports.

 

[Moniker]

Does closing each report after opening each report solve it, as in:

DoCmd.OpenReport "rptAthensQuestionnaire", AcViewNormal, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

DoCmd.Close acReport,"rptAthensQuestionnaire"

DoCmd.OpenReport "rptEdinburghQuestionnaire", AcViewNormal, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

DoCmd.Close acReport,"rptEdinburghQuestionnaire"

And so on.

Also, have you stepped through the code to see what it's doing exactly?

 

[ahuvas]

Im not sure what you mean by stepped through the code but when I went into the VBA editor and clicked on step into etc.. nothing happens, when I compile the code nothing happens either. The code doesnt seem to be breaking at any point it just isnt doing what I want it to.

 

[Moniker]

Set a breakpoint in the code (click in the left margin and it will highlight the row in a maroon color). In your case, set your breakpoint on the line that starts If DCount(.... Now, when the code reaches that line, it will pause and bring up the code window. Press F8 it the code will move line-by-line, highlighting the row it's on. This is an easier way to see where the code is bombing.

And, did you try putting the Close acReport commands in there?

 

[ahuvas]

I KNOW What the problem is now. Just not what to do!

I realised the difference between the dummy database and my database is that on the close report event I have the text:

Private Sub Report_Close()
Forms!frmReportView.Visible = True
End Sub


I have this so when the research assistant closes the report the main menu for viewing reports individually pops back up. However when I took this text away the print all reports form works fine.

Anyway to get around this? I have attached a picture that outlines the structure of my forms/reports and close/open/visible commands.

 

[ahuvas]

I got this code to wrok. Is there a more elegant way of doing it?

Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantDetails", _
"ParticipantID = Forms!frmPrintAllReports!txtSearch") > 0 Then

DoCmd.OpenReport "rptAthensQuestionnaire", acPreview, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

Reports!rptAthensQuestionnaire.Visible = False

DoCmd.SelectObject acReport, "rptAthensQuestionnaire", True

DoCmd.PrintOut acPrintAll

DoCmd.OpenReport "rptClevelandQuestionnaire", acPreview, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

Reports!rptClevelandQuestionnaire.Visible = False

DoCmd.SelectObject acReport, "rptClevelandQuestionnaire", True

DoCmd.PrintOut acPrintAll

DoCmd.Close acForm, "frmPrintAllReports"

DoCmd.Close acReport, "rptAthensQuestionnaire"

DoCmd.Close acReport, "rptClevelandQuestionnaire"
    
  Else
    MsgBox "No records found - Please check your Participant ID  "

End If

End Sub

 

[Brianwarnock]

Your problem is that when printing all reports frmReportView is not used and is not open, so you need to be able in report close to check where you have come from, or maybe in the print all code open and hide the form.

Brian

 

[Brianwarnock]

On second thoughts just test to see if the form is open. if there isn't a built in option to do this, and I don't know the answer to that, then on form open set a Public variable equal to open, and on form close set it to close and then test that.

Brian

 

[ahuvas]

Your problem is that when printing all reports frmReportView is not used and is not open, so you need to be able in report close to check where you have come from, or maybe in the print all code open and hide the form.

Brian

The Form frmReportView is ALWAYS OPEN (just not always visible - see my diagram) because this is where the PrintAllReports cmd button is. It normally becomes invisible when you click to open any ofthe single reports so you can view them (which causes a popup form to open so I can filter the report) and becomes visible again either when I click cancel on the pop-up form or when I close the report once I am finished with it.

In this case though clicking on the "Print All Reports" you cause the six different reports to open,print and close. only each time it tries to make visible the form frmReportView (as it normally does) only after the first report opens and prints it stops the next 5 from opening and printing. In my new code above, I cause the report to open (visible=false did not cause it to become invisible??? for some reason) set focus, print and then open the next form and so on, and then close all the open reports so that the making visible of "frmReportView" does not occur until the end so it doesnt interrupt any of the processes.

Also I noticed that using the code above causes the hidden database window to appear - I am not sure why. Opening and closing individual reports do not cause the database window to open - only something in the above code causes it to open. BLuch. One problem is replaced with anotehr.

http://www.access-programmers.co.uk/forums/showthread.php?t=75420&highlight=hidden+database+window <-- I am not the only one with this problem - I wish they posted their solution.

 

[Brianwarnock]

In the Db you posted there was a separate form for printing all reports, and when I tried to open frmReportView it failed on a "sub or function not defined" with Keycode highlighted in the debugger.

When I tested my theory as best I could it all worked fine.

Brian

 

[Rich]

DoCmd.OpenReport "rptAthensQuestionnaire", acNormal, , _
"ParticipantID = Forms!frmPrintAllReports!txtSearch"

 

[ahuvas]

Sorry I was a little overzealous with deleting forms etc to get it under max size. I left everything as is and just uploaded it to a free hosting site.

Here it is:
http://www.filesend.net/download.php?f=aeecd16b129712d38ccfd2cb06a35fc7

I am not sure what you mean by testing to see if the form is open? or why this helps? The forms is always open just not always visible.

I found the only way to get around the suddenly appearing database window was to do:
DoCmd.RunCommand acCmdAppMinimize on form load of the switchboard manager.

Rich I cannot use AcNormal because it automatically opens the report prints it and the closes it - this cases the form frmReportView to become visible and stops the other reports from opening and printing.

 

[Brianwarnock]

I down loaded , but I can only see your switchboard and code , by crashing the application., I need a password to get further, but I see that you do have a command button to fire off the print all scenario.

What I am suggesting, but cannot test, is that as it is the report close executing the Forms!frmReportView!visible = True that is causing the problem, you need to bypass that. try this
Create a Module called Globals
Public pIsThisFromPrintAll as string

In the code where you print all

pIsThisFromPrintAll ="YES"
at the start
pIsThisFromPrintAll = ""

at the end

In your close reports events

If pIsThisFromPrintAll = "" Then
Forms!etc
endif

pIsThisFromPrintAll will be equal to "" initially.

Brian

 

[ahuvas]

Is there no text in the module?

 

[Brianwarnock]

If you mean the Global module , no in this case just the one line.
You could declare your public variables at the start of any module but I liked to keep my ine in a separate module along with any global routines.

Brian

PS got your message.

 

[ahuvas]

Okay I think it worked. Let me try a bit more..

 

[ahuvas]

Yehaaww! It seems to work well - but better than my ploddy round about the way version Thank-you

haha see my next problem under VBA coding.. splitting my database causes errors with the password module. Doh!

It seems like I have been spending an awful lot of time on here lately but I now know something new which means that I can do it myself next time.

 

[Brianwarnock]

I got a run time overflow in the function handedness on the Edinburgh report Athens and Cleveland ok.

Brian

Sorry have to go now, but my theory works ,.