Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email multiple reports together (2K)

    Hi,
    Quick and hopefully easy question here.
    I have three reports, for this purpose named 1,2 & 3.
    I currently have a link in the Main Form to a Report Form that allows the user to preview the reports (1,2 & 3) and then using a customized toolbar for reports, email them seperately. What I would like to know is whether or not I can have a control in the Form (FrmReports) that will email all of the reports at once instead of creating a central report which amalgamates the others.
    Any suggestions would be welcome.

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

    Re: Email multiple reports together (2K)

    One option is to create an unbound report (i.e. no record source), and place the reports 1, 2 and 3 on it as subreports. You can then use the standard tools to send the report (containing 1, 2 and 3.)

    An alternative would be to export the three reports to separate files, then use Automation to create an e-mail with the three exported files as attachments, and send it. This is a lot more work.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email multiple reports together (2K)

    Thanks Hans,
    Don't know why I didnt think of that!
    I do have another query regarding archiving entries on other databases.
    This is the coding (edited) that was in a previous post relating to a different database:

    Private Sub cmdArchive_Click()
    Dim strSQL As String

    On Error GoTo ErrHandler

    If MsgBox("You are about to archive records. Have the reports been done?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If

    strSQL = "UPDATE tblMain SET Archive = True WHERE Archive = False And Actual Is Not Null And DateFull Is Not Null"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    I understand that the coding is checking parameters to ascertain as to whether the record returns specific values. How would I go about amending this coding to only count records the relate to the previous month. The field that would be focused on for this would be DateOut in TblMain. I tried to add 'And DateOut=DateSerial(Year(Date()),Month(Date())-1,1)' but this didn't work. Any ideas?

    Also on a similar note, with reports, within a Text Box, where you want to display the previous month, how would you go about doing this? Currently its '=now()-31' which I'm not overly happy with due to its unreliability (i.e February 28 days) . I've tried =DateSerial(Year(Date()),Month(Date())-1,1) but this requests Parameter fields when the report is opened, i.e. Enter Parameter Field, Date.

    Sorry for yet more questions!

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

    Re: Email multiple reports together (2K)

    1. Try

    ... And DateSerial(Year([DateOut]),Month([DateOut]),1)=DateSerial(Year(Date()),Month(Date())-1,1)

    2. The expression

    =DateSerial(Year(Date()),Month(Date())-1,1)

    should work, I don't understand why it would cause a parameter prompt. One thing you can check is if you have missing VBA references, although it seems improbable:

    - Activate the Visual Basic Editor (Alt+F11)
    - Select Tools | References...
    - If you see a reference starting with MISSING, clear its check box.
    - Click OK.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email multiple reports together (2K)

    Hi Hans,
    Still the same problem. On reflection I presume that this is because the databases are FE & BE, and its actually the table in the BE database that I need to archive. This is the coding that I now have:
    Private Sub cmdArchive_Click()
    Dim strSQL As String

    On Error GoTo ErrHandler

    If MsgBox("You are about to archive records. Have the reports been done?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If

    strSQL = "UPDATE tblMain SET Archive = True WHERE Archive = False And DateOut Is Not Null And DateSerial(Year([DateOut]),Month([DateOut]),1)=DateSerial(Year(Date()),Month(Date())-1,1)"
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    How would I alter this to have an effect on the BE databases? As I understand it, these are the one that contain the actual data, with forms, queries, etc in the FE databases.

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

    Re: Email multiple reports together (2K)

    I assume that you have put a link to tblMain in the FE. If so, executing the query in the FE will actually update the table in the BE.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email multiple reports together (2K)

    Yep, the FE has a link to the TblMain in the BE. I'll keep perservering with this.
    Thanks for your help

Posting Permissions

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