By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Multiple Combo Boxes as well as multiple Multi select List Boxes toreport

P: n/a
How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?

I can get one Multi List Box, just not several, to report using this
code i found -
Private Sub cmdPreview_Click()

On Error GoTo Err_Handler

Dim varItem As Variant 'Selected items

Dim strWhere As String 'String to use as WhereCondition

Dim strDescrip As String 'Description of WhereCondition

Dim lngLen As Long 'Length of string

Dim strDelim As String 'Delimiter for this field type.

Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.

strDoc = "Products by Category"

With Me.lstCategory

For Each varItem In .ItemsSelected

If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","

'Build up the description from the text in the visible
column. See note 2.

strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "

End If

Next

End With

lngLen = Len(strWhere) - 1

If lngLen 0 Then

strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 2

If lngLen 0 Then

strDescrip = "Categories: " & Left$(strDescrip, lngLen)

End If

End If

If CurrentProject.AllReports(strDoc).IsLoaded Then

DoCmd.Close acReport, strDoc

End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:

Exit Sub

Err_Handler:

If Err.Number <2501 Then 'Ignore "Report cancelled" error.

MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"

End If

Resume Exit_Handler

End Sub

Any help would be greatly apprciated!

Many thanks

Michael
Nov 11 '08 #1
Share this Question
Share on Google+
12 Replies


P: n/a
No one can help me?
Nov 12 '08 #2

P: n/a
mi****@gmail.com wrote:
How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?

I can get one Multi List Box, just not several, to report using this
code i found -
Private Sub cmdPreview_Click()

On Error GoTo Err_Handler

Dim varItem As Variant 'Selected items

Dim strWhere As String 'String to use as WhereCondition

Dim strDescrip As String 'Description of WhereCondition

Dim lngLen As Long 'Length of string

Dim strDelim As String 'Delimiter for this field type.

Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.

strDoc = "Products by Category"

With Me.lstCategory

For Each varItem In .ItemsSelected

If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","

'Build up the description from the text in the visible
column. See note 2.

strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "

End If

Next

End With

lngLen = Len(strWhere) - 1

If lngLen 0 Then

strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 2

If lngLen 0 Then

strDescrip = "Categories: " & Left$(strDescrip, lngLen)

End If

End If

If CurrentProject.AllReports(strDoc).IsLoaded Then

DoCmd.Close acReport, strDoc

End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:

Exit Sub

Err_Handler:

If Err.Number <2501 Then 'Ignore "Report cancelled" error.

MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"

End If

Resume Exit_Handler

End Sub

Any help would be greatly apprciated!

Many thanks

Michael
You have a good start. Let's say you have 3 listboxes; List0, List1,
List2.

Dim strList0 As String
Dim strList1 As String
Dim strList2 As String
Dim strWhere As STring

Change strWhere in the code for the first listbox to strList0. Ex:
strWhere = strWhere & strDelim & .ItemData(varItem) &
to
strList0 = strList0 & strDelim & .ItemData(varItem) &

Now copy the code for scanning lstCategory selections then paste it
before the line
If CurrentProject.AllReports(strDoc).IsLoaded Then

Change that code to reflect the data from that listbox and assign it to
strList1

Keep copying/pasting and updating the code for the listbox until all
listbox selections are complete. Then do something like
If strList0 "" then strWhere = strWhere & strList0 & " And "
If strList1 "" then strWhere = strWhere & strList1 & " And "
If strList2 "" then strWhere = strWhere & strList2 & " And "
strWhere = Left(strWhere,len(strWhere)-5) 'remove trailing And

For debugging purposes I might put a line like
Debug.print strWhere
so you can view the final result

Then call the report.
Nov 12 '08 #3

P: n/a
Thanks for the reply. I don't know how this code would fit in.

If i had one Combo Box called CountrySelect and another called
GroupSelect to print to a report called rptContact what code would i
use?
The button i have set up to print the report uses the selected fields
to filter data, i currently have -

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptContact"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ContactID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Nov 13 '08 #4

