Event Procedure to call a Report

Hi,
I am trying to print address labels. I have a form which asks for
the user to input the first name, middle initial, and last name. On
the button click to print the label, I have an event procedure which
is called. The problem seems to be having two different reports in the
same procedure. Basically, I want to check if the user has typed in a
middle initial. If no middle initial is typed in, I want to load the
report with only the first and last name. However, if the user puts a
first name, middle initial, and last name, I want to load a different
report which filters the results using the entered middle initial. The
report using the first and last name works properly by positioning the
address label by a user defined module which asks how many labels to
print and where to start on the page. However, when the report is
opened using the middle initial, the report opens properly and asks
the user how many labels and where to start, but it ONLY prints 1
label at position 1. The module function called LabelLayout is not
called to position the label on the page. I can't understand why it
works for one report and not the other. When the reports are called
they open "OnLoad" LabelSetup(), and on exit LabelReset(). Here is the
event procedure code:

------------------------------------------------------------ ----------------------------
Private Sub Print_Label_Click()
'Send patient data to label report
Dim first As String
Dim middle As String
Dim last As String

first = "[First Name] = '" & Me![First Name] & "'"
middle = "[Middle Initial] = '" & Me![Middle Initial] & "'"
last = "[Last Name] = '" & Me![Last Name] & "'"

'Open report in Print Preview.
If IsNull(Middle_Initial) Then
DoCmd.OpenReport "Address Label", acViewPreview, , last
Else
DoCmd.OpenReport "Address Label with Middle", acViewPreview, ,
middle
End If
End Sub
------------------------------------------------------------ ----------------------------



Here is the Module Code:

'*********************************************************
'Declarations section of the module.
'**********************************************************

Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&

'Type the following functions:

'==========================================================
' The following function will cause an input box to
' display when the report is run that prompts the user
' for the number of used labels to skip and how many
' copies of each label should be printed.
'===========================================================

Function LabelSetup()
LabelBlanks& = Val(InputBox$("Enter Number of Blank Labels to
Skip"))
LabelCopies& = Val(InputBox$("Enter Number of Copies to Print"))
If LabelBlanks& < 0 Then LabelBlanks& = 0
If LabelCopies& < 1 Then LabelCopies& = 1
End Function

'===========================================================
' The following function sets the variables to a zero
'===========================================================
Function LabelReset()
LabelBlanks& = 0
LabelCopies& = 0
BlankCount& = 0
CopyCount& = 0
End Function

'===========================================================
' The following function is the main part of this code
' that allows the labels to print as the user desires.
'===========================================================

Function LabelLayout(R As Report)
If BlankCount& < LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& < (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function

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

End Sub

Re: Event Procedure to call a Report

AMD_GAMER wrote:
> Hi,
> I am trying to print address labels. I have a form which asks for
> the user to input the first name, middle initial, and last name. On
> the button click to print the label, I have an event procedure which
> is called. The problem seems to be having two different reports in the
> same procedure. Basically, I want to check if the user has typed in a
> middle initial. If no middle initial is typed in, I want to load the
> report with only the first and last name. However, if the user puts a
> first name, middle initial, and last name, I want to load a different
> report which filters the results using the entered middle initial. The
> report using the first and last name works properly by positioning the
> address label by a user defined module which asks how many labels to
> print and where to start on the page. However, when the report is
> opened using the middle initial, the report opens properly and asks
> the user how many labels and where to start, but it ONLY prints 1
> label at position 1. The module function called LabelLayout is not
> called to position the label on the page. I can't understand why it
> works for one report and not the other. When the reports are called
> they open "OnLoad" LabelSetup(), and on exit LabelReset(). Here is the
> event procedure code:
>
> ------------------------------------------------------------ ----------------------------
> Private Sub Print_Label_Click()
> 'Send patient data to label report
> Dim first As String
> Dim middle As String
> Dim last As String
>
> first = "[First Name] = '" & Me![First Name] & "'"
> middle = "[Middle Initial] = '" & Me![Middle Initial] & "'"
> last = "[Last Name] = '" & Me![Last Name] & "'"
>
> 'Open report in Print Preview.
> If IsNull(Middle_Initial) Then
> DoCmd.OpenReport "Address Label", acViewPreview, , last
> Else
> DoCmd.OpenReport "Address Label with Middle", acViewPreview, ,
> middle
> End If
> End Sub
> ------------------------------------------------------------ ----------------------------
>
>
>
> Here is the Module Code:
>
> '*********************************************************
> 'Declarations section of the module.
> '**********************************************************
>
> Option Compare Database
> Option Explicit
>
> Dim LabelBlanks&
> Dim LabelCopies&
> Dim BlankCount&
> Dim CopyCount&
>
> 'Type the following functions:
>
> '==========================================================
> ' The following function will cause an input box to
> ' display when the report is run that prompts the user
> ' for the number of used labels to skip and how many
> ' copies of each label should be printed.
> '===========================================================
>
> Function LabelSetup()
> LabelBlanks& = Val(InputBox$("Enter Number of Blank Labels to
> Skip"))
> LabelCopies& = Val(InputBox$("Enter Number of Copies to Print"))
> If LabelBlanks& < 0 Then LabelBlanks& = 0
> If LabelCopies& < 1 Then LabelCopies& = 1
> End Function
>
> '===========================================================
> ' The following function sets the variables to a zero
> '===========================================================
> Function LabelReset()
> LabelBlanks& = 0
> LabelCopies& = 0
> BlankCount& = 0
> CopyCount& = 0
> End Function
>
> '===========================================================
> ' The following function is the main part of this code
> ' that allows the labels to print as the user desires.
> '===========================================================
>
> Function LabelLayout(R As Report)
> If BlankCount& < LabelBlanks& Then
> R.NextRecord = False
> R.PrintSection = False
> BlankCount& = BlankCount& + 1
> Else
> If CopyCount& < (LabelCopies& - 1) Then
> R.NextRecord = False
> CopyCount& = CopyCount& + 1
> Else
> CopyCount& = 0
> End If
> End If
> End Function
>
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>
> End Sub

You have
R.NextRecord = False
R.PrintSection = False
for BlankCount& < LabelBlanks& but no PrintSection = True and no Next
Record = True (don't know if you want to go to the next record) in the
Else section of code.

Underneath
Function LabelLayout(R As Report)
put the word
Stop
and step thru your code. I think you'll find your problem.

Bell
http://www.youtube.com/watch?v=Z5iQb9ydhR4