Popup Form to Print or Email Report

[ramez75]

Hi,

I didnt know under what section to post my question but since it involved report, form and vba I thought I post it under GENERAL, so apologize if this is not the appropriate place.

This what I am trying to do.
I have an Access Database designed using Switchboard to open different forms, reports and menu's etcc. All toobars are hid when the Database is opened. I would like to have a form (already created) that will open up when any report is opened within the database and the form will have 3 buttons Cancel, Print and Email. I already created the form with the buttons and added it to the Open event of each report within the database as shown below

Private Sub Report_Open(Cancel As Integer)
    If Not IsLoaded("frmSwitchboard") Then
        MsgBox "Open this report using the Switchboard Form, Preview Report Section."
        Cancel = True
    End If
DoCmd.Close acForm, "frmSwitchboard"
DoCmd.OpenForm "frmPrintEmailPopup"
End Sub

Ofcourse when I click Cancel I revert to the Switchboard Main Menu (PERFECT)

I cant figure out how to make the PRINT and EMAIL buttons code to work.

An example if I have REPORT XYZ opened from the Switchboard Menu

Forms!frmSwitchboard.Filter = "[ItemNumber] = 1 " & "And [SwitchboardID] = 5"
    DoCmd.OpenReport "ReportXYZ", acViewNormal

I want frmPrintEmailPopup form to pop open up and if the individual want to PRINT or EMAIL the report then they can click on one of the buttons on frmPrintEmailPopup form, then once printed or emailed the REPORT XYZ is closed and frmPrintEmailPopup form is also closed and reverted to the Switchboard Main Menu.

Any ideas help will be greatly appreciated

Thank you

RB

 

[MaZeWorX]

why not use a tool bar ?? assigned to the report

 

[Niroth]

To print the report when it's opened it's:

docmd.openreport "ReportXYZ"

acNormal allows you to view it as per normal, acPreview lets you see Print Preview etc

Have no idea what the code is for email, searching for it too. Any suggestion, helpful people?

 

[ramez75]

OK I googled around and did some experimenting and this what I have so far, of course I still cant get the Print and Email button to work because I am misssing something but cant figure it out

First I declared a Global variable in the frmPrintEmailPopup Form, the reason I did that coz I have around 10 reports and wanted the form to be able to know what to Print and Email so I used the below code

Option Compare Database  
Option Explicit  
 
Dim ReportName As String
 
Private Sub Print_Click()
    On Error GoTo Err_Print_Click
 
    DoCmd.SelectObject acReport, ReportName, False
    DoCmd.PrintOut
 
Exit_Print_Click:
    Exit Sub
Err_Print_Click:
    Resume Exit_Print_Click
End Sub
 
Private Sub Email_Click()
On Error GoTo Err_Email_Click
Dim stText As String        '-- E-mail text
Dim stSubject As String     '-- Subject line of e-mail
stSubject = " BLA BLA BLA"
stText = "More BLA BLA BLA"
        DoCmd.SendObject acSendReport, ReportName, "Snapshot Format", , , , stSubject, stText
Exit_Email_Click:
   Exit Sub
Err_Email_Click:
   Resume Exit_Email_Click
End Sub

So the above is the code I drafted the problem now how do I get the 'ReportName' Somehow I think that I need to hardcode the something into each Report in order when the Report let say for sake of simplicity a user opens REPORT XYZ then I want ReportName = REPORTXYZ which then will feed into the frmPrintEmailPopup since I declared ReportName as Global variable.

Let me know if anyone have any idea how I can resolve this I the mean time I will keep experimenting

Thank you

RB

 

[Niroth]

Seems to me you're giving yourself a hard time for nothing.

First of all, I think you haven't specify the report correctly. If you have ten reports to choose from, maybe If function, or select case, would be more appropriate. So if there's something in the pop up form itself, or another form running in the background, that you can use to determine what report to call, use that in your code.

So let say there's 5 reports, r1, r2, r3, r4, r5

and "backgroundForm", the form from which you call "popupForm", contains some information that will determine which report get called up for printing or emailing, say customerID.

code should go something like this:

private sub print_click()