P: n/a
I've tried following your instructions but this code doesn't work -

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strCountrySelect As String
Dim strCategorySelect As String
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptContact"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelect = strCountrySelect & strDelim
& .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[CountryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If strCountrySelect "" Then strWhere = strWhere &
strCountrySelect & " And "
If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
strWhere = Left(strWhere, Len(strWhere) - 2)

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Nov 13 '08 #5

P: n/a
mi****@gmail.com wrote:
Thanks for the reply. I don't know how this code would fit in.

If i had one Combo Box called CountrySelect and another called
GroupSelect to print to a report called rptContact what code would i
use?
The button i have set up to print the report uses the selected fields
to filter data, i currently have -

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptContact"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ContactID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
What is strDelim set to?

If using IN, a string should be surrounded in ' or ". Ex
Country In ("England","France")
Country In ('England','France')
If numeric, like an ID number
CountryID In (1,2)

If a combo, there's only 1 selection. In listboxes, depending on the
setup, you can have 1 or many. I suppose you have it set up to Simple
or Extended for many options.

Use a ' or " to surround strings. Use # for dates. Nothing for numbers.

Here's some sample code to get you going.
Private Sub cmdPreview_Click()

Dim var As Variant
Dim strWhere As String
Dim strC As String 'Country
Dim strG As String 'Grooup
Dim strX As STring 'Combobox
Dim strQ As STring

strDoc = "rptContact"
strQ = """"

'string example. Gets second column.
With Me.CountrySelect
For Each var In .ItemsSelected
strC = strC & strQ & .Column(1,var) & strQ & ","
Next
strC = "Country IN (" & Left(strC,len(strC)-1) & ")"
End With

'numeric example. Gets first column.
With Me.GroupSelect
For Each var In .ItemsSelected
strG = strG & .Column(0,var) & ","
Next
strG = "GroupID IN (" & Left(strG,len(strG)-1) & ")"
End With

'Combo box example as a string value (uses a ' to surround string)
If not IsNull(Me.COmboboxName) then
strX = "SomeFldName = '" & Me.ComboBox & "'"
Endif

If strC "" Then strWhere = strC & " And "
If strG "" then strWhere = strWhere & strG & " And "
If strX "" then strWhere = strWhere & strX & " And "

'remove trailing " And " in strWhere
If strWhere "" then strWhere = Left(strWhere,len(strWhere)-5)

'next 2 lines for testing purposes only
msgbox strWhere
Debug.print strWhere

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere
End Sub
Nov 13 '08 #6

P: n/a
All the Listboxes are Simple Multi Listboxes.

Users will be adding fields that will appear in these listboxes, so
it's impossible to sepcify the Variables, ie Country In
("England","France") or Country In ("1","2"). I want users
will be able to select multiple Countries and Multiple Categories and
the report to only pull through Appropriate Contacts.

So i have this code, which i still can't get to work, though i'm sure
the solution is easy enough:-

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelect As String
Dim strCategorySelect As String
Dim strWhere As String

strDoc = "rptContact"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelect = strCountrySelect & strDelim
& .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountrySelect) - 1
If lngLen 0 Then
strCountrySelect = "[CountryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

With Me.CategorySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySelect = strCategorySelect &
strCategorySelect & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategorySelect) - 1
If lngLen 0 Then
strCategorySelect = "[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
If strCountrySelect "" Then strWhere = strWhere &
strReligionSelect & " And "
If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub
Nov 13 '08 #7

P: n/a
mi****@gmail.com wrote:
All the Listboxes are Simple Multi Listboxes.

Users will be adding fields that will appear in these listboxes, so
it's impossible to sepcify the Variables, ie Country In
("England","France") or Country In ("1","2"). I want users
will be able to select multiple Countries and Multiple Categories and
the report to only pull through Appropriate Contacts.

So i have this code, which i still can't get to work, though i'm sure
the solution is easy enough:-
First thing, do you even know what the value of strWhere is before you
open the report from your code below?

Are you getting errors? Then comment out your OnError line.

Do you even know if the data from strWhere is correct?

Are you collecting number or alphas when you create the criteria?

Do you know how to concatenate strings together?

What is strReligion? Pray to Jesus that it might work and hope for a
miracle?

Why do you mix/match ItemData and Column in your For/Nexts?

I have no idea what "i still can't get to work" means. This isn't a
mindreaders club. You really need to spend some time and THINK!
>
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelect As String
Dim strCategorySelect As String
Dim strWhere As String

strDoc = "rptContact"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelect = strCountrySelect & strDelim
& .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountrySelect) - 1
If lngLen 0 Then
strCountrySelect = "[CountryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

With Me.CategorySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySelect = strCategorySelect &
strCategorySelect & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategorySelect) - 1
If lngLen 0 Then
strCategorySelect = "[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
If strCountrySelect "" Then strWhere = strWhere &
strReligionSelect & " And "
If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub
Nov 13 '08 #8

P: n/a
Thanks again for your reply.

I am not reporting on just one Listbox that has several columns.

I want to print a report based on more than 1 multi select listbox.
One list box is called CategorySelect with CategoryID as the selection
field
The other is called CountrySelect with CountryID as the selection
field

I can base the report on CountrySelect using the code i posted first.

Can you base a report on more than one Multi Select Listbox?

Thanks
Nov 13 '08 #9

P: n/a
mi****@gmail.com wrote:
Thanks again for your reply.

I am not reporting on just one Listbox that has several columns.

I want to print a report based on more than 1 multi select listbox.
One list box is called CategorySelect with CategoryID as the selection
field
The other is called CountrySelect with CountryID as the selection
field

I can base the report on CountrySelect using the code i posted first.

Can you base a report on more than one Multi Select Listbox?

Thanks
Yes, you can.

I'd suggest studying the code I posted earlier this morning. Use that
as a "template" for you to experiment with. I'd suggest knowing what
the value is of strWhere as a filter prior to passing it to the report.
I'd suggest you compile the code. I'd suggest you debug.print the
strWhere and see if that filter works in the report by opening the
report and seeing if that filter works.
Nov 13 '08 #10

P: n/a
On Thu, 13 Nov 2008 04:49:11 -0800 (PST), mi****@gmail.com wrote:
>Thanks again for your reply.

I am not reporting on just one Listbox that has several columns.

I want to print a report based on more than 1 multi select listbox.
One list box is called CategorySelect with CategoryID as the selection
field
The other is called CountrySelect with CountryID as the selection
field

I can base the report on CountrySelect using the code i posted first.

Can you base a report on more than one Multi Select Listbox?

Thanks
Yes you can. I'll bet that some of the professional database programmers in
this news group will have examples in their web sites. However, there is a way
to accomplish this without writing a lot of code because I don't write code.

You can make several intermediate tables. Get your data for the first list box
from your main table. Append data to intermediate table 1 (with append query)
based on the selections of the first list box. Get your data for the second
list box from intermediate table 1. Append data to intermediate table 2 based
on the selections of the second list box. Get your data for the third list box
from intermediate table 2. etc. This is not pretty, but it works. Before
starting a new set of selections, delete all data from all intermediate tables
but leave the empty tables in the database so they don't have to be created
every time you use them. I have a small (228KB) sample database (A97) that
uses 3 cascading listboxes. It can be zipped a little smaller. I stopped
sending zipped files to folks who don't know me because they don't know if a
virus is getting into my zipped files. I can send it as an attachment in E-mail
if you want, either straight or zipped.

Chuck

Nov 13 '08 #11

P: n/a
Excellent. Thanks again for your help. I see where i hadn't changed
the strWhere to strCountry.

Now working with code:-
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelect As String
Dim strCategorySelect As String
Dim strWhere As String
strDoc = "rptContactReport"

With Me.CountrySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelect = strCountrySelect & strDelim
& .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountrySelect) - 1
If lngLen 0 Then
strCountrySelect = "[CountryID] IN (" & Left$
(strCountrySelect, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

With Me.CategorySelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySelect = strCategorySelect &
strCategorySelect & strDelim & .ItemData(varItem) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategorySelect) - 1
If lngLen 0 Then
strCategorySelect = "[CategoryID] IN (" & Left$
(strCategorySelect, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

If strCountrySelect "" Then strWhere = strWhere &
strCountrySelect & " And "
If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub
Nov 13 '08 #12

P: n/a
Hi,
1. What is the value of strDelim??? since you are looking up text(I
assume) you need at a minimum a single quot(tick) mark around each
text value and I don't see that in your code.

2. Have you stepped through this code in 'debug' mode and looked at(in
immediate window) the string it builds? if so post it.

bobh.
On Nov 13, 9:42*am, mic...@gmail.com wrote:
Excellent. Thanks again for your help. I see where i hadn't changed
the strWhere to strCountry.

Now working with code:-
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

* * Dim varItem As Variant
* * Dim strDescrip As String
* * Dim lngLen As Long
* * Dim strDelim As String
* * Dim strDoc As String
* * Dim strCountrySelect As String
* * Dim strCategorySelect As String
* * Dim strWhere As String
* * strDoc = "rptContactReport"

* * With Me.CountrySelect
* * * * For Each varItem In .ItemsSelected
* * * * * * If Not IsNull(varItem) Then
* * * * * * * * strCountrySelect = strCountrySelect & strDelim
& .ItemData(varItem) & strDelim & ","
* * * * * * * * *strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
* * * * * * End If
* * * * Next
* * End With

* * lngLen = Len(strCountrySelect) - 1
* * If lngLen 0 Then
* * * * strCountrySelect = "[CountryID] IN (" & Left$
(strCountrySelect, lngLen) & ")"
* * * * lngLen = Len(strDescrip) - 2
* * * * If lngLen 0 Then
* * * * * * strDescrip = "Categories: " & Left$(strDescrip,lngLen)
* * * * End If
* * End If

* * With Me.CategorySelect
* * * * For Each varItem In .ItemsSelected
* * * * * * If Not IsNull(varItem) Then
* * * * * * * * strCategorySelect = strCategorySelect &
strCategorySelect & strDelim & .ItemData(varItem) & strDelim & ","
* * * * * * * * *strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
* * * * * * End If
* * * * Next
* * End With

* * lngLen = Len(strCategorySelect) - 1
* * If lngLen 0 Then
* * * * strCategorySelect = "[CategoryID] IN (" & Left$
(strCategorySelect, lngLen) & ")"
* * * * lngLen = Len(strDescrip) - 2
* * * * If lngLen 0 Then
* * * * * * strDescrip = "Categories: " & Left$(strDescrip,lngLen)
* * * * End If
* * End If

* * * * If strCountrySelect "" Then strWhere = strWhere &
strCountrySelect & " And "
* * * * If strCategorySelect "" Then strWhere = strWhere &
strCategorySelect & " And "
* * * * If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)

* * If CurrentProject.AllReports(strDoc).IsLoaded Then
* * * * DoCmd.Close acReport, strDoc
* * End If

* *DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
* * Exit Sub

Err_Handler:
* * If Err.Number <2501 Then
* * * * MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
* * End If
* * Resume Exit_Handler
Debug.Print strWhere
End Sub
Nov 14 '08 #13

This discussion thread is closed

Replies have been disabled for this discussion.