ItemsSelected

[Guest]

The SQL below is the event for a command button.

As you can see a report is generated based on the date range and the
reviewer picked from a list box (called ListReviewer).

The report gives a total count of records for that reviewer.

Problem is: If the reviewer had 7 records, instead of one page with the
totals, it produces 7 pages with the same information?

Please take a look at this code and see if I'm loop wrong or something to
produce seperate reports for each record...

Thanks
////////////////////////////////////////////////////////////////
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

 

[John W. Vinson]

Problem is: If the reviewer had 7 records, instead of one page with the
totals, it produces 7 pages with the same information?

Please take a look at this code and see if I'm loop wrong or something to
produce seperate reports for each record...

Try stepping through the code: set a breakpoint by opening the code in the VBA
editor and clicking in the vertical bar to the left (a red dot will show which
line is the breakpoint).

Run the code from the form, and see what is actually being inserted into
stLinkCriteria.

John W. Vinson [MVP]