if forms!backgroundForm!customerID = 1 to 10 then
docmd.close acform, "popupForm"
docmd.openreport "r1"

elseif forms!backgroundForm!customerID=11 to 20 then
docmd.close acform, "popupForm"
docmd.openreport "r2"

...etc

end if
end sub

substitute your code for emailing into the docmd.openreport part.

maybe I wasn't clear before, when you use docmd.openreport "ReportXYZ", without any specification as to what kind of view you want, access simply print the report, so you don't have to use any object.printout function.

 

[ramez75]

Niroth thanks for the quick response.

I have a database designed using Switchboard....So one of the buttons on the switchboard called "Preview Report".
So if a user click on Preview Report Button on the Main Switchboard Menu the Preview Report Menu will open. Below is the Setup in the Switchboard Table. Ofcourse some reports require Date Range to be created well all that is fine so far.

Switchboard ItemsSwitchboardIDItemNumberItemTextCommandArgument50Preview Reports Switchboard051Preview XYZ4rptXYZ52Preview A4rptA53Preview B4rptB54Preview C4rptC55Preview D4rptD56Preview E4rptE57Preview F4rptF58Return to Main Switchboard11

So lets saw now the user click on Preview XYZ button (No Date Range Required for this report generation). Report will open along with frmPrintEmailPopup Form (POPUP=YES), that way I give the user the option to Print or Email if needed, if not needed there is a cancel button which then will close Report XYZ (in our example) and return to the Preview Report Switchboard Menu. Hence, I made the popup form open once the report opens. I added the below to each Report ONCLICK OPEN Event

DoCmd.OpenForm "frmPrintEmailPopup"

So the reason I couldnt or atleast didnt figure out how to Use IF/Case condition is how will the Print or Email commands now which form is open. At the point when report is opened all I have open is the POPUP form beside it thats why I was reverting to a Global Variable "REPORTNAME". So how can I implement your idea to my situation.

Sorry for the long note just trying to be as clear and elaborate as possible

Thank you again

 

[MaZeWorX]

again... why not use access Tool Bars to email/print/cancel the report .. you can customize the menu and have it open with the report. The menu option in access for emailing when clicked will cause a pop up to appear and give your users the option of sending the report in several formats ... excel, rtf, word, snap shot. Why re invent the wheel? when access provides a solution for what you want to do already

 

[Niroth]

Whatever criteria you used to open the report from the switch board can do the same, but here's an easier way:

1. put in a textbox into the pop up form, name it "reportName", and make it invisible.

2. add these lines into each report's on open event:

DoCmd.OpenForm "frmPrintEmailPopup"
forms!frmPrintEmailPopup!reportName=me.report.name

then in the pop up form's print button use this line:

docmd.openreport me.reportName

viola. That should do it.

 

[ramez75]

Niroth

Thanks for the info it worked thats exactly what i needed and will use the same concept for emailing too in this case i will use something along the below lines under the email button.

DoCmd.SendObject acSendReport, Me.reportName, "Snapshot Format", , , , stSubject, stText

Invisible textbox cool. I was trying to build case conditions was driving me crazy. Thanks again

MaZeWorX, the reason I didnt want to use toolbars is atleast I dont know how yet but if you right click the mouse you can add item to the toolbar and i didnt want the user to have any accessible way to add anything and i cant lock the feature. Also I open the Switchboard maximized with nothing but the switchboard.
Just for learning purposes can you lock the mouse right click and how would you add the Email/Print to the report.

The way I was thinking, is create two macros. Htoolbar and Stoolbar and configured the Actions to Email and Print buttons and they show and hide only when a report is open

 

[ramez75]

DoCmd.SendObject acSendreport, Me.reportName

worked perfectly as I want. So one popup form will allow users to print and email with minimum coding.

Thanks Niroth

 

[MaZeWorX]

Yes you can restrict users from right clicking the toolbars to add to them look at the Database startup options also so you know for the future you can add custom toolbars to reports and forms .. a diff one for each if you desired. You build your toolbar by customizing then save it. Then in the forms properties you can assign the toolbar from a drop down list of saved tool bars.

HTH