Solved

Msaccess, printing between 2 reports

Posted on 2014-01-30
14
325 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 48

Expert Comment

by:Dale Fye
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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