Printing a series of reports from a list

[Guest]

I would like to sequentially print 3 reports that take their parameters from
a query returning three ids (parameters).

I used to do this in PDOX and it seemed much easier than in Access.

Thanks
wem3rd

 

[Steve Schapel]

Wem,

If you asre doing this via a macro, you would just need to use three
OpenReport actions in the macro, one for each report.

 

[Guest]

Wem,

If you asre doing this via a macro, you would just need to use three
OpenReport actions in the macro, one for each report.

Steve,

There is more to this issue. The list of "parameter values" is variable and
changes each time the query is run. It may return three or thirty IDs, each
of which is to be a parameter for a particular report. I need to place this
list in a que then print one report for each.

Thanks.

wem3rd

 

[Steve Schapel]

Wem,

When you say "parameters", I think you are referring to Criteria. Can
you tell me where these values come from? Are they entered or selected
by the user at the point where the report is printed? Or are they the
result of some other data management procedure in your database? Or
something else? Can you post back with an example of the query? This
query that you are referring to - is it the Record Source for the report?

 

[Guest]

Wem,

When you say "parameters", I think you are referring to Criteria. Can
you tell me where these values come from? Are they entered or selected
by the user at the point where the report is printed? Or are they the
result of some other data management procedure in your database? Or
something else? Can you post back with an example of the query? This
query that you are referring to - is it the Record Source for the report?

Steve,

Yes, the results of the query are listed in a single field and will be used
as criterial in another parameter query which is used to make a report.
There may be numerous records in the first single field table, each record is
to be used as the parameter in another select query.

Is that more clear?

Thanks

wem3rd

 

[Steve Schapel]

Wem,

Thanks for the further explanation.

In Access, the term "parameter query" means a query where the user is
prompted to enter the criteria value at the time that the form or report
based on the query is opened.

Can you post the SQL view of the query that your report is based on, and
also the SQL of the query that provides your list of criteria values.
From what I understand so far, the usual method of dealing with this
would be join these two queries on their related field, which will
automatically result in the report having the required records.

Sorry if I am missing something obvious here, but I think it will help
if you can give specific details, with data examples, it will help.

 

[Guest]

Wem,

Thanks for the further explanation.

In Access, the term "parameter query" means a query where the user is
prompted to enter the criteria value at the time that the form or report
based on the query is opened.

Can you post the SQL view of the query that your report is based on, and
also the SQL of the query that provides your list of criteria values.
From what I understand so far, the usual method of dealing with this
would be join these two queries on their related field, which will
automatically result in the report having the required records.

Sorry if I am missing something obvious here, but I think it will help
if you can give specific details, with data examples, it will help.

Steve,

Thank you for your patience. I effectively need to place the listing of
criteria (many) into a for/next loop so that if I have say 5 ids in the list,
it will print five reports, and if the listing contains ten ids, I will get
10 reports and so forth.

I understand that I can nest the SQL statement in the criteria frame of the
query, but I think that would only cycle once without some kind of loop.
That's where I loose it.

I would be glad to send you SQL statements of a simplified scenario if you
think it would help.

wem3rd

 

[Steve Schapel]

Wem,

Ah, now I begin to understand what you are trying to do.

The reason I suggested sending the SQL of your queries was to help me
grasp what you are working with. Simplified scenarios tend not to help
in such cases - the real thing is what will help.

If you really want to loop through the criteria one at a time, and do a
separate printout of the report for each one, then probably a macro is
not the way to go. It is possible, but looping is not the strength of
macros. You would be better to use a VBA procedure. I can show you how
to do this if you like.

But really, you are probably doing this more complicated than necessary.
This could probably be seen more productively as a report design
question. Base the report on a query that returns all of the required
records. THis should be very easy. This is what I was trying to get at
in my previous reply. In the design of the report, in the Sorting &
Grouping function, put in a ID Footer section in the report, and set the
Force New Page property of this section to After Section. Ok, then you
just need to print the report one time, and it will come with a separate
page for each of the selected IDs, which will be exactly the same result
as if you had x number of reports print.

 

