Results 1 to 2 of 2
  1. #1
    Rschoenb is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    9

    Prompt using form set start/end date using form also Save Report / email report

    Hi,

    I have the following code that works great to preview a report using a form to prompt for start/end date using cmd button. What I would also like to do is add a button to allow a user to save report as file and/or e-mail report. The code that I am using does not seem to allow this. :/ Can someone help me tweak what I already have to complete these tasks save to file and e-mail using two separate buttons? If I can't use the below code to complete these task's what are my best options.

    I would also like to use this same code to set the start/end date for queries if I can I am just not sure how to do it. Maybe I will need to post that question under queries.

    Code:
    Private Sub Command5_Click()
    'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
    'Purpose: Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note: Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

    'DO set the values in the next 3 lines.
    strReport = "Week1rpt" 'Put your report name in these quotes.
    strDateField = "[FollowUp1]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview 'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

    Exit_Handler:
    Exit Sub

    Err_Handler:
    If Err.Number <> 2501 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler

    End Sub


    Thanks for the help!

    Ruth

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The query can have criteria parameters that reference the input boxes on form.

    Why do you need to set criteria in query? The code opens a report with filter criteria using the WHERE CONDITION of OpenReport.

    You want to save the report to PDF?

    Review https://www.accessforums.net/import-...ges-25042.html

    For email examples review
    https://www.accessforums.net/program...ook-21903.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-05-2012, 12:22 PM
  2. Replies: 6
    Last Post: 12-12-2011, 09:57 PM
  3. Replies: 11
    Last Post: 12-08-2011, 05:31 PM
  4. Replies: 1
    Last Post: 07-07-2010, 04:22 PM
  5. Replies: 1
    Last Post: 05-01-2009, 07:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums