Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Capture criteria from query (2003 sp2)

    The qryEric has the following criteria: [What Date?]

    So when I preview this report it asks for a date. I am now taking this a step further and wanting to send this repot via email in Excel format. What I am wanting to know is how to "capture" the date, so I can add the email functionality to a button.

    <pre>Private Sub cmdSndEmail_Click()
    DoCmd.SendObject acReport, "rptExcDailyRpt2", acFormatXLS, "jlowry@lhlacy.com", , _
    , , , False
    End Sub</pre>


    How can I use the Date the report is based on the populate the Subject line in the above code?

    thanks,
    jackal

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

    Re: Capture criteria from query (2003 sp2)

    Put a text box txtDate on the form.
    Set its Format property to one of the date formats.
    Replace the parameter in the query with

    [Forms]![NameOfYourForm]![txtDate]

    Make sure you open the report from the form only.
    Use

    DoCmd.SendObject acReport, "rptExcDailyRpt2", acFormatXLS, "jlowry@lhlacy.com", , , "Report for " & Me.txtDate, , False

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> You haven't provided any feedback to the replies you received to <post:=624,545>post 624,545</post:> and <post:=626,577>post 626,577</post:>, so others reading those threads don't know whether the replies were useful.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Capture criteria from query (2003 sp2)

    Thanks. Here is what I have so far.
    On my frmStartUp, there is a button "Send Daily Report", this opens up "frmMyDate". This form contains a text box (txtDate) with label ("What Date?") and button (cmdSendRpt) to send the email.

    <pre>Private Sub cmdSendRpt_Click()
    DoCmd.SendObject acReport, "rptExcDailyRpt2", acFormatXLS, "jlowry@lhlacy.com", , _
    , "Excavation Daily Report for " & Me.txtDate, "Here is the Excavation Daily Report _
    for " & Me.txtDate, False

    Me.txtDate = ""
    End Sub</pre>



    How would I close this form from within the code or should I just put a close button?

    thanks,
    jackal

    p.s. As far as the other posts, the first one I have just finished and will post what I have and the second one I am still knee deep in, but will post as soon as I can.

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

    Re: Capture criteria from query (2003 sp2)

    You can add a line

    DoCmd.Close acForm, Me.Name

    just above the End Sub. This will close the form. If you prefer to give the user another chance to send the report, add a separate close button that executes this code.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Capture criteria from query (2003 sp2)

    Thanks you sir.
    That is exactly what I was looking for.

    thanks,
    jackal

Posting Permissions

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