PrintOut Method and underyling records

[scroce]

I've successfully created a queryDef that words correctly using SQL. It's created by:

'Call CreateQueryDef method
Set qdf = CurrentDb.CreateQueryDef("qryResult", strSQL)

Now I want to run a report based on this query"

'Open the report based on this query
DoCmd.OpenReport "rptPostingNotice", acViewPreview
DoCmd.PrintOut acPages, 1, 4, , 2

If I use the PrintOut Method of DoCmd as stated above, I will get the the first four pages of my report printed out, two copies each.

My Question: how do I make this dynamic? In other words, the report will have a different amount of pages whenever I run it. - So I tried this:

intRecordCount = DCount("[MemberID]", "qryResult")

which counts the records in my recordset, and then:

DoCmd.PrintOut acPages, 1, intRecordCount, , 2

Which does seem to work - although it may not be the best way.

But now, I want to be able to manipulate the number of copies that each page of my report prints out, based on a field in qryResult called NumOfLocations - I.E. - I need to substitute a variable for the "2" to change every time the report page advances to reflect whatever number is in that underlying field called NumOfLocations that is included in the qryResult, which in turn is the source for my rptPostingNotice - HERE IS WHERE I'M STUCK!!!!!

Maybe I shouldn't even be using PrintOut? Help!

I don't know if I've explained this adequately. Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[Darrylles]

Hi,

Too simple perhaps, why haven't you replaced '2' with 'NumOfLocations'.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[scroce]

Hi - thanks for your response!

I tried that, however, nothing prints out when I do so. - When I debug the code, [NumOfLocations] keeps coming up empty, and I'm not sure why.

So I tried this:

NumLoc = DLookup("[NumOfLocations]", "qryResult")

which gets me as far as returning the NumOfLocations field for the first record only.

The problem with this is that it doesn't seem to want to change dynamically with each page of the report.

- let's say that record has 3 locations associated with it, well then that gets picked up just fine, but then it will print out 3 copies for every page of the report that comes out. I want the number of copies to change with each page of the report, based on the underlying field NumOfLocations, which changes on every page of the report.

Does that make sense?

Anyway, I can't seen to get it to advance to the next record's NumOfLocations field - what I need is something like the "MoveNext" method - but I'm not really using a recordset right now - maybe that's my problem????


Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects

 

[scroce]

OK - for anyone who may be searching in the future - here's a resolution the the problem:


Problem:

Can I do this: (pseudocode)

For Each Page in rptMyReport
Print each page x number of times
where x is a number field on the report
that changes with each page




Solution: Use a DAO.Recordset to query for the records, DCount to count up the records in the recordset, and a For..Next nested within a Do Loop to cycle through the underlying data of the report to print them out one by one.

Dim rs As DAO.Recordset
Dim dbs As Database
Dim var As String
Dim intRecordcount As Integer
Dim repName As String
Dim i



Set dbs = CurrentDb
intRecordcount = DCount("[MemberID]", "qryResult&quot
repName = "rptPostingNotice"
strSql = "SELECT * FROM qryResult"
Set rs = dbs.OpenRecordset(strSQL)

DoCmd.OpenReport repName, acViewPreview

Do While Not rs.EOF
For i = 1 To intRecordcount
DoCmd.PrintOut acPages, i, i, , rs("NumOfLocations&quot
rs.MoveNext
Next
Loop

rs.Close
dbs.Close


Q: Why is my computer doing that?
A: Random Perversity of Inanimate
Objects