Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Unanswered: export 1 report to multiple files

    I don't know if this can be done or not.
    I have a report that is filtered by certain criteria.

    One of the criteria is customer number. Instead creating a new report for
    each customer can I program Access just to loop through the customer file
    and set the criteria of the report dynamically? This is what I have so far.
    Any help would be greatly appreciated.



    Public CustomerID, Filename As Variant 'setting up necessary global variables
    ' Usage: PrintAllCustomers("Reportnamehere")
    Public Sub PrintAllCustomers(ReportName As String)
    Dim ADOCon As ADODB.Connection
    Dim Rst As ADODB.Recordset
    Dim Folder As String ' to hold directory information

    'now lets ask directory you want to save
    Folder = InputBox("Where do you want to save files?", "Destination Folder", Application.CurrentProject.Path)
    'if canceled leave the sub without processing.
    If Folder = "" Then Exit Sub

    Set ADOCon = Application.CurrentProject.Connection 'get current connection
    Set Rst = New ADODB.Recordset 'to query current customers
    'open recordset and load all data we need into recordset
    'I added "WHERE Print=True" to SQL statement which will
    'only print the customers we want
    Rst.Open "SELECT Business.BusinessName, ImportAddresses.Name, ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State, ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber FROM (Business INNER JOIN Zips ON Business.CustomerNumber=Zips.CustomerNumber) INNER JOIN ImportAddresses ON Zips.ZipCode=ImportAddresses.Zip GROUP BY Business.BusinessName, ImportAddresses.Name, ImportAddresses.Add1, ImportAddresses.City, ImportAddresses.State, ImportAddresses.Zip, Zips.Type, Zips.CustomerNumber, ImportAddresses.Type HAVING (((ImportAddresses.Type)=Zips.Type)) ORDER BY Business.BusinessName, ImportAddresses.Name", ADOCon, adOpenForwardOnly

    Do While Not Rst.EOF 'start sending all reports to files
    CustomerID = Rst!CustomerNumber 'set necessary global variables before print to file
    Filename = Folder & "\" & Rst!BusinessName & ".SNP" 'set folder and filename.
    'send report to file
    DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP, Filename, True
    Rst.MoveNext 'next customer
    Loop
    Rst.Close
    Set Rst = Nothing
    End Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    just an idea, I never tried this; but if you could set up a global variant variable and set it to customerid and use it in your report as filter. which I see in your code snippet you do it. is it not working?
    ghozy.

  3. #3
    Join Date
    Jun 2004
    Posts
    15

    Global variable in report

    I do not know how to code within an Access report filter the Global variable.
    This is something that I would imagine can be done but I have never done it either.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you could write a public functrion that returns this global variable and then you could use this function in your report query I guess.
    ghozy.

Posting Permissions

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