SELECTION CRITERIA using COMBO and LISTBOX

[ZaZa]

Hi everyone:

I have a problem. I have a combo box called "SelectStatus" as follows:

SelectStatus
---------------
StatusID ( integer)

And a list box called "SelectSystem" as follows:

SelectSystem
------------
SystemID (text)


The liStbox can have multiple selections.

The user is supossed to select a status (ie 1,2, 3) from the combo box , then choose one or more systems from the List box as selction criteria for opening a report.

If the user chooses a status (ie 1) and then chooses two or more systems ( ie. system 3 and system 4) I want the report to show :
A)ALL RECORDS IN SYSTEM 3 THAT HAVE A SATUS OF 1 AND
B)ALL RECORDS IN SYSTEM 4 THAT HAVE A STATUS OF 1

What I am getting is:
A) All records in System 3 (for all statuses) And
B) All records in System 4 that have a status of 1


I have posted the code which I pieced together from Previous help on this forum. the code is on the on-click event of a command button on the form. I think that the error is in the criteria for the lsit box selection.

Please help is you can.

----------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant
Dim strCriteria As String

Set cntl = Me.SelectSystem
lngCounter = 0

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & "[SystemID] = '" & cntl.Column(0, varItem) & "'"
Else
strCriteria = strCriteria & "[SystemID] = '" & cntl.Column(0, varItem) & "' or"
End If
Next varItem

If Not IsNull(Me![SelectStatus]) Then
strCriteria = strCriteria & " And [StatusID]= " & Me![SelectStatus] & ""
Else
strCriteria = strCriteria
End If

DoCmd.OpenReport "Rpt02", acViewPreview, , strCriteria
------------------------

Thanks
ZaZa

 

[ZaZa]

Hi everyone,

I think I solved the problem. It turned out to be not so difficult afterall. I will post the code for the benefit of anyone who may encounter this problem.

---------------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant
Dim strCriteria As String

Set cntl = Me.SelectSystem
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & " [StatusID] = " &
Me![SelectStatus] & " And [SystemID] = '" &
cntl.Column(0, varItem) & "'"
Else
strCriteria = strCriteria & " [StatusID] = " &
Me![SelectStatus] & " And [SystemID] = '" &
cntl.Column(0, varItem) & "' or"
End If
Next varItem

Else
For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & " [SystemID] = '" &
cntl.Column(0, varItem) & "'"
Else
strCriteria = strCriteria & " [SystemID] = '" &
cntl.Column(0, varItem) & "' or"
End If
Next varItem
End If


DoCmd.OpenReport "Rpt02", acViewPreview, , strCriteria


'Clear the selections in the listbox after the report is opened.
Dim i As Integer

For i = 0 To (SelectSystem.ListCount - 1)
SelectSystem.Selected(i) = False
Next i


End Sub
---------------------------------------------

Regards,
ZaZa