Report Conditional Page Break (not!)

[waltb]

Briefly, I have a relational database for tracking inventory of samples. A 'Study' can produce any number of 'Samples' and each 'Sample' can generate up to 5 replicates termed 'Aliquots', using 3 tables as follows: Study, Sample, and Aliquots. All 3 tables use KeyIDs. The Sample table also includes the value to refer back to it's study. The Aliquots table also includes the value to refer back to it's Sample. Referential integrity is enforced such that if a study needs to be deleted, then all downstream Sample and associated Aliquot records are deleted as well. All is going well.

A query-based Sample Log report is configured such that the Samples are sorted to list in sequential order (along with an Aliquot subsort) (starting with "1" until the end of the year, then start over with "1" for a new year). The associated Study also appears along side the sample number. All of this displays in the Details section of the report. A page break must occur if the Study name changes.

At first, all seemed OK if I grouped by the study KeyID (not the study name itself since the study numbers do not arrive in logical order). Howevever, a study required additional products later in the year and resulted in some sample numbers appearing out of their logical numeric sequence due to the grouping.

I then decided to remove the report grouping, and add some expressions to the query (one called "NEWPAGE") which would eventually compare and evaluate the study number from one aliquot to the next (via a DLookup using ID +1), resulting in "Yes" or "No" results if a page break was required or not, and added the following code to the Details Format:

If NewPage = "Yes" Then
Reports![SampleLog].Section(acDetail).ForceNewPage = 2
Else
Reports![SampleLog].Section(acDetail).ForceNewPage = 0
End If

That was working fine, except I have some missing KeyID values due to record deletions. And of course my expressions to determine a page break evaluate incorrectly on those rare occassions where (ID +1) doesn't exist (I had included NZ in my expressions to avoid null arguments).

So, how do I "look up" a value in the next record from any given record within my report when (ID +1) results in a null value, in order to make a page break determination?



Thanks.

[apr pillai]

You can generate new KeyIDs for each Year group by adding a new Column in the Report Query. Copy the following VBA Code into a Standard Module and save the Code:

Public Function QrySeq(ByVal fldvalue, _
                       ByVal fldName As String, _
                       ByVal QryName As String, _
                       ByVal ctrlField As String) As Long
'-------------------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query in a new Column
'Author : a.p.r. pillai
'Date   : Dec. 2009
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------------------
'1 : Column Value - must be unique Values from the Query
'2 : Column Name  - the Field Name from Unique Value Taken
'3 : Query Name   - Name of the Query this Function is Called from
'4 : Group Column Name - To check & Reset to 1 for each group
'-------------------------------------------------------------------------------
'Limitations - Function must be called with a Unique Field Value like AutoNumber
'            - as First Parameter
'            - Need to Save the Query after change before opening
'            - in normal View.
'-------------------------------------------------------------------------------
On Error GoTo QrySeq_Err
Dim k As Long

restart:
If i = 0 Or DCount("*", QryName) <> i Then
    Dim j As Long, db As Database, rst As Recordset

    i = DCount("*", QryName)
    ReDim varArray(1 To i, 1 To 4) As Variant
    Set db = CurrentDb
    Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
    
    j = 1: k = 1
    Do While j <= i And Not rst.EOF
       varArray(j, 1) = rst.Fields(fldName).Value
       varArray(j, 2) = k
       varArray(j, 3) = fldName
       If j <> 1 And j <> i Then
          If rst.Fields(ctrlField).Value <> varArray(j - 1, 4) Then
            k = 1
            varArray(j, 2) = k
          End If
       End If
          varArray(j, 4) = rst.Fields(ctrlField).Value
          rst.MoveNext
          j = j + 1: k = k + 1
    Loop
       rst.Close
End If

If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
    i = 0
    GoTo restart
End If

For k = 1 To i
If varArray(k, 1) = fldvalue Then
    QrySeq = varArray(k, 2)
    Exit Function
End If
Next

QrySeq_Exit:
Exit Function

QrySeq_Err:
MsgBox Err & " : " & Err.Description, , "QrySeqQ"
Resume QrySeq_Exit

End Function

Create a new Query Column and call the QrySeq() Function with correct parameters to generate sequence numbers for each Year Group.

If you need continuous Sequence Numbers for the full Query Records then refer the following Blog Post: Autonumbering in Query Column. The above Code is a variant of this blog post and is intended to publish the solution in an Article next week.

Read the comment section at the top of the Code to pass the parameters correctly when called from a Query Column.

Good luck.

[waltb]

Thanks! Just a couple of questions:
1. Correct me if I'm wrong but it looks like I need to substitute various parameters with those from my own database (QryName, fldName, CtrlField)?


2. Does these loops and arrays function AFTER the report sorting has taken place (which gets my data in the correct sequential order for display)?

3. Rather than creating temporary sequential 'KeyID' values, is there VBA coding simply involving 'move to next record' within a query-based recordset, to obtain and compare the needed values for my expressions?

Thanks!

Walt

[apr pillai]

Yes, you have to substitute the parameter values from your own Query.

You may create a Make-Table Query and run it to create an output table for your report with the correct sequence numbers. This will ensure that the generated new numbers stays in the Table.

[waltb]

Thanks. I'll give it a whirl and let you know.

[waltb]

Ok, finally getting back to this issue. Substituting "QryName" was the easy part. I'm confused regarding the following two items:

rst.Fields(fldName).Value

rst.Fields(ctrlField).Value

I'm not sure what 'fldName' and 'ctrlField' are referring to. I'm assuming 'fldName' is the KeyID field name (in my case, simply ID). If so, what is 'ctrlField'?

Thanks!

[apr pillai]

You must sort the data on the 'CtrlField' to group the Value on this field and the sequence number will reset to 1 on this field when the value changes.

Example:
Sample Data
------------

SchoolName ID Score KeyID
City School . . . 1 . . . 15 . . . 1
City School . . . 5 . . . 13 . . . 2
City School . . . 3 . . . 10 . . . 3
Holy Trinity . . . 8 . . . 25 . . . 1
Holy Trinity . . . 15 . . . 17 . . . 2
Holy Trinity . . . 6 . . . 9 . . . 3
Holy Trinity . . . 2 . . . 6 . . . 4

The sample Function call for the above data:

Call QrySeq([ID],"ID","Query1","SchoolName")

[waltb]

Thanks again. However, I was able to find another solution. It was a counter within a function, much like you presented, but only ~12-15 lines of code, which was called up in the report query.
There was no need for an array. A grouping (by this counter value) was added to the report and works like a charm. I wouldn't have figured it out but it was easier than I thought. Thanks again!

[alikeita]

Hi,
I have a report on which countries names and their delegations will appear. However, on some pages, and at the end of the page, a country can stay alone when the members of the delegation are on the other page.
I tried Keep Together but didn’t work, this because I want all countries to be continued but if one country’s name stays at the end of the page it will be forced to join its delegation members on the next page.
I tried also the conditional break based on a control specific value, but this one will break the page every time at the same place even if the country fits in the page.
Is there any thing that helps to count access report lines or controls per page so I can apply a code on it to break at a specific place of the page ?
Thanks