Starting a query on record # "x" forward

[Rich]

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.

 

[Michel Walsh]

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]

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.

 

[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]

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

 

[Michel Walsh]

Forgot:

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


Vanderghast, Access MVP

 

[Rich]

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]

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]

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.