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

    Unanswered: 1 Report format exported multiple times using different variables as name

    Good Evening. I am attempting to export a report to a *.SNP
    format. That is not the tricky part though. The tricky part is that
    the report must be named Filename = Folder & "\" & Rst!BusinessName & ".SNP" this is still not the tricky part. The tricky part is that the report must filter all but the specific information about the specific company it is being run on. Then the report must be run on all the customers in the customer table. The code I am working with is listed below:

    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, False
    Rst.MoveNext 'next customer
    Loop
    Rst.Close
    Set Rst = Nothing
    End Sub

    Any help you could provide would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Filter the dataset before executing the report.

    Code:
     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.
       Rst.filter = "CustomerNumber = " & CustomerID
       DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP, Filename, False
       Rst.MoveNext 'next customer
    Loop
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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