Report Generation in Acess 2007

frank_guess
frank_guess used Ask the Experts™
on
Looking for a way to use a table called Tbl_Divisions that has DivisionID and Division Total.  A table named Tbl_AddressBook that has Division, Cost Center, Emplid, EmpName, and other information.  I need a report created for each line within the Tbl_Divisions by DivisionID From the Tbl_AddressBook.  and then total the number of team members and total number of teams from that table.

I want to put this on a form as a button so it when I click on the button it will kick off and create all individual division reports from the Tbl_AddressBook without anyone having to select each one.  I would appreciate the help.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<a table called Tbl_Divisions that has DivisionID and Division Total.>
This table concerns me as tables should not really contain (store)  "Totals"

Totals should be calculated in a query.

So basically you would create a query to calculate the totals, (and show the DivisionID) then relate this to the AddressBook table on DivisionID

Then use the report wizard in Access to create a simple report.

See if you can get this far on your own, then report back.

Remember, since you did not post any details on this design, it is difficult fo us to present a tru "Solution" here.
it is not clear if your tables are properly normalized or related...


<I want to put this on a form as a button so it when I click on the button...>
This is much later, ...lets focus on the table design first, then the report...
Sound fair?

JeffCoachman

Author

Commented:

Ok, here goes:
Table one is the Tbl_Divisions that has DivisionID and Division Total.  The totals is generated from a query and updated to the Tbl_Divisions table.  This is used for reporting purposes and as a check point in other parts of the system.

Second I have a table named Tbl_AddressBook that has Division, Cost Center, Emplid, EmpName, Office phone, cell phone, and home phone.

I have a query where I tie both Tbl_Divisions and Tbl_AddressBook together by DivisionID.

I have a report based on this query where it shows the Divisionid,Cost Center and Department Name,
Then it list in detail the members of the department along with their  Emplid, EmpName, Office phone, cell phone, and home phone.

I have been running this report and individually inserting the DivisionID so I am able to print each report manually.  


What I want to do is use the Tbl_Divisions table DivisionID that list all report groups that I need individual reports for.
I will have approximately 30 reports.  I need to be able to create each report against the DivisionID and save each report as a PDF and as a printed report.

I am looking for code behind the buttons to do this.

Hope that answers the question better.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Table one is the Tbl_Divisions that has DivisionID and Division Total.  The totals is generated from a query and updated to the Tbl_Divisions table.  This is used for reporting purposes>
Again, you can use a query for this.
Using a table for this introduces more issue that it solves.

The bottom line is that you can use a recordset to loop all the DivisionID's then generate a report for each.

As far as I can tell, the solution here is the same as the solution here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27018331.html

JefFCoachman
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Jeff, thanks for responding.  Ok I am removing the Division totals.  The table now has only the DivisionID.  

I setup the two tables in a query within the report and need it to extract individual reports by DivisionID.  When I have it setup it error's out.  Not sure what is causing me trouble.
Here is the code for the button.

Private Sub CmdButtonRecallRosters_Click()
On Error GoTo Err_CmdButtonRecallRosters_Click

Dim rst As DAO.Recordset
Dim strReportName As String
strReportName = "Rpt_680_RecallRoster"
Set rst = CurrentDb.OpenRecordset("SELECT DivisionID FROM Tbl_Divisions")
rst.MoveFirst

Do While Not rst.EOF
    DoCmd.OpenReport strReportName, acViewPreview, , "Division=" & rst!DivisionID
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\" & "Division" & rst!DivisionID & ".pdf"
    DoCmd.Close acReport, strReportName
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing


Exit_CmdButtonRecallRosters_Click:
    Exit Sub

Err_CmdButtonRecallRosters_Click:
    MsgBox Err.Description
    Resume Exit_CmdButtonRecallRosters_Click
   
End Sub
MIS Liason
Most Valuable Expert 2012
Commented:
Please do not insert Error handling until the code is proven.
Inserting your specific Error handling before the code is proven prevents Access from highlighting the offending line of code.

So comment out your EH code and run the code.
Where is the error occurring and what is the exact error number and description?

Just a guess though...
Change this:
    "Division=" & rst!DivisionID
...to this:
    "DivisionID=" & rst!DivisionID

If DivisionID is Text, then try something like this:
    "DivisionID=" & "'" & rst!DivisionID & "'"


JeffCoachman

Author

Commented:
Jeff, Thanks for the information and that fixed my problem.  I have another question.  Still part of this one.

Do you know where I can find the code information for the different codes for the outputformat
I was given acFormatPDF, I cannot find this anywhere.  I want to send the report to a printer.  Can you help.

DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, "C:\" & "Division" & rst!DivisionID & ".pdf"

Author

Commented:
Thanks, Jeff.  Great call.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial