More Problems with List Box

[richard]

Hi

Having some problems running a report with a list box selection. Below is
the code I am using.
The code should create a criteria (in the field Seperator) to run a report
based upon a query, however this is not happening. If I enter the criteria
directly into the query and then run the query it works fine, so it would
appear the criteria is not being passed to the query.
The problem is the report runs and shows all records from the table
regardless of the selection in the list box

Private Sub cmdRunReport_Click()

Dim varItem As Variant
Dim strCriteria As String
Dim strAssistFMJobs As String
Dim stDocName As String
Dim ctrl As Control

Set ctrl = Me.lstAssistFMJobs



For Each varItem In ctrl.ItemsSelected
strAssistFMJobs = strAssistFMJobs & "," & ctrl.ItemData(varItem)
Next varItem

strAssistFMJobs = Mid(strAssistFMJobs, 2)
strCriteria = "Seperator In(" & strAssistFMJobs & ")"


If Len(strAssistFMJobs) = 0 Then
MsgBox "You did not select anything from the list", vbExclamation,
"Nothing to find!"
Exit Sub
End If

stDocName = "Data Sheet for Clients"
DoCmd.OpenReport stDocName, acViewPreview, strCriteria

End Sub

Thanks

Richard

 

[Rick Brandt]

Having some problems running a report with a list box selection.
Below is the code I am using.
The code should create a criteria (in the field Seperator) to run a
report based upon a query, however this is not happening. If I enter
the criteria directly into the query and then run the query it works
fine, so it would appear the criteria is not being passed to the
query. [snip]

stDocName = "Data Sheet for Clients"
DoCmd.OpenReport stDocName, acViewPreview, strCriteria

You are putting your criteria in the Filter argument. It belongs in the Where
argument. Just add one more comma...

DoCmd.OpenReport stDocName, acViewPreview,, strCriteria

 

[Pat Hartman]

Seperator is spelled incorrectly. The correct spelling is Separator.