Does anyone know where I can get the code to print a single mailing
label using VBA? User has labels that are six per sheet, and the
particular task requires them to print a single label.

Thanks!

John

On Wed, 01 Jul 2009 09:57:12 -0700, John von Colditz wrote:

First create a label report.
Then add a Report Header.
Add 2 unbound controls to the Header.
Name one "SkipControl"
Leave it's control source blank.

Name the other control "SkipCounter"
Set it's control source to:
=[Skip how many?]

Then code the report's Detail Print event¡K

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False ' Skip missing labels on sheet
Me.PrintSection = False
Else
[SkipControl] = "No" ' Print labels
Me.PrintSection = True
Me.NextRecord = True
End If

End Sub

And code the Report Header's Format event¡K

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub

Run the label report. Enter the number of label positions you wish to
skip.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Wed, 1 Jul 2009 10:07:56 -0700, fredg wrote:

Whoops! I think I might have answered a question you did not ask,
though you are welcome to use the code to print just the one label on
a sheet that has missing labels.
To print just one label you'll need to filter the report's record
source to return just that one record. I can't tell you how to do that
as you haven't given any indication of how the user selects that one
record or what the criteria is.
Post back with more information.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

After serious thinking John von Colditz wrote :
I need to note this is being done via Word 2003, not Access reports!

Have a look at this

It asks which label you want to print from and how many labels.

You will need to set some sort of filter to print the desired record

Phil

Option Compare Database
Option Explicit
' Label Saver v97.30 from Peter's Software
'
' Allows users to specify a starting label position and a number of
copies via input boxes
' when printing labels
'
' This module was created by:
'
' Peter's Software
' http://www.peterssoftware.com
' info (AT) peterssoftware (DOT) com
'
' ...with some help from Microsoft Knowledge Base article Q299024
'
' This module is freeware.
'
' I do not require that this header be included in any apps you distribute,
' but would appreciate credit being given in your documentation.
'
' If you find that this product is useful to you, please drop by our web
site
' at http://www.peterssoftware.com and sign up on our mailing list so we
can
' notify you when new versions of Label Saver and other Access tools become
available
'
' Usage:
' LS_Init - from your label report's report
header OnFormat event procedure
' (create a label report header if you
don't have one already. Set
' the report header section height to
0.)
' LS_ReportOnOpen Me, Cancel - from your label report's OnOpen event
procedure, and
' LS_DetailOnPrint Me - from your label report's Detail
OnPrint event procedure.
'

'Module variables
Dim iLSBlankRecordsToPrint As Integer
Dim iLSBlankCount As Integer
Dim iLSCopiesToPrint As Integer
Dim iLSCopiesCount As Integer

Sub ls_DetailOnPrint(Rpt As Report)
'Print a specified number of blank detail sections.

On Error GoTo ls_DetailOnPrint_err

If iLSBlankCount < iLSBlankRecordsToPrint Then
'Leave a blank detail section without skipping a record
Rpt.NextRecord = False
Rpt.PrintSection = False
iLSBlankCount = iLSBlankCount + 1
Else
If iLSCopiesCount < iLSCopiesToPrint Then
Rpt.NextRecord = False
iLSCopiesCount = iLSCopiesCount + 1
Else
iLSCopiesCount = 1
End If
End If

ls_DetailOnPrint_exit:
Exit Sub
ls_DetailOnPrint_err:
MsgBox "Error in Label Saver subroutine ls_DetailOnPrint - " & Err & " -
" & Err.Description
GoTo ls_DetailOnPrint_exit

End Sub

Sub ls_Init()
iLSBlankCount = 0
iLSCopiesCount = 1
End Sub

Sub ls_ReportOnOpen(Rpt As Report, ByRef Cancel As Integer)
'Prompts user for a label printing start position, and a number of
copies.
'Sets variables for the OnFormat event procedure to handle

Dim iStartLabel As Integer
Dim iCopies As Integer
Dim vResp As Variant

On Error GoTo ls_ReportOnOpen_err

'Prompt user for starting label position
vResp = InputBox("Start at which label?", "Label Saver", 1)
If vResp = "" Then
'Cancel was clicked
Cancel = True
GoTo ls_ReportOnOpen_exit
End If
iStartLabel = CInt(vResp)

'Validation check
If iStartLabel >= 1 And iStartLabel <= 400 Then
Else
MsgBox "Starting label must be between 1 and 400." & vbCrLf & vbCrLf
& " Labels/Report cancelled"
Cancel = True
GoTo ls_ReportOnOpen_exit
End If

'Prompt user for number of copies
vResp = InputBox("How many copies of each label?", "Label Saver", 1)
If vResp = "" Then
'Cancel was clicked
Cancel = True
GoTo ls_ReportOnOpen_exit
End If
iCopies = CInt(vResp)

'Validation check
If iCopies < 1 Then
MsgBox "Number of copies must be greater than 0." & vbCrLf & vbCrLf
& " Labels/Report cancelled"
Cancel = True
GoTo ls_ReportOnOpen_exit
Else
If iCopies >= 1 And iCopies <= 100 Then
Else
If MsgBox("Are you sure you want to print " & iCopies & " copies
of each label?", vbYesNo, "Label Saver") = vbYes Then
Else
MsgBox "Labels/Report cancelled"
Cancel = True
GoTo ls_ReportOnOpen_exit
End If
End If
End If

'Set variables. These are used in the Report Detail OnFormat event
procedure
iLSBlankRecordsToPrint = iStartLabel - 1
iLSCopiesToPrint = iCopies

ls_ReportOnOpen_exit:
Exit Sub
ls_ReportOnOpen_err:
MsgBox "Error in Label Saver subroutine ls_ReportOnOpen - " & Err & " -
" & Err.Description
GoTo ls_ReportOnOpen_exit

End Sub

Sub ls_ReportOnPage()

'iLSBlankCount = 0

End Sub


And on the Report you need

Option Compare Database
Option Explicit

Dim StartLabInd As Boolean ' Whether start label routine invoked

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

ls_DetailOnPrint Me

End Sub

Private Sub Report_Close()

StartLabInd = False

End Sub

Private Sub Report_NoData(Cancel As Integer)

MsgBox "There are no labels for this category", vbInformation
Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)

ChoseLable:
'Lable Saver
If StartLabInd = False Then ' Not asked
ls_ReportOnOpen Me, Cancel 'Label Saver
StartLabInd = True
End If

Exit Sub

Report_Open_Err:
MsgBox Err.Description
End If

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

ls_Init

End Sub


"John von Colditz" <johnvonc (AT) earthlink (DOT) net> wrote

Phil,

I'll give it a try!

Thanks!

John

Phil Stanton formulated on Wednesday :

John von Colditz presented the following explanation :
I figured it out! I create a temporary tablewith the correct number of
blank records before the label I want to print. I then have Access open
the document in Word, set the Merge Source to my temp table and execute
the merge. Works like a charm!

Thanks Fred,

That was a very useful piece of code.