Reports-Multi-Select List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I use a multi-select Listbox to select multiple reports to print? I
know this is a tricky procedure if you can help I appreciate it.
 
Loop through the ItemsSelected collection of the list box, using OpenReport
for each one.
 
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" & Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i
 
That code looks like it is aimed at opening one report with a range of
values.

If I understood your original request, you wanted to open a number of
different reports. Totally different. You don't need to build up a criteria
string; you need to OpenReport for each one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" &
Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i

Allen Browne said:
Loop through the ItemsSelected collection of the list box, using
OpenReport
for each one.
Click to expand...
Click to expand...
 
Try this:
Dim ctl As Control
Dim varItem As Variant
Dim strSql As String

Set ctl = Me![lstSummaries]
strSql = "[SummaryID] IN ("
'Assume [SummaryID] is the bound field in listbox
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSql = strSql & ctl.ItemData(varItem) & ", "
Next varItem

'If no category is selected, make it null in the criteria.
If strSql = "[CategoryID] IN (" Then
DoCmd.OpenReport stDocName, acPreview
Else
strSql = Left$(strSql, Len(strSql) - 2) & ")"
DoCmd.OpenReport "Summary Report", acPreview, , strSql
End If

Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" & Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i

Allen Browne said:
Loop through the ItemsSelected collection of the list box, using OpenReport
for each one.
Click to expand...
Click to expand...
 
I'm sorry, I'll use the Northwind Database as the example. There is a
listbox that list all orders I want to be able to have the ability to select
several orders from the listbox and print them.



Allen Browne said:
That code looks like it is aimed at opening one report with a range of
values.

If I understood your original request, you wanted to open a number of
different reports. Totally different. You don't need to build up a criteria
string; you need to OpenReport for each one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" &
Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i

Allen Browne said:
Loop through the ItemsSelected collection of the list box, using
OpenReport
for each one.

How do I use a multi-select Listbox to select multiple reports to
print?
I
know this is a tricky procedure if you can help I appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry, ngan Allan is right I do want to open a number of different
reports. Totally different.

ngan said:
Try this:
Dim ctl As Control
Dim varItem As Variant
Dim strSql As String

Set ctl = Me![lstSummaries]
strSql = "[SummaryID] IN ("
'Assume [SummaryID] is the bound field in listbox
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSql = strSql & ctl.ItemData(varItem) & ", "
Next varItem

'If no category is selected, make it null in the criteria.
If strSql = "[CategoryID] IN (" Then
DoCmd.OpenReport stDocName, acPreview
Else
strSql = Left$(strSql, Len(strSql) - 2) & ")"
DoCmd.OpenReport "Summary Report", acPreview, , strSql
End If

Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" & Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i

Allen Browne said:
Loop through the ItemsSelected collection of the list box, using OpenReport
for each one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I use a multi-select Listbox to select multiple reports to print?
I
know this is a tricky procedure if you can help I appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
Will this do it?

Dim i As Variant

For Each i In Me![lstSummaries].ItemsSelected
DoCmd.OpenReport "Summary Report", acPreview
Next i

Tru said:
I'm sorry, ngan Allan is right I do want to open a number of different
reports. Totally different.

ngan said:
Try this:
Dim ctl As Control
Dim varItem As Variant
Dim strSql As String

Set ctl = Me![lstSummaries]
strSql = "[SummaryID] IN ("
'Assume [SummaryID] is the bound field in listbox
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSql = strSql & ctl.ItemData(varItem) & ", "
Next varItem

'If no category is selected, make it null in the criteria.
If strSql = "[CategoryID] IN (" Then
DoCmd.OpenReport stDocName, acPreview
Else
strSql = Left$(strSql, Len(strSql) - 2) & ")"
DoCmd.OpenReport "Summary Report", acPreview, , strSql
End If

Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" & Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, , "[SummaryID]=Criteria
Next i

:

Loop through the ItemsSelected collection of the list box, using OpenReport
for each one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I use a multi-select Listbox to select multiple reports to print?
I
know this is a tricky procedure if you can help I appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
Take a look at:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

It is similar to what yo posted, but gives more detailed steps on how to set
it up, and notes on how to handle the places where it can go wrong (data
types, hidden columns, etc.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tru said:
I'm sorry, I'll use the Northwind Database as the example. There is a
listbox that list all orders I want to be able to have the ability to
select
several orders from the listbox and print them.



Allen Browne said:
That code looks like it is aimed at opening one report with a range of
values.

If I understood your original request, you wanted to open a number of
different reports. Totally different. You don't need to build up a
criteria
string; you need to OpenReport for each one.


Tru said:
Allen can you tell me what is wrong with code.

Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstSummaries].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[SummaryID]='" &
Me![lstSummaries].ItemData(i)
& "'"

'Open Report
DoCmd.OpenReport "Summary Report", acPreview, ,
"[SummaryID]=Criteria
Next i

:

Loop through the ItemsSelected collection of the list box, using
OpenReport
for each one.

How do I use a multi-select Listbox to select multiple reports to
print?
I
know this is a tricky procedure if you can help I appreciate it.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top