Starting a query on record # "x" forward

Background....
Printing detail (firearm info) on ID cards. 1st card holds 1st 6 guns,
I want additional cards to print rest of them (without duplicating 1st
6. Owners may not have more than seven or could have many more. Guns
are stored in table with license ID, name, etc...

So, what I need to do is skip the 1st six guns for an owner and print
the rest (if they have more than 6) on the additional cards. The detail
part of the gun info is a report with a query selecting the owners
license ID (so it only prints his/her guns on the card).

Is there an easy (or not so easy) way of doing this (starting with the
7th queried gun and going forward))?

Thanks,
Rich.

Inside a report, sure. Note that you will have to transfer the 'magical'
value of 6 ( I doubt it is a constant applicable to each and every report,
isn't it).

In the onFormat event handling of the section, have something like:


==================
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static mcount As Long

mcount = mcount + 1
If mcount = 6 Then
MoveLayout = False
PrintSection = False
Else
MoveLayout = True
PrintSection = True
End If

End Sub
====================


Note that the static variable simply counts the number of record the sub has
seen. You could use a report-scope variable for the same purpose.


Vanderghast, Access MVP



"Rich" wrote in message
...
Background....
Printing detail (firearm info) on ID cards. 1st card holds 1st 6 guns, I
want additional cards to print rest of them (without duplicating 1st 6.
Owners may not have more than seven or could have many more. Guns are
stored in table with license ID, name, etc...

So, what I need to do is skip the 1st six guns for an owner and print the
rest (if they have more than 6) on the additional cards. The detail part
of the gun info is a report with a query selecting the owners license ID
(so it only prints his/her guns on the card).

Is there an easy (or not so easy) way of doing this (starting with the 7th
queried gun and going forward))?

Thanks,
Rich.

Thanks, and that works..... as long as the report isn't a sub-report
inside another report.

If I run the report it does what I am looking for, however, I have the
report as a sub-report, and it seems to ignore the format statements
below (even though when I trace it, it does goes into the format detail
routine below).

It does however cycle through the detail & format part quite a few
times, so I am thinking that might be the issue also (that things are
getting reset).

Is there something that a report does differently when it's used as a
sub-report?
Any other ideas?

Thanks,
Rich.

Michel Walsh wrote:
Inside a report, sure. Note that you will have to transfer the 'magical'
value of 6 ( I doubt it is a constant applicable to each and every report,
isn't it).

In the onFormat event handling of the section, have something like:


==================
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static mcount As Long

mcount = mcount + 1
If mcount = 6 Then
MoveLayout = False
PrintSection = False
Else
MoveLayout = True
PrintSection = True
End If

End Sub
====================


Note that the static variable simply counts the number of record the sub has
seen. You could use a report-scope variable for the same purpose.


Vanderghast, Access MVP



"Rich" wrote in message
...
Background....
Printing detail (firearm info) on ID cards. 1st card holds 1st 6 guns, I
want additional cards to print rest of them (without duplicating 1st 6.
Owners may not have more than seven or could have many more. Guns are
stored in table with license ID, name, etc...

So, what I need to do is skip the 1st six guns for an owner and print the
rest (if they have more than 6) on the additional cards. The detail part
of the gun info is a report with a query selecting the owners license ID
(so it only prints his/her guns on the card).

Is there an easy (or not so easy) way of doing this (starting with the 7th
queried gun and going forward))?

Thanks,
Rich.

After testing and tracing, it looks like it cycles through the records
twice (2nd time displaying all, because mcount is no longer = 6. Is
this a "feature" of a sub-report? I did get it to work by doing a
DCount on the report query and then when mcount qrycount, subtracted
the qrycount from it to start it back to 1, but I would like to see if
there is a better way of doing it.

Thanks,
Rich.

Michel Walsh wrote:
Inside a report, sure. Note that you will have to transfer the 'magical'
value of 6 ( I doubt it is a constant applicable to each and every report,
isn't it).

In the onFormat event handling of the section, have something like:


==================
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static mcount As Long

mcount = mcount + 1
If mcount = 6 Then
MoveLayout = False
PrintSection = False
Else
MoveLayout = True
PrintSection = True
End If

End Sub
====================


Note that the static variable simply counts the number of record the sub has
seen. You could use a report-scope variable for the same purpose.


Vanderghast, Access MVP



"Rich" wrote in message
...
Background....
Printing detail (firearm info) on ID cards. 1st card holds 1st 6 guns, I
want additional cards to print rest of them (without duplicating 1st 6.
Owners may not have more than seven or could have many more. Guns are
stored in table with license ID, name, etc...

So, what I need to do is skip the 1st six guns for an owner and print the
rest (if they have more than 6) on the additional cards. The detail part
of the gun info is a report with a query selecting the owners license ID
(so it only prints his/her guns on the card).

Is there an easy (or not so easy) way of doing this (starting with the 7th
queried gun and going forward))?

Thanks,
Rich.

No, it is not a feature of a sub-report, but of the static-ity of the
variable. Indeed, if your intention is to use the report as subreport, move
the counting variable in the report variable declaration: in VBA, right
after the OPTION statement and before any procedure definition

Dim mcount As Long ' and remove it from the Detail_Format sub.


and, in the header format section, set this variable to zero.


This way, the variable should re-initialize itself to zero each time the
header section (of the report, even used as subreport) is about to be
formatted.

Technically, you should decrement the variable if the Detail section
'retreats'.



========================
Option Compare Database
Option Explicit

Dim mcount As Long ' ---- new

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Static mcount As Long ' ---- commented out
mcount = mcount + 1
If mcount = 6 Then
MoveLayout = False
PrintSection = False
Else
MoveLayout = True
PrintSection = True
End If

End Sub

Private Sub Detail_Retreat() ' --- new
mcount = mcount - 1
End Sub
==========================




Vanderghast, Access MVP



"Rich" wrote in message
...
After testing and tracing, it looks like it cycles through the records
twice (2nd time displaying all, because mcount is no longer = 6. Is this
a "feature" of a sub-report? I did get it to work by doing a DCount on
the report query and then when mcount qrycount, subtracted the qrycount
from it to start it back to 1, but I would like to see if there is a
better way of doing it.

Thanks,
Rich.

Forgot:

==============
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
mcount = 0
End Sub
==============


Vanderghast, Access MVP

I actually removed the sub-report and added the detail in the main
report. It displays and works correctly in the print preview, however
when I print, it adds the extra records back into the detail part. I am
tracing this part now.

Thanks,
Rich.

Michel Walsh wrote:
No, it is not a feature of a sub-report, but of the static-ity of the
variable. Indeed, if your intention is to use the report as subreport, move
the counting variable in the report variable declaration: in VBA, right
after the OPTION statement and before any procedure definition

Dim mcount As Long ' and remove it from the Detail_Format sub.


and, in the header format section, set this variable to zero.


This way, the variable should re-initialize itself to zero each time the
header section (of the report, even used as subreport) is about to be
formatted.

Technically, you should decrement the variable if the Detail section
'retreats'.



========================
Option Compare Database
Option Explicit

Dim mcount As Long ' ---- new

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Static mcount As Long ' ---- commented out
mcount = mcount + 1
If mcount = 6 Then
MoveLayout = False
PrintSection = False
Else
MoveLayout = True
PrintSection = True
End If

End Sub

Private Sub Detail_Retreat() ' --- new
mcount = mcount - 1
End Sub
==========================




Vanderghast, Access MVP



"Rich" wrote in message
...
After testing and tracing, it looks like it cycles through the records
twice (2nd time displaying all, because mcount is no longer = 6. Is this
a "feature" of a sub-report? I did get it to work by doing a DCount on
the report query and then when mcount qrycount, subtracted the qrycount
from it to start it back to 1, but I would like to see if there is a
better way of doing it.

Thanks,
Rich.

Yes, the static variable 'remembers its previous value until its container
get terminated. So, it may be logical that the static variable works only
'the first time' you 'print' the report, either in preview, either normally,
but not after, unless you close the report itself between the 'printing'.
The variable at the report level should remove that problem, if you don't
forget to re-initialize it to 0 in the report header format event.

Vanderghast, Access MVP


"Rich" wrote in message
...
I actually removed the sub-report and added the detail in the main report.
It displays and works correctly in the print preview, however when I print,
it adds the extra records back into the detail part. I am tracing this
part now.

Thanks,
Rich.

THANK YOU MUCH. All is now Well.

Made the variable a public var at the top of the report, initialized it
in the report header (which I wasn't using) and it does exactly what I
am looking to do, including the print preview and print.

I have been programming for 17 years, but have only recently started
using Access more heavily. Lots of "different" things that are not as
straight forward as I am used to doing (not that this is one of them),
but this forum has been excellent in finding the help.

Appreciate it.
Rich.

Michel Walsh wrote:
Yes, the static variable 'remembers its previous value until its container
get terminated. So, it may be logical that the static variable works only
'the first time' you 'print' the report, either in preview, either normally,
but not after, unless you close the report itself between the 'printing'.
The variable at the report level should remove that problem, if you don't
forget to re-initialize it to 0 in the report header format event.

Vanderghast, Access MVP


"Rich" wrote in message
...
I actually removed the sub-report and added the detail in the main report.
It displays and works correctly in the print preview, however when I print,
it adds the extra records back into the detail part. I am tracing this
part now.

Thanks,
Rich.