Please help Detail section problems

[crustas]

Hello

I have a problem in seting the max number of rows in the detail section. I have N (N can take any value bigger than zero) components for each product_id , but i want to split in to groups of 8 (for labeling purposes). I've have tried several aproaches to this problems and nothing it keeps the same, show allways all the rows not only 8 as i pretended. I would like that someone could bring me some light to the problem.

For example for a product_id with 15 rows of subcomponents:

What i have
LABEL NUMBER 1
product id (product header)
xxxxxxxxx
components (detail section)
1 xxxxxx
2 xxxxxx
...
15 xxxxxx

what i want
LABEL NUMBER 1
product id (product header)
xxxxxxxxx
components (detail section)
1 xxxxxx
2 xxxxxx
...
8 xxxxxx

LABEL NUMBER 2
product id (product header)
xxxxxxxxx
components (detail section)
9 xxxxxx
10 xxxxxx
...
15 xxxxxx


Thanks in advance
Crustas

 

[GIA]

I have a solution but it involves a tad of coding, unless someone can provide the missing link I describe below.

Code was needed because I can't come up with a "sequence number" property or such -- sort or like what autonumbering does -- that returns a 1 for the first record, 2 for the second, etc. If THAT is available (call it FOO), then stick another query in between your record source and report, that produces the exact same data as the recordsource plus another column,
GroupNum:=FOO\8 '<<--- or the &quot;N&quot; you describe
and sort/group on GroupNum.

Failing having FOO, in code you could write a simple loop that just incremented a program variable (a la i=i+1) and construct the GroupNum field there.

Note that you could design field GroupNum into the recordsource to begin with, and whether through a query or through code, effectively do an Update Query; you don't have to add another table. (Duh, but I always forget Update query is there, tee hee) &quot;Outside of a dog, a book is probably man's best friend; and inside of a dog, it's too dark to read&quot; - G. Marx

 

[GIA]

Well, does anyone know - short of maintaining your own counter - if there is a sequence counter of records available in Access? I.e., is there a function or variable that returns 1 when the first record is reported (or &quot;query-resulted&quot, 2 on the next, etc.?

Or does anyone have a clever trick to generate such on a query, like a recursive technique?

(As an aside, when I need to enumerate items in Excel, I let (e.g.) A4 be 1, A5 be A4+1, a6 be a5+1, etc. ... so there is a generated and self-maintaining enumeration.) &quot;Outside of a dog, a book is probably man's best friend; and inside a dog, it's too dark to read&quot; - G. Marx

 

[67peshawar294]

Ya all,
No, I'm not from the south.
I think I stumbled across the answer.I wrote a post several months ago didn't get a reply but I finally figured it out.
This is the only way &quot;I&quot; can see to figure and display items between a set of values.

My table is &quot;frames&quot;
the fields are [whpr] and [nhere]

I created a new form, placed the following in the &quot;Page Header&quot; section of the form.
txtbox
=&quot;$&quot; & 0 & &quot; TO &quot; & &quot;$&quot; & [text0]
For another column I placed another txtbox and put:
=DCount(&quot;[whpr]&quot;,&quot;[frames]&quot;,&quot;[whpr]<text0&quot
A third txtbox for another column with:
=DSum(&quot;[nhere]&quot;,&quot;frames&quot;,&quot;[whpr]<text0&quot

text0 is an unbound txtbox that I use to enter a dollar amount.

The next ROW the txtboxes were:

=[text0] & &quot; TO &quot; & [text0]*2

=DCount(&quot;[whpr]&quot;,&quot;[frames]&quot;,&quot;[whpr]+.01 >text0 and whpr <text0*2&quot

=DSum(&quot;[nhere]&quot;,&quot;frames&quot;,&quot;[whpr]>text0+.01 and whpr <text0*2&quot

I did this for 17 rows IN THE PAGE HEADER section.
and incremented each one according to my needs.
The out put was:
Frame cost Num of prices Num of frames
$0 to $10 57 78
$10 to $20 66 36
$20 to $30 107 72
$30 to $40 173 134
.....
Yes it was pretty tedious aligning the boxes and figuring the increments. Now when I enter a dollar amount in the text0 txtbox the &quot;Frame cost&quot; column reflects the change if I enter 12.56 the out put is:
o to 12.56
12.56 to 25.12
25.12 to 37.68
etc.
hope this helps someone out there.
Jim

 

[67peshawar294]

OOPS!
I put the above in the &quot;FORM HEADER&quot; NOT PAGE HEADER.
The above works for numbers, I don't know if it can be used for strings. I'll leave that to the experts.
Also in the &quot;on exit event&quot; of text0 I put:
DoCmd.Requery &quot;text0&quot;
DoCmd.GoToControl &quot;TEXT0&quot;
to keep the input in the text box.
Jim

 

[crustas]

&quot;Ask&quot; for Billy Gatezzzzz

 

[crustas]

I've found the perfect solution for the initial problem.

Through code i've found a way of creating a table that each record includes several 'header' and 'row' fields. There are as many row 'field' as many rows i want to display in the report.

The code gets deeper but the report turns out very simple.

Thanks for the help and support.
Crustas