Print Report for each Filtered Record

[Brilstern]

Ok,

I have a report that is opened up with a filter to a certain person's Equipment Custody Receipt card(ECR). Within this report are two subreports that are linked to the master field on the report to filter each subreport. I can open this report up and print it individually using a form based parameter. What I want to do is print a report for each person or [SSN] on my master table with each persons info. See the link below for the question leading to this one.

http://bytes.com/topic/access/answer...subreport-load

This is what opens up the report:

Private Sub cmdOpenECR_Click()
 
   Dim strFilter As String
   strFilter = "[SSN] = '" & Me.MemberSelect & "'"
       Call DoCmd.OpenReport(ReportName:="rptECR", View:=Access.AcView.acViewReport, wherecondition:=strFilter)
 
End Sub

These are the main controls on the Master Report:

      =[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & "."
    ="XXX-XX-" & Right([ssn],4) & "/" & [MOS]
    =[EAS] & "    Section: " & [Instrument]
    Date Checked In
    SSN (not visible, used for filter)

This is the table that I want to print a report for each [SSN]/This is also the Master Reports datasource:
qryBand Members

    Field                      Type
    Rank                       Text
    FName                      Text
    LName                      Text
    MI                         Text
    SSN                        Text PK
    MOS                        Text
    EAS                        Date/Time
    Instrument                 Text
    Date Checked In            Date/Time
    Supply Rep                 Text

Location Expression:(=[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & ".")[/code]

Below are the two datasources for the SubReports:
tblInstruments

    Field                      Type
    Category                   Text
    Instrument                 Text
    Make                       Text
    Model                      Text
    Serial                     Text PK
    Condition                  Text
    Location                   Text (not visible)
    Checked In/Out             Text
    Date Checked Out/In        Date/Time
    Supply Rep Doing Check Out Text
    Purchase Year              Date/Time

tblNon Serialized Gear

    Field                      Type
    Category                   Text
    Item Name                  Text
    Brand                      Text
    Condition                  Text
    Checked Out                Text
    Location                   Text(not visible)
    Date Checked Out           Date/Time
    Supply Rep Doing Check Out Text
    Description                Text

The Location is the Master/Child Field that creates the filter on the SubReports. The location on both subreports is a built query with the matching expression:

Expression:(=[rank] & " " & [lname] & ", " & [fname] & " " & [mi] & ".")

Any guidance, I have looked at a lot of forums and none of them seem to lead me any closer to my answer...

Thx
Sgt B

[NeoPa]

If I understand you correctly then all you need to do is to remove the WhereCondition parameter from your DoCmd.OpenReport() call (Line #5 from your first code block) :

Call DoCmd.OpenReport(ReportName:="rptECR", View:=acViewReport)

PS. Notice the usual version of the View parameter.

[Brilstern]

NeoPa,

Ok so what this does is it prints one report with half of the data coming from one social and the rest coming from another. Not sure what causes that.

My goal here is to print out separate reports (on different sheets) for each person.

Im my head I see it like this...

For each SSN in [SSN] print out an individual report

[NeoPa]

The filtering (WhereCondition parameter) determines which data (records) should be included in the report. How the report deals with each record individually is down to the report's design.

[Brilstern]

OK, I understand what you are saying. I think I might be confusing myself here. I do want to filter it. But I want to filter it for each member and then print for each member separately.

Sgt B

[NeoPa]

In that case (if you're sure - I suspect you could handle it differently mind) you would loop through the various records in your code and open the report to print for each one using the specific filter (for the record currently active).

[Brilstern]

OK, This is more where I was heading, I don't really have much experience with the loop function but I will do some research and ask more if i run into trouble. Thank you for the help.

Sgt B

[Brilstern]

Well this is what I tried and to no luck. I get "data type mismatch in criteria expression" error.

Private Sub cmdPrintAll_Click()
 
Dim rsDat As Recordset
Set rsDat = CurrentDb.OpenRecordset("Select [SSN] From [tblBand Members]")
Do
        DoCmd.OpenReport "rptECR", acViewNormal, , "SSN = " & rsDat(0)
 
rsDat.MoveNext
Loop Until rsDat.EOF
 
End Sub

Any Ideas

The below link is where I based my vba off of.
< Snip >

Sgt B

[NeoPa]

Stevan Bias:
This is more where I was heading

Unsurprising and quite usual thinking. Generally however, not sensible thinking. Without knowing your situation in detail I can only say that a more appropriate approach is probably to design the report to print all the records properly. After all, why design something to ensure only the selected item is included, just so that you can loop through all the items and print them separately? Considering the original report, without the added restriction, does the whole job for you anyway.

Nevertheless, if that's the way you choose to go, and let's face it if you do it the other way there's little more to add is it's far simpler to start with, then your line #4 can be a table rather than the SQL string you've used. Anyway, the following might work for you if my guesses as to what is what are correct :

Private Sub cmdPrintAll_Click()
    Dim cdb As DAO.Database
 
    Set cdb = CurrentDb
    With cdb.TableDefs("[tblBand Members]").OpenRecordset(dbOpenTable)
    Do (While Not .BOF) And (Not .EOF)
        Call DoCmd.OpenReport(ReportName:="rptECR", _
                              View:=acViewNormal, _
                              WhereCondition:="[SSN] = '" & !SSN & "'")
        Call .MoveNext
    Loop
End Sub

PS. This is still not a way I would recommend, but you may have reasons of your why this is your preference so I'll leave it with you.

[Brilstern]

NeoPa,

This does work but what would you suggest. This is my first attempt on "record looping" so I would like to know whats out there.

Sgt B

[sierra7]

Hi
From something I remember about a previous post from Stevan, he had over 50,000 people on his database so I can understand that he does not want a report for everyone, just 'Members of the Band', so looping through them as per NeoPa's code should do the trick.

The alternative (more usual) way, would be to setup a new query for the report that included the field that identifies a person as being the member of a band. (while you are at it include any other fields that you may require to filter on i.e Marksmen, Tank driver etc) By then calling this report with the 'WHERE' filter as 'BandMember= True', you should get what you want.

At the foot of each section you will have to include a PAGE BREAK control to ensure each starts on a new page.

If the Quartermaster then wants his tanks back, you are in a position to run an alternative report for just Tank Drivers.
S7

[Brilstern]

Sierra7,

Interesting link to my previous questions but fortunately these are two different databases. In the one in question it only had band members and I want to print a report for every member no matter what. The reason behind the filter is to push the correct information to each report because it has subreports that are filtered.

Sgt B

[sierra7]

Hi,
In that case I cannot see why NeoPa's first post did not work.

I imagine both your sub-reports are in the same Detail section ?

If you make a copy of the Master report, then delete the two sub-reports does it run properly.

If you then put in the PAGE BREAK do you then get one page per person?

Then add back one sub-report at a time and re-test. What results?
S7

[NeoPa]

I'm not clear what you're unclear on Stevan.

S7 describes it reasonably well. The WhereCondition parameter which is used to specify which group, or individual, records to open (and therefore print) controls which to include in the specified print run. If the design of the report handles multiple records properly (Headers and footers in the right places and pages thrown between records where required) then they can all be processed in a single print run.

If you have a table with three records, A; B; C, then you can either say Run report or alternatively, Run report specifically for A, Run report specifically for B and Run report specifically for C. Both approaches eventually produce the same results, but the first is more straightforward.

Does that make sense? Is that what wasn't clear?

[Brilstern]

Here seeing what I may have might help

Because the filter for the report is not in the report itself but pushed via the command to open it, see the attached DB to better explain what it is.

Sgt B

[Brilstern]

I do see what y'all are saying now but I don't know why it wont work.

Sgt B

[sierra7]

Hi
A quick look shows that the two subreports are linked to the main report via 'Location'. (Link Master Fields / Link Child Fields)
Surely it should be SSN?

S7

[Brilstern]

Sierra7,

No, If you look on the hidden tables on the instrument and non serialized gear the linking field is location. When the user selects where the item is they don't use SSN because that would just be impracticable. So I have created an expression (location) that uses the Rank, LName, and FName as a combolist selection.

Sgt B

[sierra7]

OK
But I could not see a Location in the Master Record. I take it that Rank, LName, and FName are all there but in that case you have to specify them as

Rank;LName;FName 

for both Mastcer and child fields, or create a 'Location' field in the same format as you used previously.

I'll take a closer look

S7

[sierra7]

OK, there are a number of issues.
The first is that although you are formulating and selecting a Location for the Master Record, it's not appearing anywhere on the report and all the other master data is appearing in the Page Header section.

It would be better to move all this heading into the Detail section. I'm still playing.

I also noticed that in the NonSerialized subreport the hidden Location field is in the Page Header, not the detailed section (this may not matter butI'll check)

I have it running OK but I'll put the Page Breaks in and add a few more bods.

S7

[sierra7]

I think it is working properly now. By putting the data in the Page Header you were getting the codes for the first record only and this was not changing on subsequent pages.

Each persons Location code must appear in the Detail section to associate the details for that individual. I've just remembered that I have not hidden the Location field but really you could use this instead of your 'member' control.

Note the position of the Page Break, just after the second sub-report.

I've also changed the [Print All] button's code to Print Preview for testing purposes.
S7

[Brilstern]

AHA,

Thank you. I see now, Good to know I'm not completely crazy. Out of curiosity why does the location of the data matter in the since of this report?

Sgt B

[NeoPa]

Stevan Bias:
Out of curiosity why does the location of the data matter in the case of this report?

Typically, a page header and footer will only reflect data that is common to all sections printed on that page. If there is data that needs to be shown once only for any grouping then group header and footers can be used for that.

[Brilstern]

OK,
I figured that is what it might be but I wanted to make sure. Thx

Sgt B

[sierra7]

Hi,
I'd like to gently point out that it was not NeoPa's suggestion that fixed the report, otherwise the issue would have been resolved last week.

What was wrong were the location of field controls in the Header section rather tah the Detail section.
S7

[NeoPa]

Go ahead and reallocate the Best Answer Stevan. I'm aware you've struggled manfully to decide between the two of us already (I noticed the posts being set and reset over a period). S7 certainly spent some time and effort on assisting you directly with your project. It's hard not to see an answer being reset as a criticism of your work - even though that's often not an accurate reflection of the OP's thinking at all. You, as an OP, are left in a difficult position in a question like this, where you feel more than one member has been particularly helpful in a thread.

I understand all these issues and won't be the least put-out if you use this to thank S7 for his good work in helping you on this issue :-)

[Brilstern]

I understand both of you and yes, I have had a lot of trouble deciding which is better, one because NeoPa's shows the correct code and easier to see the answer but I do see where S7's explanation is more useful to the viewer.

[sierra7]

@NeoPa
I'm not really miffed. However, this should have been (forgive me Stevan) a trivially easy question which the first post should have cleared up. (Most usually people create a report then ask the question how to restict it to a selection!)

Anyone searching the database for advice might overlook some of the issues revealed by this case. (If I really wanted to rant, I would pick on the search engine used on this site. I can't even find my own answers to save typing on occasions)

The looping solution is good and may prove useful, especially for producing separate PDF reports for emailing (rather than one long one). It would have been a solution in this case because the content was the same as the header.

However, the posting of the demo database was invaluable in helping to resolve this issue. Thanks for taking the time to prepare it Stevan; I know how much time it takes to sanitize data. I think we should ask for samples more frequently because so much time is taken trying to decide what the OP means and we can easily end-up making wrong assumptions.

I'm just an occasional visitor to the site and am not looking for Brownie Points, just answers.
S7

[NeoPa]

S7:
I think we should ask for samples more frequently because so much time is taken trying to decide what the OP means and we can easily end-up making wrong assumptions.

I know exactly where you're coming from S7. I prefer to encourage posters to express their questions clearly, however, rather than to take the easy way out (for most) and decide that the experts can dig around for themselves to work out what the question should have been.

It's always going to be a problem, as very few people generally are skilled in the use of language nowadays. Fewer even appreciate why it can be important. Clearly anyone spending as much time as you do (Occasioinal Visitor is a bit too modest.) gets to realise very early on how important it can be when you see so many threads where the answers would be so obvious if only the questions were asked properly (@Stevan - I'm talking generally here. In no way is this directed at you).

PS. As a starter, [site:bytes.com sierra7 XXX] in Google will help you find your XXX posts.
PPS. I just added a quick feedback mini-article to help with this issue - Searching For Answers Within a Specific Site (EG. Bytes.com).
I use a database for all the important threads myself. Let me know if you'd like a copy.