Access Report (pages printed vs. record count) 1

[ETID]

Hi Everyone...

I have an access report that is launched from a form via vba and the NUMBER pages that are "auto printed" are based on a variable (labelsx) created by running a Mod function on the number of records in the report source. (THE PURPOSE IS TO MINIMIZE WASTE OF LABEL STOCK).

i.e.
Dim stDocName As String
On Error GoTo Err_Print_File_Labels_Click
If labelsx < 30 Then
MsgBox ("Not enough for a full sheet of Avery 8366 Labels, please try again later.")
Exit Sub
Else
End If
PAGE_COUNT = Int(labelsx / 30)
DoCmd.SelectObject acReport, "Labels Project_File_Labels_Avery_8366", True
DoCmd.PrintOut acPages, 1, PAGE_COUNT

'------------
After the printout... a seperate non vba macro runs an append query based on the source data of the report, to keep a log of the labels printed.

The problem is the append query is logging all records from the report source...How do I tell the append query to write only the number of records equal to PAGE_COUNT * 30?

 

[Remou]

Hi
Can you convert the Macro to VBA? Then you could say:

Set rs = CurrentDb.OpenRecordset("Select * From Members")
rs.MoveLast
intPages = rs.RecordCount \ 30
strSQL = "Insert Into Members ( Code, Identifier ) " _
& "Select Top " & intPages * 30 & " Members.Code, " _
& "Members.Identifier From Members Order By Code"
CurrentDb.Execute strSQL

 

[ProgramError]

Do the labels have a auto numbered key? are the records ever delected? If so print out then try some thing like

if record count > 29 then
for counter = 0 to 30
record number = last record count - counter
or even
record number = (last record count - 30) + counter
print it off
next counter
end if

reset record count to last record and save it somewhere

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[ETID]

Thanks to everyone...

Remou, with a little editing the example you posted work just lovely.

and to ProgramError...thanks for that bit of code, I will most likely be able to use it in this project as well.