Report works but then fails when used as a Sub-Report source

[GtiVr6]

Hi, sorry if this sounds too easy or stupid, I've been trying for a couple of days now to get this to work and I've read a lot on here so hopefully didn't miss anything.

I have a report that basically draws fields out of a table and displays them, nothing fancy. I need it to print 8 and then page break, again no problem as a report - it works. Now the main report has 5 of these sub reports and I figured I would work with it one at a time to get it right and eliminate re-doing a lot of work. When I use the report as the sub report source there are no page breaks and the report is only one page - shows 23 of the 26 records. If I link the master/child items I get one record only and still one page. I have the page break in the sub report detail and the code is as follows (found in the OnFormat portion):

If Me![txtCount] Mod 8 = 0 Then Me![PGBK].Visible = True _

Else Me![PGBK].Visible = False


I'm at a loss. I would have thought a working report could be used as a sub report with no tweaking. I thought worst case I would need to move the page break to the main report but still check on the number of records placed in the sub report. Any ideas?

 

[dhookom]

Subreports have no knowledge of page sections, events, controls,...

My attempt would be to add a column in the record source of the subreport that would number the first 8 records with a 1 then the next 8 records with a 2 etc. You can then add a column or expression to your main report with 1 as a control source then 2 then ...

You could use the link master child to link the 1 on the main report to 8 1s on the subreport etc...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GtiVr6]

Thanks, I'll try that and see how it goes.

 

[GtiVr6]

Hi Duane, I've tried to do what you've asked and I'm having some trouble. I've also reviewed what you wrote in a previous thread where someone was having a similar issue and they needed to group by 10. I've added the column in the record source query to create the GroupNum however when I review the query all the records have the same GroupNum. In total there are only 26 records right now and I need them in groups of 8. When I run the following code the GroupNum for all the records is 3 - I'm either a twit or I'm not understanding something here =) The code is below (it's pretty much your code from the other post).

SELECT Val(DCount("*","Muck Data"))\8 AS GroupNum, [Muck Data].*
FROM [Muck Data] ORDER BY [Muck Data].Manager_Name

This report provides the manager status on all their clients. The main report has 5 sub reports and I can fit 8 records on a page nicely, so after 8 I require the gorup to move on to another page. Muck Data is the table with all the information in it.

 

[dhookom]

You must DCount() something that is unique. Your DCount() will always return 24 which divided by 8 is 3. How would you want to group and order your records?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GtiVr6]

I see, now I have 2 situations for this report. For the individual manager reports, the unique factor will be manager name, however in the second instance (which is the one I was working on since it returns the most records) is the senior manager report.

I'll add the criteria to select based on this field and see what happens.

Now I think I misunderstood your explanation in the thread where I obtained this code. I expected the query to go through and place a 0 in the GroupNum for the first 8 records, 1 in the next 8 etc until all the records were retrieved. Then on the main report I could have a control with control source =1 and use the running total in order to match the two groups and thus print the 8, page break the new control increments, then prints the next 8 and so on. Am I right, did I misunderstand?

 

[GtiVr6]

Hmmm, I'm still not getting very far here and I know it's something stupid that I just don't seem to get. I downloaded the Northwind DB and put your code in there and it works fine, like I want it to.

Basically I want it to return to me, all client related fields for each person the report is run for (the query will ask me the name and boom it prints out).

The client name is the primary key and it set as unique. I have an index on the managers name with duplicates as they can manage more than one client. So in the case I am working on I have 26 clients in the table and they all belong to the same senior manager. I would like them to be in groups of 8, ordered by client name. So I made a change in the criteria portion of the DCount function but the example you provided was numeric and I'm not sure if I'm translating it right into a string format. Bear with me but the code you provided is:

SELECT Val(DCount("*","Orders","EmployeeID=" & [EmployeeID] & " AND OrderID <" & [OrderID] ))\10 AS GroupNum, Orders.*
FROM Orders
ORDER BY Orders.EmployeeID;

which I read as take the number portion of the result returned from the DCount function, divide it by 10 and put the integer portion in GroupNum, select all fields from the orders table, order them by employee and return the records. With DCount it says count all records from the Orders table where EmployeeID equals [EmployeeID] and OrderID is less than (this is the thing I'm not sure I understand) [OrderID]. I see in the table that OrderID is a unique key and is associated to an employee but why less than, I don't see what it's comparing it to. So my code was similar (I changed some data for testing):

SELECT Val(DCount("*","Global","Manager=" & [Manager] & " AND Client <" & [Client] ))\8 AS GroupNum, Global.*
FROM Global
ORDER BY Global.Manager;

but it isn't working like I wanted. I managed to get all 0's instead of all 3's but I think that was because it's failing somewhere.