Multiple Reports, One Record At A Time (1 Viewer)

[Old Gnome]

(Access 97 running on Windows 95)

I am seeking to print multiple reports one record at a time. I can get the reports to print from VB code, but I can't seem to find a way to print just one record at a time.

The report source table is based on an input table run through some calculations. The report source table is always created for only the records that have been selected for printing.

I would like to be able to print all selected records, but not have to collate the reports myself.

I am using multiple reports because of Access' 22 inch report definition limit.

Any ideas?

TIA

oldgnome

 

[dennyryan]

You might try setting up a loop within your VB code where you read one record at a time. Within the loop issue the DoCmd.OpenReport command and pass selection criteria for the current record.

In this approach the report gets run as many times as the number of records in your input table. There is probably a better way but that's the way I would do it. I'm assuming you know how to create the VB code to do the looping thing and pass the selection criteria in the DoCmd line.

Denny

 

[Old Gnome]

I know how to create a Do While loop, but I'm not sure what to pass through to the reports to print just one record.

Can you point me in the right direction?

TIA

oldgnome

 

[dennyryan]

The synatx would be something like this:

dim strSQL as String
dim stDocName as string

do while not rs.eof
strSQL = "Account = " & rs("Account")
stDocName = "AcctRpt1"
DoCmd.OpenReport stDocName, acPreview, , _ strSQL
rs.movenext
loop


I'm assuming you know how to open up the recordset ("rs" in my example). If not let me know and I'll send an example.

Denny

 

[Old Gnome]

Thanks for the code example. I think I can take it from here....I'll let you know.

oldgnome

 

[Old Gnome]

Denny:

I feel I am so close I can almost taste it. After some working with the code this morning, here is what it looks like:

Dim dbs As Database, rstRecords As Recordset
Dim strRptName As String
Dim strRecord As String
Set dbs = CurrentDb
Set rstRecords = dbs.OpenRecordset("T Calculated Values")

strRecord = "T Calculated Values" & rstRecords("Project_Name")

Do While Not rstRecords.EOF
strRptName = "R OTS Cost Model - 0 Summary"
DoCmd.OpenReport strRptName, acViewNormal,,*what goes here?*

…
rst.MoveNext
Loop

What do I replace *what goes here?* with?

TIA

oldgnome

 

[dennyryan]

It does look like you're very close. I think the changes that you need would be as follows:

strRecord = "Project Name ='" & rstRecords("Project_Name")
& "'"

DoCmd.OpenReport strRptName, acViewNormal,,strRecord

I'm assuming that your Project Name field is a character field in which case you need to include the single quotes in your selection string (strRecord). Insetad of the single quote I think you can use Chr(13) so that it's a little easier to identify the delimiter.

I hope this helps get the process working.

Denny

 

[Old Gnome]

Denny:

It works! Many thanks. (Understanding that I don't have a clue what continent you are on,) the next time we are in the same city, I owe you a beer, a meal, a cup of coffee or tea, whatever.

oldgnome

 

[dennyryan]

Glad to hear it worked!. I'm in Iowa City, Ia. It's at the end of the Road Less Travelled (if at all). Perhaps I can collect on that beer offer at the next intergalactic Access User Group convention.

denny

 

[Old Gnome]

Well...I thought it worked. It turned out the first time printed the first record twice, then the second record. I am only selecting two records to print while testing.

I have also, while trying to tweak the code, run into a couple of other near solutions. In all, they are:
1. Print first record twice, then the second, then finish successfully.
2. Print the second record only, then finish successfully.
3. Print both records and then deliver an 'end of file'-type of message. (I know I should have written it down, but I thought I was nearly at Nirvana....)
4. Print the first record twice, then finish successfully.

(By 'successfully' above, I mean that the code completes without further complaint.)

Here is the current code, giving #4 above:

Dim dbs As Database, rstRecords As Recordset
Dim strRptName As String
Dim strRecord As String
Set dbs = CurrentDb
Set rstRecords = dbs.OpenRecordset("T Calculated Values")
rstRecords.MoveFirst
strRecord = "Project_Name ='" & rstRecords("Project_Name") & "'"
Do While Not rstRecords.EOF
strRptName = "R OTS Cost Model - 0 Summary"
DoCmd.OpenReport strRptName, acViewNormal, , strRecord
rstRecords.MoveNext
Loop

Any thoughts on what to do next?

oldgnome

[By the way, I'm in Green Bay, WI and pass through Iowa about once or so a year. Next time through?]

 

[dennyryan]

I think I see the problem if the code you sent is accurate. The strRecord statement needs to be inside the loop so that value changes on each record. At least in theory that seems right but ideas like this often fall apart when I try to implement them.

Green Bay, eh. Not so far away after all. I work for a company out of Milwaukee and all I hear from about August on is Packer cheers.

Denny

 

[Old Gnome]

That did it!

Many thanks.

We've lived in Green Bay for nearly 6 years now and I have started getting tired of 'all Packers, all the time.' And I grew up in Milwaukee....

oldgnome