[Guest]

Wem,

Ah, now I begin to understand what you are trying to do.

The reason I suggested sending the SQL of your queries was to help me
grasp what you are working with. Simplified scenarios tend not to help
in such cases - the real thing is what will help.

If you really want to loop through the criteria one at a time, and do a
separate printout of the report for each one, then probably a macro is
not the way to go. It is possible, but looping is not the strength of
macros. You would be better to use a VBA procedure. I can show you how
to do this if you like.

But really, you are probably doing this more complicated than necessary.
This could probably be seen more productively as a report design
question. Base the report on a query that returns all of the required
records. THis should be very easy. This is what I was trying to get at
in my previous reply. In the design of the report, in the Sorting &
Grouping function, put in a ID Footer section in the report, and set the
Force New Page property of this section to After Section. Ok, then you
just need to print the report one time, and it will come with a separate
page for each of the selected IDs, which will be exactly the same result
as if you had x number of reports print.

I missed seeing your response (above) and will do just as you recommend. An
easy and correct solution - Thank you!

wem3rd

 

[Jeff Byrd]

I have been searching for a way to loop through a query and send individual
reports to the printer. If we can send them individually rahter than as one
report our printer will fod them saving my staff time in stuffing them into
envelopes.

Could you share the code to do it.

Here is what I have so far.
Function PrintReport()

Dim intRecordCount As Integer
Dim stDocName As String

stDocName = "Printtest"

'Delcale and instantiate a recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

'Open a recordset based on the result of the query
rst.Open "qryPrinttest", CurrentProject.Connection
intRecordCount = 0

'' Open report here???
Set mobjCurrentReport = stDocName


Do Until rst.EOF





intRecordCount = intRecordCount + 1

rst.MoveNext

Loop


End Function

Thank you

 

[Steve Schapel]

Jeff,

What distinguishes one copy of the report from another? I assume the
qryPrinttest query is the Record Source for the report? And that there
is one of the fields in this query, on the basis of which you want the
report to present the data related to the value of this field?

 

[jeff]

Yes there is a field in the query.

Jeff,

What distinguishes one copy of the report from another? I assume the
qryPrinttest query is the Record Source for the report? And that there is
one of the fields in this query, on the basis of which you want the report
to present the data related to the value of this field?

 

[Steve Schapel]

Thanks for the elucidation, Jeff. Any other details you can share?

 

[Guest]

Steve,

I have found it necessary only to print the one report, one category per
page, as you suggested. I will capture this "looping code" for later use as
well.

Thanks.

wem3rd

 

[Jeff Byrd]

Sorry if I am confusing the issue. There is a report for each active
listing that we have. WE determine the active listings from our Listing
table by running an active listing query. The Field ListingID on the report
matches the field ListingID in the query.

 

[Steve Schapel]

Thanks, Jeff. Not really confusing - just not enough information.
Don't forget, I can't see your database. Only way for me to know what
your query is named, and how your report works, etc, is what you tell me
.

Try something like this...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ListingID FROM [Active
Listing]")
With rst
Do Until .EOF
DoCmd.OpenReport "YourReport", , , "ListingID=" & !ListingID
.MoveNext
Loop
.Close
End With
Set rst = Nothing

 

[Jeff Byrd]

Thanks Steve I got it to work!

Thanks, Jeff. Not really confusing - just not enough information. Don't
forget, I can't see your database. Only way for me to know what your
query is named, and how your report works, etc, is what you tell me .

Try something like this...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ListingID FROM [Active
Listing]")
With rst
Do Until .EOF
DoCmd.OpenReport "YourReport", , , "ListingID=" & !ListingID
.MoveNext
Loop
.Close
End With
Set rst = Nothing

--
Steve Schapel, Microsoft Access MVP

Sorry if I am confusing the issue. There is a report for each active
listing that we have. WE determine the active listings from our Listing
table by running an active listing query. The Field ListingID on the
report matches the field ListingID in the query.