help with multiple select list box

[mrathi]

Hi,
I have a list box that will show (list of reports) items based on a query. My current list box is not a multiple selection. I have a button, so that a user can select an item from the list and view that particular report. Here is the current code:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Dim CRType
CRType = Me!List1

If IsNull(CRType) Or CRType = "" Then
MsgBox "You have not selected a Report from the list."
Else

DoCmd.OpenReport "MrptEULiquidStorage", acViewPreview, , "EmissionUnitId = '" & List1 & "'"
End If

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Is there a way, I can implement this with multiple select? I would appreciate a detailed response.

Thanks and have a Happy Thanksgiving

 

[MakeItSo]

Try my FAQ (701-4432) about using multi-select listboxes as query criteria. This might give you some code to work with.
;-)
Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[mrathi]

I used the follwoing code:
Dim X As Variant
For Each X In List1.ItemsSelected
DoCmd.OpenReport "MrptEUMiscellaneous", acViewPreview, , "EmissionUnitId = '" & List1 & "'"
Next

However, it gives the error message of no data for the report. Please help.

Thanks

 

[MakeItSo]

The error lies here:
...'" & List1 & "'"
replace it with this:
...'" & List1.ItemData(X) & "'"

BTW: Does that work when working with the "List1" listbox directly?
I always had to "clone" it:
Dim ctlList
Set cltList=List1
...
For Each X in ctlList.ItemsSelected

makeItSO


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[mrathi]

Thanks for your reply. I changed to what you said, however, now it just opens the first selected report and not the next ones.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim X As Variant
For Each X In List1.ItemsSelected
DoCmd.OpenReport "MrptEUMiscellaneous", acViewPreview, , "EmissionUnitId = '" & List1.ItemData(X) & "'"
Next

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Error$
Resume Exit_Command2_Click

End Sub

 

[mrathi]

I don't believe this, now with the same code, no matter what the selections is, it opens the MrptEUMiscellaneous for all the EmissionUnitIds. Please help.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim X As Variant
For Each X In List1.ItemsSelected
DoCmd.OpenReport "MrptEUMiscellaneous", acViewPreview, "EmissionUnitId = '" & List1.ItemData(X) & "'"
Next

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Error$
Resume Exit_Command2_Click

End Sub

 

[MakeItSo]

Should have recognized earlier - you are trying to open multiple instances of the same report - but the report is a mere container. It can only be filled with different records each time you open it.

Why do you want to display all records anyway? That's what forms are for, not reports. If you want to print the report, open it in acViewNormal, or send / save each report as rtf or HTML or Snapshot instead of displaying it.
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Qwert0000]

I use this code to do what your trying to do.

It prints reports only on selected values.


If CboSelectIndividualReport.ListCount = 0 Then
MsgBox "Please Select Member(s)", vbOKOnly, "Try Again"
Exit Sub
End If

For SelectCount = 0 To CboSelectIndividualReport.ListCount - 1
If CboSelectIndividualReport.Selected(SelectCount) Then
stFilter = stFilter & "SSN = '" & CboSelectIndividualReport.Column(0, SelectCount) & "' OR "
End If
Next
stFilter = Left(stFilter, Len(stFilter) - 4)

 

[Qwert0000]

Right after I posted I reliazed I made a mistake and grabbed the wrong piece of code. My coding isnt pretty but it seems to work. My CboSelectIndividualReport is actually a multiselect extended list box that originally started out as a combo box, never renamed it is all. Hope this helps

Eric.


Private Sub CmdPrintIndReport_Click()
Dim SelectCount As Integer
Dim totalselect As Integer
Dim stDocName As String
Dim stFilter As String
Dim Result as Integer

On Error GoTo Err_CmdPrintIndReport_Click

For SelectCount = 0 To CboSelectIndividualReport.ListCount - 1
If CboSelectIndividualReport.Selected(SelectCount) Then
totalselect = totalselect + 1
End If
Next
If totalselect = 0 Then
MsgBox "Please Select Member(s)", vbOKOnly, "Try Again"
Exit Sub
End If

For SelectCount = 0 To CboSelectIndividualReport.ListCount - 1
If CboSelectIndividualReport.Selected(SelectCount) Then
stFilter = stFilter & "SSN = '" & CboSelectIndividualReport.Column(0, SelectCount) & "' OR "
End If
Next
stFilter = Left(stFilter, Len(stFilter) - 4)


Result = MsgBox("You are about to print " & totalselect & " reports", vbOKCancel, "Confirm&quot
If Result = 2 Then
Exit Sub
End If

stDocName = "StudentReport"

DoCmd.OpenReport stDocName, acNormal, , stFilter


Exit_CmdPrintIndReport_Click:
Exit Sub

Err_CmdPrintIndReport_Click:
MsgBox Err.Description
Resume Exit_CmdPrintIndReport_Click

End Sub

 

[mrathi]

Thank you so much Eric, your code works just great. I appreciate it.