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.
Stephen RoesnerAnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
aikimarkCommented:
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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jeffrey CoachmanMIS LiasonCommented:
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.
Stephen RoesnerAnalysisAuthor Commented:
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\
Stephen RoesnerAnalysisAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Stephen RoesnerAnalysisAuthor Commented:
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
Stephen RoesnerAnalysisAuthor Commented:
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
Stephen RoesnerAnalysisAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"
Stephen RoesnerAnalysisAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, you don't need to make a table and export the table.

The export will dump everything if finds in the query named "Client Critical Error Report W5", so you must first insure that the query is returning the data you expect. If not, then you'll have to adjust it to return the data you want, and then filter it using the qdf.SQL method shown above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen RoesnerAnalysisAuthor Commented:
Scott thanks you for all your help I have it working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.