Solved

Msaccess, printing between 2 reports

Posted on 2014-01-30
14
316 Views
Last Modified: 2014-01-31
Hello Experts,
I am using MSAccess 2013. Wondering if this is possible.
Company has 100 employees and 10 departments. I flagged each employee with E and S for english and spanish.
I have 2 reports, one english and one spanish.
I need to be able to print a report for each employee sort by, department, and then employee last name.
seems easy, but the tricky part is, it has to print a spanish report for the employee flagged as S and an english report for employees flagged as E.
and still maintain the sort order.
the final result should be the 100 pages sorted accordingly but with english and spanish mixed in..

Is this possible?
0
Comment
Question by:jann3891
  • 7
  • 6
14 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39822402
Could you do this with a Master report / Subreport? Have the master report retrieve all of the employees, and hide / show the subreport that matches the desired language? The subreport would need to accept the identifier for the employee.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39822461
my first thought would be to create the main report which basically has a recordsource that only includes the fields you need for your sorting, plus the [Language] code field.

Then create an English version and a Spanish version of a subreport.  Put both of those subreports on the form, one on top of the other.  Set their Height to 0 and CanGrow to Yes.

Then use the Detail sections Format event, along with the [Language] field from the main forms recordsource to set the visible property to each of the subreports.  Something like:

Private Sub Detail_Format

    me.subEnglish.visible = me.[Language] = "E"
    me.subSpanish.Visible = me.[Language] = "S"

End sub

This is just an idea, as I have never actually tried this.
0
 

Author Comment

by:jann3891
ID: 39822513
thanks for the prompt reply. let me give that a shot and I will report back
0
 

Author Comment

by:jann3891
ID: 39822638
i filtered my query to 10 employees only.
it seems to be looking at the first employee, which is E, and then run the remainder of the employees in english, and then the second employee is S, then it run another set for all the 10 employees again in spanish..
so, overall, it generated 100 reports..
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39822922
can you generate some made up data and send all of the pertinent parts in a dummy database?

I'll take a look tonight and see if I can make that work.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39822987
OK,  What you probably failed to do was create the link between the main (wrapper report) and the two subreports.  You would need to establish that link for both of the sub reports.

Take a look at the example attached.  The main report (rpt_English_Spanish) only has two fields [lngNumber] and [Language] and is sorted by the lngNumber field.  I have displayed those two fields in the report, but would normally set their visible property to No.

In the sub_English sub report, I have put the data labels on the left side of the data, while on the sub_Spanish report, I put the data labels on the right.

Another thing you will notice is that I didn't put a label for each report in the "Page" or "Report" header, this is because those won't print for a sub-report.  Instead, I created a group in the subforms to group by the [lngNumber] value, and placed a label in the group header to distinguish between the English and the Spanish reports.

The only difference between rpt_English_Spanish and rpt_English_Spanish2 is that in #2, instead of sorting by [lngNumber], I grouped by that field instead.  This achieves the same sorting purpose, but also provides me with a group footer.  By clicking on the Group Footer and opening the properties window, you can set the ForceNewPage property to "AfterSection", which will result in each report showing up on a separate page.
AlternatingReports.accdb
0
 

Author Comment

by:jann3891
ID: 39824235
OK, i will study this and try to follow. will get back to you soon
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jann3891
ID: 39824293
Ok, I think I'm following what you did now. but the main report is still not the desired result i was looking for...
the first record is E, so I only want the english to appear and the spanish report to not show at all. and the third page should only show spanish and english should not show... etc..
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39824414
Then it sounds like you failed to add the code to the report details format section.

Open the example click on the details section header and look at the code associated with the Detail_Format event.  In that code, it uses the value in my [Language] field to show/hide the appropriate report (English or Spanish) for each record.  Only one of those reports should show for each person (but you will have to change my [Language] field to reflect the field in your table that contains the E or S.
0
 

Author Comment

by:jann3891
ID: 39824456
I am still working on your sample database you attached, and when I open the rpt_English_Spanish report, it is showing both english and spanish for each record. (see attached)
Capture.JPG
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39824492
OK, this is a problem with the Report View of Access, which is now the default.  When you view a report in report view, many of the form events do not fire.  Instead of simply double clicking on the report, right click and view it in PrintPreview mode.

When you use PrintPreview, those events will fire.
0
 

Author Comment

by:jann3891
ID: 39824510
Oh my, I did not know that. I am barely starting on Access 2013, and also just noticed that I indicated I am using 2013, instead of 2003... anyways, your sample database did work as you said. I will try to replicate this on my database and will report back soon.
Thanks again.
0
 

Author Comment

by:jann3891
ID: 39824629
it works !!! thank you very much for taking the time to help me out..
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39824636
glad to help.  That is what we are here for.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now