Report's Detail Section repeats producing multiple pages

[GraeagleBill]

My brain tickler of the day!

Report where the detail section contains two (2) sub-reports. The RecordSource queries for the two sub-reports are created in code, below are the string creations prior to execution of the query defs: (The queries run without error)

            strQReceipts = "SELECT CategoryName, Sum(Credit) AS SumOfCredit FROM tblCategories " & _
                           "INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID " & _
                           "WHERE (((TDate) >= #" & curRptPerBeg & "# And (TDate) <= #" & curRptPerEnd & "#)) GROUP BY CategoryName, tblRegister.CatID " & _
                           "HAVING (((tblRegister.CatID)>0));"
            
            strQExpense = "SELECT CategoryName, Sum(Debit) AS SumOfDebit FROM tblCategories " & _
                          "INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID " & _
                          "WHERE (((TDate) >= #" & curRptPerBeg & "# And (TDate) <= #" & curRptPerEnd & "#)) GROUP BY CategoryName, tblRegister.CatID " & _
                          "HAVING (((tblRegister.CatID)>0));"

Just note here that the "strReceipts" query when run produces 6 records when the date bracketing is set to the month of January, 2018............... my most important clue so far. There are 6 repetitions of the "Recripts" sub-report.



The design view of the parent report:
and its properties:

When the "Receipts" query is run directly:




The properties of the "Receipts" sub-form container:


The SQL view of the "Receipts" query:

SELECT tblCategories.CategoryName, Sum(tblRegister.Credit) AS SumOfCredit
FROM tblCategories INNER JOIN tblRegister ON tblCategories.CatID = tblRegister.CatID
WHERE (((tblRegister.[TDate])>=#1/1/2018# And (tblRegister.[TDate])<=#1/31/2018#))
GROUP BY tblCategories.CategoryName, tblRegister.CatID
HAVING (((tblRegister.CatID)>0));

So, what's going on that I get 6 repetitions of the "Detail" section of the report?

[ranman256]

looks normal. youre getting multiple records.
do you want 1 client's records? then use only 1 client ID
or
if you want many clients, then the report should pagenate after each client id.

[GraeagleBill]

Thanks, but sorry I don't understand your reply. The entire sub-report pair are duplicated for each record returned by the query. There should only be one sub-report for "Receipts" and one for "Expenses". See the report example in post #1.

[pbaldy]

What is the record source of the report itself? If it returns more than one record, those should be in a different section.

[GraeagleBill]

Thanks Paul, I had a sense it was something simple.

The parent report was originally the basis of just the "Expenses" view of the DB. When the requirement came about to add the "Receipts" view of the DB in a combined view, it became necessary to split the task into the two sub-reports. Guess who forgot to remove the RecordSource from the Parent?

As always,
Bill
(PS) Say hi to "J"

[pbaldy]

No problem Bill. "J" says hi back. We're visiting kid/grandkids in NZ. Nice and warm here right now!