Solved

Msaccess, printing between 2 reports

Posted on 2014-01-30
14
323 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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