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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frederick Wilson - 31 Jan 2005 00:17 GMT

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?

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 01:20 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Hello All,
>
[quoted text clipped - 10 lines]
> 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?

Reply to this Message

Frederick Wilson - 31 Jan 2005 01:55 GMT

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

> 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
[quoted text clipped - 5 lines]
> to pass the filtering string. That filtering string would be a WHERE clause
> construction without the WHERE word.

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 02:54 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> So let me get this right
> with the where clause in the openreport I would do something like
[quoted text clipped - 41 lines]
>> method to pass the filtering string. That filtering string would be a
>> WHERE clause construction without the WHERE word.

Reply to this Message

Fred Wilson - 31 Jan 2005 11:37 GMT

> 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)
[quoted text clipped - 22 lines]
> strWhere = strWhere_Field1 & " And " & strWhere_Field2
> DoCmd.OpenReport "ReportName", , , strWhere

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 13:34 GMT

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.

Signature

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
[quoted text clipped - 40 lines]
>> strWhere = strWhere_Field1 & " And " & strWhere_Field2
>> DoCmd.OpenReport "ReportName", , , strWhere

Reply to this Message

Frederick Wilson - 31 Jan 2005 13:41 GMT

DUHH, Sorry for that question.

I'll let you know what the outcome is.

Thanks,

Fred

> 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.

Reply to this Message

Frederick Wilson - 31 Jan 2005 20:01 GMT

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

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 20:18 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 44 lines]
>> method to pass the filtering string. That filtering string would be a
>> WHERE clause construction without the WHERE word.

Reply to this Message

Ken Snell [MVP] - 31 Jan 2005 20:21 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 44 lines]
>> method to pass the filtering string. That filtering string would be a
>> WHERE clause construction without the WHERE word.

Reply to this Message

Frederick Wilson - 31 Jan 2005 20:45 GMT

Okay

> 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.

Reply to this Message