records per list

[Dan @BCBS]

The code below belongs to a command button, it returns records based on
choices from a list box (Reviewer).

I get this nasty pop-up window saying [Reviewer] In('John Doe')
I click ok and the report shows all errors. The report is fine I use it in
other procedures.

Why am I getting this nasty pop-up???



Private Sub cmdReviewer_Click()
On Error GoTo Err_cmdReviewer_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_ReviewerMonthly"
stReviewerList = ""

'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get reviewer in ListReviewer
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)

'-------------------------
' for debugging - delete after code runs without errors
MsgBox stLinkCriteria
'-------------------------

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

Exit_cmdReviewer:
Exit Sub

Err_cmdReviewer_Click:

Err_cmdReviewer:
MsgBox Err.Description
Resume Exit_cmdReviewer

End Sub

 

[Dale Fye]

Because you have the following set of instructions in your code:

'-------------------------
' for debugging - delete after code runs without errors
MsgBox stLinkCriteria
'-------------------------

and you have not either remarked out or deleted the msgbox line.

HTH
Dale

 

[Dan @BCBS]

Yes this is true - that is why I get the message - which answers my question.
But I added in an attempt to find out why I get to results (error on every
line of the report).

I hope you did not spend much time looking at that!!

I need to reform my question and address the error issue better.

Thanks

Because you have the following set of instructions in your code:

'-------------------------
' for debugging - delete after code runs without errors
MsgBox stLinkCriteria
'-------------------------

and you have not either remarked out or deleted the msgbox line.

HTH
Dale

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

The code below belongs to a command button, it returns records based on
choices from a list box (Reviewer).

I get this nasty pop-up window saying [Reviewer] In('John Doe')
I click ok and the report shows all errors. The report is fine I use it in
other procedures.

Why am I getting this nasty pop-up???



Private Sub cmdReviewer_Click()
On Error GoTo Err_cmdReviewer_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_ReviewerMonthly"
stReviewerList = ""

'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get reviewer in ListReviewer
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)

'-------------------------
' for debugging - delete after code runs without errors
MsgBox stLinkCriteria
'-------------------------

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

Exit_cmdReviewer:
Exit Sub

Err_cmdReviewer_Click:

Err_cmdReviewer:
MsgBox Err.Description
Resume Exit_cmdReviewer

End Sub

 

[Bob Quintal]

Yes this is true - that is why I get the message - which answers
my question. But I added in an attempt to find out why I get to
results (error on every line of the report).

I hope you did not spend much time looking at that!!

I need to reform my question and address the error issue better.

Thanks

the IN keyword in SQL is not a function, there must be a space

between the IN and the ( which follows it.

Personally, I'd rewrite your reviewer code as follows

stReviewer = ""
For Each stReviewer In ListReviewer.ItemsSelected
stReviewerList = stReviewerList & _
",'" & ListReviewer.ItemData & "'"
Next stReviewer
if len(trim(stReviewerList)) > 1
stReviewerList = "[REVIEWER] IN (" & mid(stReviewerList,2) & ") "
End If

 

[John W. Vinson]

The code below belongs to a command button, it returns records based on
choices from a list box (Reviewer).

I get this nasty pop-up window saying [Reviewer] In('John Doe')
I click ok and the report shows all errors. The report is fine I use it in
other procedures.

What's actually in the Reviewer field in the table? Is it perhaps a Lookup
Field - in which case it actually contains a hidden numeric key, while
appearing to contain 'John Doe'?

John W. Vinson [MVP]