MultiListBox instead of Paramater query - Allen Browne code

  • Thread starter tiffany_fitzgerald
  • Start date
T

tiffany_fitzgerald

Hi! I have been struggling trying to get a MultiList box to replace a
Parameter dialog box, that is able to pass in values to a query that is
bound to a report. I have used the following code, but have a couple
of questions.

1. The multi-list box I am using only contains one text field -- do I
need to use all of the code listed below (I copied and pasted this
directly from a previous post courtesy of Allen Browne):

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

2. My form name has spaces in it - and when I try to use that after
the With statement below, VB won't handle it - what is the correct
syntax?

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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


3. Here is the entire code, in case you need to see the order, etc.
Any help would be MUCH APPRECIATED!!!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
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 = "by ba"

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

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
 
B

Brian Bastl

tiffany,

if the listbox is named 'BusinessArea1' then change:
'With Issues by Business Area.BusinessArea1'
to
With Me.BusinessArea1

Brian


Hi! I have been struggling trying to get a MultiList box to replace a
Parameter dialog box, that is able to pass in values to a query that is
bound to a report. I have used the following code, but have a couple
of questions.

1. The multi-list box I am using only contains one text field -- do I
need to use all of the code listed below (I copied and pasted this
directly from a previous post courtesy of Allen Browne):

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

2. My form name has spaces in it - and when I try to use that after
the With statement below, VB won't handle it - what is the correct
syntax?

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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


3. Here is the entire code, in case you need to see the order, etc.
Any help would be MUCH APPRECIATED!!!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
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 = "by ba"

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

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
Click to expand...
 
A

Allen Browne

Presumably we are talking about the code in this link:
http://allenbrowne.com/ser-50.html

To answer your specific questions:

1. Yes. You need to use all the code to loop through the ItemsSelected
collection of the list box and build up the WhereCondition.

2. The code is intended to go in the form's module, so you literally use
"Me." You do not need to use your form name. If you wanted to use your form
name for some reason, you would enclose it in square brackets since it
contains spaces, e.g.:
Forms![Issues by Business Area].BusinessArea1

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

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

Hi! I have been struggling trying to get a MultiList box to replace a
Parameter dialog box, that is able to pass in values to a query that is
bound to a report. I have used the following code, but have a couple
of questions.

1. The multi-list box I am using only contains one text field -- do I
need to use all of the code listed below (I copied and pasted this
directly from a previous post courtesy of Allen Browne):

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

2. My form name has spaces in it - and when I try to use that after
the With statement below, VB won't handle it - what is the correct
syntax?

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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


3. Here is the entire code, in case you need to see the order, etc.
Any help would be MUCH APPRECIATED!!!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
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 = "by ba"

'Loop through the ItemsSelected in the list box.
With Issues by Business Area.BusinessArea1
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
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

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Business Area] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

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
Click to expand...
 
T

tiffany_fitzgerald

Thank you both SO much! The report is now running, however not
returning any results --could you take a look at the query? I think
it's right, but I'm not sure why no results are showing. The query
returns the correct results when I run it on it's own, althought it
prompts me with a parameter box--is that right?
SELECT [BPC Issues].[Issue Title], [BPC Issues].[Issue Description],
[BPC Issues].Rating, [BPC Issues].[Initiating Group], [BPC
Issues].[Initiation Date], [BPC Issues].[Audit Number], [BPC
Issues].Documentation, [Action Plan].[Action Plan Description], [Action
Plan].[Business Area], [Action Plan].[Business Area 2], [Action
Plan].[BPC Contact], [Action Plan].[BPC Contact 2], [Action
Plan].[Target Date], [Action Plan].Status, [Action Plan].Comments
FROM [BPC Issues] INNER JOIN [Action Plan] ON [BPC Issues].[Tracking
Number] = [Action Plan].[Tracking Number]
WHERE ((([Action Plan].[Business Area]) IN
([Forms]![IssuesbyBusinessArea]![BusinessArea1]))) OR ((([Action
Plan].[Business Area 2]) IN
([Forms]![IssuesbyBusinessArea]![BusinessArea1])));
 
B

Brian Bastl

tiffany,

You may need to directly reply to Allen's post so that he sees it. He's much
more qualified to answer your question.

Brian
 
T

tiffany_fitzgerald

Thank you both SO much! The report is now running, however not
returning any results --could you take a look at the query? I think
it's right, but I'm not sure why no results are showing. The query
returns the correct results when I run it on it's own, althought it
prompts me with a parameter box--is that right?
SELECT [BPC Issues].[Issue Title], [BPC Issues].[Issue Description],
[BPC Issues].Rating, [BPC Issues].[Initiating Group], [BPC
Issues].[Initiation Date], [BPC Issues].[Audit Number], [BPC
Issues].Documentation, [Action Plan].[Action Plan Description], [Action

Plan].[Business Area], [Action Plan].[Business Area 2], [Action
Plan].[BPC Contact], [Action Plan].[BPC Contact 2], [Action
Plan].[Target Date], [Action Plan].Status, [Action Plan].Comments
FROM [BPC Issues] INNER JOIN [Action Plan] ON [BPC Issues].[Tracking
Number] = [Action Plan].[Tracking Number]
WHERE ((([Action Plan].[Business Area]) IN
([Forms]![IssuesbyBusinessArea]![BusinessArea1]))) OR ((([Action
Plan].[Business Area 2]) IN
([Forms]![IssuesbyBusinessArea]![BusinessArea1])));

Reply
 
A

Allen Browne

The code you are using generates the WhereCondition for OpenReport by
looping through the ItemsSelected in the multi-select list box.

You do not need the WHERE condition in the query as well. Try cutting
everything out of the query from WHERE to the end.

Then when you click your command button to open the report, it will build up
the string, and apply it as the report's Filter.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add code to handle All jobs FROm dropdown list if None selected- 5
Crosstab Query Building Report 2
Works, But Doesn't Filter 1
select child records from listbox and generate a report 0
Error 13 - Type Mismatch 5
Two Multi-Select List Boxes as Filters 11
Two multi-select list boxes 3
list box selection as filter 1

Top