Link to home
Start Free TrialLog in
Avatar of Stephen Roesner
Stephen RoesnerFlag for United States of America

asked on

Break up access report

I have built an access report - what i need to do is by vba code take a table that has multiple accounts and export the report to excel and print a separate report for each account - how can i do that - this is my first access report.
Avatar of Richard Daneke
Richard Daneke
Flag of United States of America image

I don't understand the need to export to Excel.
If your report:
  1. has grouping by the field which stores the multiple accounts
  2. is showing the footer for this field, then
you can set the Force New Page property in the footer section of the report to print a new page for each account.
As Richard said, you really should do this in Access. Exporting to Excel just to create a "report" is often more work than it's worth, and your nicely formatted report will NOT transfer over to Excel, since you don't really export a "report" to Excel, but rather you export the data contained in a report to Excel.

That said, if you need to export your data to Excel for distinct blocks of records, you can do something like this:

First, create a query that will be used to export your data. Add the table containing your Account data to the query, and save it as something like "qryAccounts". You don't really have to worry with anything else, since you'll be resetting the SQL later.

Dim rst As DAO.Recordset
Set rst= Currentdb.OpenRecordset("SELECT DISTINCT Account FROM YourTable")
Dim qdf As DAO.QueryDef
Set qdf = Currentdb.QueryDefs("qryAccounts")

Do Until rst.EOF
  qdf.SQL = "SELECT * FROM Account WHERE AccountID=" & rst("AccountID")
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryAccounts", "C\SomeFolder\" & rst("AccountID") & "_DataExport.csv"    
  rst.MoveNext
Loop

More on TransferSpreadsheet: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx
For one of my clients, I do something similar.  I populate a recordset with a Select Distinct result.  I then iterate the recordset rows, and generate an Access report for that row's key value, and then save the report with a name that can easily be identified by its content.  My user also found it advantageous to save the reports in directories associated with the departments where these people worked.
What may be needed here is a clearer explanation of exactly what you have, ...and what you ultimately need.

It would help if you posted an example db that includes some sample data and the report you have now.
Then post a clear, graphical example of the "individual" reports.
Avatar of Stephen Roesner

ASKER

Scott you seem to have what i need however im getting a strange error. It indicates a file download to a wierd combo of folders it says this :
'C:\users\c36320\documents\C\ERPDP\LoadStatus\
Sorry it posted to early:

here is my code:  
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef

 Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account Number] FROM LoadStatusReport_Work5")
        Set qdf = CurrentDb.QueryDefs("Client Critical Error Report W5")
        'DoCmd.OpenQuery "Client Critical Error Report W5"
        Do Until rst.EOF
          qdf.SQL = "SELECT * FROM LoadStatusReport_Work5 WHERE [Account Number]=" & rst("Account Number")
          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Client Critical Error Report W5", "C\ERPDP\LoadStatus\FileOut\" & rst("Account Number") & "_Client Critical Error Report.xls", True
          rst.MoveNext
        Loop


but its coming up with a wierd path and saying invalid path
its show a path that says this :
'C:\users\c36320\documents\c\erpdp\loadstatus\fileout\2499212"_Client Critical Error Report.xls' is not a valid path
where could it be getting the 'C:\users\c36320\documents\ from???? c36320 is my id
The final argument of the TransferSpreadsheet method defines where the export will land:

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryAccounts", "C\SomeFolder\" & rst("AccountID") & "_DataExport.csv"

The section highlighted in bold above should be changed to indicate exactly where you want to save the files. If you could show the TransferSpreadsheet call you're using, we might be able to help more.
This is not a valid path:

C\ERPDP\LoadStatus\FileOut\" & rst("Account Number") & "_Client Critical Error Report.xls

It seems it should read like this, perhaps:

C:\ERPDP\LoadStatus\FileOut\" & rst("Account Number") & "_Client Critical Error Report.xls",

Notice the colon ( : ) after the C
Scott,
LOL stupid me fixed the colon error but it now says unable to expot data.

        Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account Number] FROM LoadStatusReport_Work5")
        Set qdf = CurrentDb.QueryDefs("Client Critical Error Report W5")
        'DoCmd.OpenQuery "Client Critical Error Report W5"
        Do Until rst.EOF
          qdf.SQL = "SELECT * FROM LoadStatusReport_Work5 WHERE [Account Number]=" & rst("Account Number")
          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Client Critical Error Report W5", "C:\ERPDP\Load_Status\FileOut\Client Critical Error Report.xls", True
          rst.MoveNext
        Loop
Scott when i look at the query that its calling it took out all the fields but put in account number but the select box is deselected the minute i click the box manually it runs the query manually am i missing something
ok i just troubleshot and I found the issue
its expecting the [account number] field to be a number and its a txt field
also im looking for another criteria in the query and that is the [clientreport] field is to = "yes"
do i need to convert the field in the export line and how do i add the clientreport field?
A text field is handled differently:

qdf.SQL = "SELECT * FROM LoadStatusReport_Work5 WHERE [Account Number]='" & rst("Account Number") & "'"

If you need to add another criteria, use the AND or OR syntax:

qdf.SQL = "SELECT * FROM LoadStatusReport_Work5 WHERE [Account Number]='" & rst("Account Number") & "' AND ClientReport=True"
OK i have most of it working Scott : here is the code I did to fix the issues - everything is working except for one minor issue. Here is the code im using:
      Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account Number] FROM LoadStatusReport_Work5")
        Set qdf = CurrentDb.QueryDefs("Client Critical Error Report W5")
        Do Until rst.EOF
          qdf.SQL = "SELECT * FROM LoadStatusReport_Work5 WHERE ([Account Number])=" & """" & rst("Account Number") & """" & " And ([ClientReport])= ""Yes"""
          Debug.Print qdf.SQL
          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Client Critical Error Report W5", "C:\ERPDP\Load_Status\FileOut\" & rst("Account Number") & "Client Critical Error Report.xls"
          rst.MoveNext
        Loop

I figured out the txt issue with the extra quotes and  they are working great
however it is printing one reocrd at a time and I want it to put all the same acct numbers in one report
so if acct 111111 has 2 records it will put both records into that xcel sheet
right now it is doin just one record in one excel sheet.

Im wondering do i need to do a make table in the loop and populate it and just export that table??
was that your plan?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scott thanks you for all your help I have it working.