By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Event Procedure to call a Report

P: n/a
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
Jan 8 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Jan 8 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.