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

    Question Unanswered: export 1 report to multiple files

    I have a report that I need to export on a monthly basis. this report need to be done for every one of my clients. Until now I have just copied and pasted a new report with a different criteria for customer number. Is there any way to automate this process such as print this report until EOR reached for customer number?


  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can print all customers in one report seperating customers with page breaks.
    ghozy.

  3. #3
    Join Date
    Jun 2004
    Posts
    15

    Post Multiple exported files

    I know that I can just have separate customers divided by page breaks but what I need to do is to have separate reports because I have written an ASP application to have each and every customer be able to download their own billing. If I have 1 big file I would have to separate them out. Another issue is that the reports have to be in SNP format. Say each customer has their own unique name. I need to be able to export a file in SNP format that is named with the customer name and be only that customers information. right now I have a query desinged and a report format developed all I do is copy/paste and then change the sql criteria to pull up the new customer. Then I add another line onto my macro of OutpuTos. This is tedious and untenable as I will be adding many many more customers.

    Any help would be greatly appreciated.

    Best Regards

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    first stage:
    Create an Public Variable (long or variant) called CustomerID and Filename in a Module and create a query that returns results using CustomerID variable as criteria. and use this query for your report (or edit your current report query to use this variable and show only the customer pointed in CustomerID variable). so we will be able change this variable and it will change report's outcome.

    second stage:
    here is the module with global variables and Sub we need.
    you should change:
    CustID, CustName, CustTable fields the ones with in your table.


    Code:
     
    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
    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
    	Rst.Open "SELECT CustID, CustName FROM CustTable", ADOCon, adOpenForwardOnly
    	Do While Not Rst.EOF	'start sending all reports to files
    		CustomerID = Rst!CustID 'set necessary global variables before print to file
    		Filename = Rst!CustName & ".SNP"
    		'send report to file
    		DoCmd.OutputTo acOutputReport, ReportName, acFormatSNP, Filename, False
    		Rst.MoveNext	'next customer
    	Loop
    	Rst.Close
    	Set Rst = Nothing
    End Sub
    I couldn't test this code because I dont have the proper tables. hopefuly it will work. you better have a backup your files before going for this
    Last edited by ghozy; 06-09-04 at 18:15.
    ghozy.

  5. #5
    Join Date
    Jun 2004
    Posts
    15

    Code looks like it will work - One quick question

    I have plenty of experience calling code from a button through a private sub.
    How would I call this public routine?

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    insert a new module and type this code in (or paste ). then in your button code you can use this sub like:
    Code:
     
    PrintAllCustomers "yourreportnamehere"
    ghozy.

  7. #7
    Join Date
    Jun 2004
    Posts
    15

    The Code Works But...

    Thank you for all of your help. The code is the first step I believe.
    There is a small problem though. 1 - I can not find where the code is
    saving the snp file. 2 - when the report runs it runs a full copy (all customers)
    each time. There doesn't seem to be any type of criteria to limit the information to only that certain customers information.

    Any Ideas?

    Jeff

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    1 - I can not find where the code is
    saving the snp file.
    It is probably saves it to where mdb file is located, if it is not there check where access software installed. I will add few new lines to that code so it will ask you where to save.


    2. when the report runs it runs a full copy (all customers)
    each time. There doesn't seem to be any type of criteria to limit the information to only that certain customers information.
    You can select which customers will be printed by the SQL statement with a where claus in the Rst.Open method in the code snippet. I will change it for example but you have to add a Print (Yes/No) field into your customer table to make my example work.


    here is the new code:
    Code:
      
    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 CustID, CustName FROM CustTable WHERE Print=True", ADOCon, adOpenForwardOnly
    	
    	Do While Not Rst.EOF 'start sending all reports to files
    		CustomerID = Rst!CustID 'set necessary global variables before print to file
    		Filename = Folder & "\" & Rst!CustName & ".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
    ghozy.

  9. #9
    Join Date
    Jun 2004
    Posts
    15

    Code

    I tried the code and get an:

    Run-time error
    No value given for one or more required parameters


    Any ideas?

    I did set the criteria of the report to [CustId]

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    could you zip & attach your database? I need more detailed info for further advice.
    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
  •