Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Execute code (A2K3)

    Guys,
    I have many reports that need to be reviewed prior to emailing. Once the user has reviewed the the report, I want a msgbox to ask the user if they are ready to email it.

    So, I have a list box of the reports for the users to select from with this code in the DoubleClick:

    PREVIEW REPORT CODE (Note: rptDept is a Public Variable):
    <hr>Private Sub lst_PH_Reports_DblClick(Cancel As Integer)
    Call RptType(1)
    End Sub
    Public Sub RptType(myDept As Integer)
    Dim RspAnswer As String, RspReturnAnswer As String
    Dim MyNote As String, myReturnNote As String
    MyNote = "Do you need to view email contacts first?"
    myReturnNote = "Send Email Now?"
    RspAnswer = MsgBox(MyNote, vbQuestion + vbYesNo, "Email Contact List")
    If RspAnswer = vbNo Then
    Select Case rptDept 'PH
    Case 1
    rptName = Me.lst_PH_Reports.Column(0)
    rptSubject = Me.lst_PH_Reports.Column(1)
    ShowReports lst_PH_Reports.Value, Forms!frm_ReportSelection (**this is a public function**)
    Case 2
    rptName = Me.lst_BH_Reports.Column(0)
    rptSubject = Me.lst_BH_Reports.Column(1)
    ShowReports lst_BH_Reports.Value, Forms!frm_ReportSelection (**this is a public function**)
    End Select
    Else
    DoCmd.OpenForm "frm_EmailContacts", acNormal, , , , acWindowNormal
    Exit Sub
    End If
    Call EmailIt (**this is a form level procedure**, I couldn't get it to work from a PUBLIC PROCEDURE due to a Runtime Error 2585)
    End Sub<hr>

    As you can see, the Select Statement is based on rptDept and calls the PUBLIC FUNCTION "ShowReports" which opens the report selected from the listbox.
    The problem is, once the report opens, the "CALL EMAIL" procedure runs. I only want it to run after the report is closed and the user has returned to the form. What do I need to accomplish this?

    CALL EMAIL CODE:
    [quote]Public Function EmailIt()
    Select Case rptDept
    Case 1
    sReportName = rptName
    sReportReason = rptSubject
    Call SendEmailNow1
    Case 2
    sReportName = rptName
    sReportReason = rptSubject
    Call SendEmailNow1
    End Select
    End Function
    Public Function SendEmailNow1()
    Dim RspReturnAnswer As String, myEmailRequest As String
    myEmailRequest = "Send Email Now?"
    ReturnAnswer = MsgBox(myEmailRequest, vbQuestion + vbYesNo, "Email Process")
    If ReturnAnswer = vbNo Then
    Exit Function
    Else
    msg = "....is attached for your review"
    sSubject = sReportReason & " Report"
    Set loDb = CurrentDb
    Set loRst = loDb.OpenRecordset("qContactEMails")
    'Collect the email recipients from the query "qContactEmails"
    'Each person who has a check mark next to their name will get the eMail
    With loRst
    Do Until .EOF
    em = .Fields("emailaddy") & ";" & em
    .MoveNext
    Loop
    End With
    'Prepare the eMail and Provide a link the
    'in the eMail with the file location
    DoCmd.SendObject acReport, sReportName, acFormatSNP, _
    To:=em, _
    Subject:=sSubject, _
    MessageText:=msg, _
    EditMessage:=False
    'Inform user the email is sent and process is complete
    'Give the user a chance to decide if they want to process another report or
    'return the report menu
    'Response = MsgBox("Email Sent! Send Another Report?", vbYesNo)
    loRst.Close
    'If user selected NO, then send them to the report menu and close this form
    If vbNo Then
    DoCmd.Close
    stDocName = "frmReportsMenu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
    End If
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Execute code (A2K3)

    I assume that your public function ShowReports uses DoCmd.OpenReport to show the report(s). You can specify that code will wait until the report has been closed by the user:

    DoCmd.OpenReport "rptMyReport", acViewPreview, , , acDialog

    Specifying acDialog as WindowMode argument makes the report act as a modal popup window. The user cannot click on other database object while the report is open, and code pauses.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute code (A2K3)

    Thanks Hans,

    Sorry for the delayed response, however your suggestion worked perfectly.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •