Duplicates being produced

[Dan @BCBS]

My report produces too many pages.! And the underlying query does not produce
duplicates.
The report is executed by a button on a form. The code behind the button
specifies Criteria, date ranges, employee and the report to open.

Only two pages should print (1&2), but it returns 1-20. pages 1&2 keep
getting
duplicated (2&3, 4&5, 6&7....) are dups of 1&2...

My report has a Report Header, Page Header. Detail, Page Footer, Report
Footer.

Here is the code behind the button to launches the report. Something is
producing duplicates when one reviewer is choosen????

Private Sub cmdHMO_Click()
On Error GoTo Err_cmdHMO_Click

Dim stDocName As String

'these need to be declared
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean
Dim stReviewer As Variant

stDocName = "r_KeyMembers"
stReviewerList = ""

'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "You must enter Start and End Dates"
Exit Sub
End If

'Reviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " And """
End If

'now remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdHMO:
Exit Sub

Err_cmdHMO_Click:

Err_cmdHMO:
MsgBox "You must pick at least one reviewer"
Resume Exit_cmdHMO
End Sub

 

[Dale Fye]

Dan,

What is the SQL string for the reports recordset? My guess is that if this
is only happening for one reviewer, you have duplicate records for that
individual in one of the underlying tables.

At the bottom of your stReviewer loop, print your stReviewerList. Does it
look unusual in any way?

Dale

 

[Dan @BCBS]

The report has VB code only to open it "Private Sub Report_Open(Cancel As
Integer)"

Attached below is the SQL that creates the report. Also, attached below
that is the code behind the command button that launches the process and
ultimately prints the report.

But, if a reviewer has 7 records for the date range indicated, then 7 pages
of the report are printed.

I have been struggling with this and it's really messing up production, any
suggestions?

SELECT DISTINCTROW tblQualityData.ICNNo, tblQualityData.IssueCloseDate,
IIf([IssueCloseDate] Is Null,Null,MonthName(Month([IssueCloseDate]),True)) AS
Months, IIf([issueclosedate] Is Null,Null,[issueclosedate]-[ReceiptDate]) AS
Days, tblQualityData.InsuranceType, tblQualityData.Source,
tblReviewers.Reviewer, tblSource.SourceType
FROM tblSource INNER JOIN (tblReviewers INNER JOIN (tblMemberInfo INNER JOIN
tblQualityData ON tblMemberInfo.MemberNo = tblQualityData.MemberNo) ON
tblReviewers.RACF = tblQualityData.Reviewer) ON tblSource.Source =
tblQualityData.Source
WHERE (((tblQualityData.IssueCloseDate) Between
[forms]![f_KeyIndicators].[txtstart] And [forms]![f_KeyIndicators].[txtend])
AND ((tblQualityData.Source)<>"other"));

Dan,

What is the SQL string for the reports recordset? My guess is that if this
is only happening for one reviewer, you have duplicate records for that
individual in one of the underlying tables.

At the bottom of your stReviewer loop, print your stReviewerList. Does it
look unusual in any way?

Dale
--
Email address is not valid.
Please reply to newsgroup only.

My report produces too many pages.! And the underlying query does not produce
duplicates.
The report is executed by a button on a form. The code behind the button
specifies Criteria, date ranges, employee and the report to open.

Only two pages should print (1&2), but it returns 1-20. pages 1&2 keep
getting
duplicated (2&3, 4&5, 6&7....) are dups of 1&2...

My report has a Report Header, Page Header. Detail, Page Footer, Report
Footer.

Here is the code behind the button to launches the report. Something is
producing duplicates when one reviewer is choosen????

Private Sub cmdHMO_Click()
On Error GoTo Err_cmdHMO_Click

Dim stDocName As String

'these need to be declared
Dim stReviewerList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean
Dim stReviewer As Variant

stDocName = "r_KeyMembers"
stReviewerList = ""

'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "You must enter Start and End Dates"
Exit Sub
End If

'Reviewer
FirstTime = True
For Each stReviewer In ListReviewer.ItemsSelected
If FirstTime Then
stReviewerList = "In('" & ListReviewer.ItemData(stReviewer) & "'"
FirstTime = False
Else
stReviewerList = stReviewerList & ",'" & ListReviewer.ItemData(stReviewer) &
"'"
End If
Next stReviewer
If Not FirstTime Then
stReviewerList = stReviewerList & ")"
End If


'create criteria string
'stReviewerList
If Len(Trim(Nz(stReviewerList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[Reviewer] " & stReviewerList & " And """
End If

'now remove the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdHMO:
Exit Sub

Err_cmdHMO_Click:

Err_cmdHMO:
MsgBox "You must pick at least one reviewer"
Resume Exit_cmdHMO
End Sub