Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    44

    Unanswered: is there an easier way?

    At the end of every month a report will be going to each manager detailing their departments orders for that month. I have a summary report that lists all departments on one report but I need to make about 20 different reports (1 for each department) I did it the long and tedious way by creating 20 different queries using each department# as criteria, then created 20 reports based on those queries.

    My next step was to create a macro using 20 SendObject actions to email each manager their report.

    Here's the problem: if a report has no data, of course I don't want it sent out. The way I have it set now, is that if the report has no data, the macro is set to cancel.

    I'm sure there has to be an easier way to do this. I just can't figure it out.

  2. #2
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    This won't help you at the minute but this is sometyhin that I am experimenting with also. If i manage to do it I'll let you know.

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by bertthefreak
    This won't help you at the minute but this is sometyhin that I am experimenting with also. If i manage to do it I'll let you know.
    I didn't try it myself, but I was just thinking ...

    Suppose you have a form on which a button is placed. Clicking this button will start generating reports and send them

    Suppose you a table containing all the managers names and their email-addresses.

    To generate a report; the query is always the same except the department#.

    for example
    Code:
    Public Sub myButton_Click()
       Call GenerateReport
    End Sub
    
    
    Public sub GenerateReport ()
       Dim strSQL as String
       Dim rsManagers as New ADODB.RecordSet
       Dim departmentID as Byte
    
       strSQL = 'SELECT allManagers FROM yourTable'
       rsManagers.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
      Do While Not rsManagers.EOF
               departmentID = rsManagers.Fields(???)
               strSQL = 'SELECT whateverYouNeed FROM yourTable WHERE department# = departmentID'
               myReport.RecordSource = strSQL
               DoCmd.OpenReport myReport.Name, acViewPreview
    
               Call SendReport (myReport, rsManager.Fields(0))
               DoCmd.Close acReport, myReport.Name, acSaveNo
    
               rsManagers.MoveNext
       Loop
    
    End Sub
    
    
    
    Public Sub SendReport (myReport, recipient)
        Write a procedure to send the report to the recipient
    End Sub

    The syntax may not be correct or complete, but it might give you an idea.
    I hope this is of any help.
    Last edited by artemide; 03-10-04 at 08:46.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    artemide is correct. All you have to do is loop through a recorset of the mangaers or their ID's and have the report print/mail on each value as you loop through.
    Dale Houston, TX

  5. #5
    Join Date
    Mar 2004
    Posts
    44
    Thanks! I'll give it a shot

  6. #6
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    I managed to get this working but it seem a bit tempramental I think i'll need to tweak it, but it did generate nine individual reports.

    Dim i As Long
    reporttype = "RptClassSummaryRegionArea"
    strwhere6 = strwhere

    tmebegin = Now()

    For i = 0 To (CboRegion.ListCount - 1)
    IntRegion = CboRegion.ItemData(i)
    strselect = "tblreview.IntReviewCaseID, tblClassification.StrClassification, tblRegion.intRegion, tblRegion.StrRegion, tblArea.StrArea, tblreview.DateInput, tblcase.DistributionChannel"
    strfrom = " (tblRegion INNER JOIN (tblArea INNER JOIN tblsortcode_branch ON tblArea.IntArea = tblsortcode_branch.IntArea) ON tblRegion.IntRegion = tblsortcode_branch.IntRegion) INNER JOIN (tblClassification INNER JOIN (tblcase INNER JOIN tblreview ON tblcase.IntCase = tblreview.IntReviewCaseID) ON tblClassification.IntClassification = tblreview.IntClassification) ON tblsortcode_branch.BranchID = tblcase.BranchID"
    StrRegion = DLookup("strregion", "tblregion", "tblregion.intregion = " & IntRegion)
    strwhere = "tblregion.intregion = " & IntRegion & " and " & strwhere6

    Debug.Print strwhere

    strsql = "select " & strselect & " from " & strfrom & " where " & strwhere & ";"

    qd.SQL = strsql

    stDocName = reporttype
    DoCmd.OpenReport stDocName, acViewDesign
    Reports!RptClassSummaryRegionArea.lblinput.Caption = strinput
    Reports!RptClassSummaryRegionArea.lblRegion.Captio n = StrRegion
    Reports!RptClassSummaryRegionArea.LblDistChannel.C aption = strdistribution
    DoCmd.OutputTo acReport, stDocName, acFormatRTF, "C:\Temp\" & stDocName & " - " & StrRegion & ".rtf"
    Next i

    hopefully this will give you something to start with.

  7. #7
    Join Date
    Mar 2004
    Posts
    44
    Thanks for your help. This thing is kicking my butt.

  8. #8
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87
    Where are you getting the list of names from? is it a table or combobox?

  9. #9
    Join Date
    Mar 2004
    Posts
    44
    it's a combo box that is taking info from a table called Associates. I've given field names of unit1, unit2, unit3, etc., depending on how many departments each associate covers.

Posting Permissions

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