running reports based on two list boxes

[Samt]

I have two list boxes, one entitled source(lstsource) and one called position(lstposition). I have populated them with information from a table, and i want to be able to choose specific items from the list and run a report based on teh filtered selections. I successfully configured the first list so it runs a report based on my selections, but the second lsit box runs all of the items in the list rather than the ones i choose. here is my query

Private Sub cmdresumefilter_Click()
Dim strFilterString As String
Dim varSelectedItem As Variant
Dim intCurrentCount As Integer
Dim stDocName As String

intCurrentCount = 1

For Each varSelectedItem In Me.lstsource.ItemsSelected
strFilterString = strFilterString & "(Source = '" & Me.lstsource.ItemData(varSelectedItem) & "')AND( Position = '" & Me.lstposition.ItemData(varSelectedItem) & "')"



If Not (intCurrentCount = Me.lstsource.ItemsSelected.Count) And (intCurrentCount = Me.lstposition.ItemsSelected.Count) Then
strFilterString = strFilterString & " Or "
End If


intCurrentCount = intCurrentCount + 1

Next varSelectedItem

For Each varSelectedItem In Me.lstposition.ItemsSelected
strFilterString = strFilterString & "(Source = '" & Me.lstsource.ItemData(varSelectedItem) & "')AND( Position = '" & Me.lstposition.ItemData(varSelectedItem) & "')"

Next varSelectedItem

stDocName = "New Resume Query1"
DoCmd.OpenReport stDocName, acViewPreview, , strFilterString

End Sub

 

[Samt]

I Solved the above problem, but now i have a problem with running a report based on a selection from EACH list box. i changed my query to:

Private Sub cmdresumefilter_Click()
Dim strFilterString As String
Dim varSelectedItem As Variant
Dim intCurrentCount As Integer
Dim stDocName As String

intCurrentCount = 1

For Each varSelectedItem In Me.lstsource.ItemsSelected
strFilterString = strFilterString & "(Source = '" & Me.lstsource.ItemData(varSelectedItem) & "')"



If Not (intCurrentCount = Me.lstsource.ItemsSelected.Count) Then
strFilterString = strFilterString & " Or "
End If


intCurrentCount = intCurrentCount + 1

Next varSelectedItem

For Each varSelectedItem In Me.lstposition.ItemsSelected
strFilterString = strFilterString & "(Position = '" & Me.lstposition.ItemData(varSelectedItem) & "')"

If Not (intCurrentCount = Me.lstposition.ItemsSelected.Count) Then
strFilterString = strFilterString & " Or "
End If

intCurrentCount = intCurrentCount + 1
Next varSelectedItem

stDocName = "New Resume Query1"
DoCmd.OpenReport stDocName, acViewPreview, , strFilterString

End Sub


When i run it, it gives an error message:

Run time error '3075'
Extra ) in query expression '((source ='c')(Position='e')Or)'
i put in dummy data into my table, thats why source = c and position = e

 

[boblarson]

Actually, try this instead -

Private Sub cmdresumefilter_Click()
Dim strFilterString As String
Dim varSelectedItem As Variant
Dim intCurrentCount As Integer
Dim stDocName As String

intCurrentCount = 1
If Me.lstsource.ItemsSelected.Count > 0 Then
strFilterString = “[Source] In(“
   For Each varSelectedItem In Me.lstsource.ItemsSelected

      strFilterString = strFilterString & Chr(34) &  Me.lstsource.ItemData(varSelectedItem) & Chr(34) & “,”

   Next varSelectedItem

End If
strFilterString = Left(strFilterString, Len(strFilterString)- 1) & “)”

If Me.lstposition.ItemsSelected.Count > 0
   If strFilterString <> “” Then
      strFilterString = strFilterString & “ And “ 
   End If

strFilterString = strFilterString & “[Position] In(“

   For Each varSelectedItem In Me.lstposition.ItemsSelected
      strFilterString = strFilterString & Chr(34) &  Me.lstposition.ItemData(varSelectedItem) & Chr(34) & “,”

   Next varSelectedItem
   
   strFilterString = Left(strFilterString, Len(strFilterString)-1) & “)”

End If

stDocName = "New Resume Query1"
DoCmd.OpenReport stDocName, acViewPreview, , strFilterString

End Sub

 

[Samt]

That worked!! Thank you soo much!!
-Sam

 

[boblarson]

That worked!! Thank you soo much!!
-Sam

No problem, glad we could help

.