How to open a report based on a multi-select listBox

[Frederick Wilson]

Hello All,

I think I have the right idea but the wrong order.

I have an unbound form for the user to select or input information for
the report.

I would normally set the criteria for the record source on the report to
equal to values in the form. However now I have used a multi-select
listbox. I know I can cycle through the items selected and place them in
a string.

I guess my question is, if I build a sql string on the form can I pass
it to a report when it is opening?

 

[Ken Snell [MVP]]

If you're using ACCESS 2002 or 2003, yes, you can pass the SQL string in the
OpenArgs argument of a DoCmd.OpenReport command. Although I'm not sure what
you plan to do with the SQL statement then; I suppose you could use it in
the report's Open event procedure to filter the report's recordsource.

However, if you just want to filter the report when it's opened, use the
fourth argument (the WhereCondition argument) of the DoCmd.OpenReport method
to pass the filtering string. That filtering string would be a WHERE clause
construction without the WHERE word.

 

[Frederick Wilson]

So let me get this right
with the where clause in the openreport I would do something like
Field1=something1 or something2, field2=something1 or something2.

I do not have any code because I do not know how to do this. I know what
I want to do but I can not figure it out.

I have a report setup that gets everything from qrySignUpReport

I have an unbound form frmSignUpReport with txtTitle, txtDate, ckActive,
lstGroupsSelect.

The user types a name for the sign up in txtTitle
The user types a date for the sign up in txtDate
ckActive is a checkbox which will be used to match a checkbox in a
personnel list. If the person in tblPersonnel is active they have a
checkbox.

Finally, the list box has two columns. I have my personnel broken down
in groups such as females, males and further defined as young, middle
age, seniors SOOOO

you can have Female - young and/or female - middle age and so one.

The idea of the list box is that you can select any of the combinations.

I was thinking that the query would be something like
field1=lstGroupsSelect.column (0)
field2=lstGroupsSelect.column (1)

but when you can have a multi select I am not sure how to deal with it.

Thanks,
Fred

 

[Ken Snell [MVP]]

I admit that I don't fully "see" your setup in my head, but what you would
"build" as the WHERE clause from your multiselect listbox might look
somethinng like this:

Dim strWhere As String
strWhere = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And [Field2]=" & Me.lstGroupsSelect.Column(1)
DoCmd.OpenReport "ReportName", , , strWhere


If you have multiple values for a single field, then something like this as
you cycle through your listbox's selections:

Dim strWhere_Field1 As String, strWhere_Field2 As String
Dim strWhere As String
Dim lngLoop As Long
strWhere_Field1 = ""
strWhere_Field2 = ""
For lngLoop = 0 To Me.lstGroupsSelect.ListCount - 1
If Me.lstGroupsSelect.Selected(lngLoop) = True Then
strWhere_Field1 = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And "
strWhere_Field2 = "[Field2]=" & Me.lstGroupsSelect.Column (1) & _
" And "
End If
Next lngLoop
If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
Len(strWhere_Field1) - 5)
strWhere = strWhere_Field1 & " And " & strWhere_Field2
DoCmd.OpenReport "ReportName", , , strWhere

 

[Fred Wilson]

I admit that I don't fully "see" your setup in my head, but what you would
"build" as the WHERE clause from your multiselect listbox might look
somethinng like this:

Dim strWhere As String

What are these lines for?

If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
Len(strWhere_Field1) - 5)

What are you striping from the string?

Thanks Ken, I really appreciate this.

Fred

strWhere = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And [Field2]=" & Me.lstGroupsSelect.Column(1)
DoCmd.OpenReport "ReportName", , , strWhere


If you have multiple values for a single field, then something like this as
you cycle through your listbox's selections:

Dim strWhere_Field1 As String, strWhere_Field2 As String
Dim strWhere As String
Dim lngLoop As Long
strWhere_Field1 = ""
strWhere_Field2 = ""
For lngLoop = 0 To Me.lstGroupsSelect.ListCount - 1
If Me.lstGroupsSelect.Selected(lngLoop) = True Then
strWhere_Field1 = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And "
strWhere_Field2 = "[Field2]=" & Me.lstGroupsSelect.Column (1) & _
" And "
End If
Next lngLoop
If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
Len(strWhere_Field1) - 5)
strWhere = strWhere_Field1 & " And " & strWhere_Field2
DoCmd.OpenReport "ReportName", , , strWhere

 

[Ken Snell [MVP]]

You'll note that I concatenate the characters
(space)And(space)
at the end of each addition of text to the string variables (strWhere_Field1
and strWhere_Field2). So, at the end, it's necessary to strip off the
trailing " And " characters from each string.

--

Ken Snell
<MS ACCESS MVP>

I admit that I don't fully "see" your setup in my head, but what you
would "build" as the WHERE clause from your multiselect listbox might
look somethinng like this:

Dim strWhere As String

What are these lines for?

If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
Len(strWhere_Field1) - 5)

What are you striping from the string?

Thanks Ken, I really appreciate this.

Fred

strWhere = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And [Field2]=" & Me.lstGroupsSelect.Column(1)
DoCmd.OpenReport "ReportName", , , strWhere


If you have multiple values for a single field, then something like this
as you cycle through your listbox's selections:

Dim strWhere_Field1 As String, strWhere_Field2 As String
Dim strWhere As String
Dim lngLoop As Long
strWhere_Field1 = ""
strWhere_Field2 = ""
For lngLoop = 0 To Me.lstGroupsSelect.ListCount - 1
If Me.lstGroupsSelect.Selected(lngLoop) = True Then
strWhere_Field1 = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
" And "
strWhere_Field2 = "[Field2]=" & Me.lstGroupsSelect.Column (1) & _
" And "
End If
Next lngLoop
If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
Len(strWhere_Field1) - 5)
strWhere = strWhere_Field1 & " And " & strWhere_Field2
DoCmd.OpenReport "ReportName", , , strWhere

 

[Frederick Wilson]

DUHH, Sorry for that question.

I'll let you know what the outcome is.

Thanks,

Fred

 

[Frederick Wilson]

Ken,

For me this was like pulling chicken's teeth but I finally got it.
Thanks for getting me on the right road. Something else, which I guess
is common knowledge, the filter option on the report has to be set to yes.

Dim strWhere_Type As String 'to hold the value of the group type
Dim strWhere_TypeID As String 'to hold the value of the group type
identifier
Dim strWhere As String 'to hold the final where clause for the
sign in report
Dim varItem As Variant
Dim ctrDataLocation As Control

strWhere_Type = ""
strWhere_TypeID = ""


Set ctrDataLocation = Me.lstGroupSelect

'Loop trough and assemble a string based on what was selected in the
list box

For Each varItem In ctrDataLocation.ItemsSelected
strWhere_Type = "Type = '" & ctrDataLocation.Column(0, varItem) & "'"
strWhere_TypeID = "TypeID = '" & ctrDataLocation.Column(1, varItem)
& "'"
strWhere = strWhere + strWhere_Type & " AND " & strWhere_TypeID + "
OR "
Next varItem

'Removing the final "OR" from the end of the where clause
If strWhere <> "" Then strWhere = Left(strWhere, Len(strWhere) - 4)


DoCmd.OpenReport "rptSignInSheet", acViewPreview, , strWhere

Thanks Again,
Fred

 

[Ken Snell [MVP]]

I've never had to set the Filter option on a report when filtering it by
using the OpenReport method with a WHERE argument.
Did you do this in design view of the report? Should not be necessary, as
all appropriate settings should be set when the report opens via this
method.

 

[Ken Snell [MVP]]

Also, you may wish to use ( ) to group together the appropriate conditions
that you're using. I think you'd want to put parentheses at the begiinning
of strWhere_Type and at the end of strWhere_TypeID strings, as it appears
that you're looking for any combination of two conditions. If you don't,
you're hoping that Jet will properly know which combinations are to go
together.

 

[Frederick Wilson]

Okay