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

Using and If Statement in conjunction w/ Multi-Select List Box to Filter a Report

P: n/a
Is there a way to add an If Statement to the following code so if data
in a field equals "x" it will launch one report and if it equals "y" it
would open another report. Anyone know how to modify this?

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 = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
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 = "[AutoID] 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,
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
________________________________________

Jan 24 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
How about replacing the line:

strDoc = "Products by Category"

with:

If [SomeControl] = "x" Then
strDoc = "Report1"
Else
strDoc = "Report2"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dk*******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Is there a way to add an If Statement to the following code so if data
in a field equals "x" it will launch one report and if it equals "y" it
would open another report. Anyone know how to modify this?

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 = "Products by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
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 = "[AutoID] 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,
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
________________________________________

Jan 24 '06 #2

P: n/a
Thank you, but since I am working with a multi-select list box with
unbound controls, the field that I need refer to isn't a part of the
form, but a part of the rowsource of the unbound multi-select list box.
I have tried to work this out, but can't seem to get it. Here is what
I have so far:

If Me.lstCategory.RowSource.Autos.AutoState = "TX" Then
strDoc = "PrintTXAutoIDCardFromMultiSelect"
Else
strDoc = "PrintMiscAutoIDCardFromMutltiSelect"
End If

lstCategory being the name of the unbound multi-select list box and
Autos.AutoState being a part of the row source of the multi-select list
box that I need to refer to. I know my problem is with the 1st line
and in the way I am trying to refer to the rowsource.

HELP!!!!

Thanks.
DK

Jan 25 '06 #3

P: n/a
So you want to know whether "TX" is one of the items in the multi-select
list box?

You will need to write a loop through the ItemsSelected collection of the
list box, examining each one to see if you have a match.

You will need to code this yourself, recognising that becuase it is a
multi-select, it is possible that multiple conditions could be matched.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dk*******@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Thank you, but since I am working with a multi-select list box with
unbound controls, the field that I need refer to isn't a part of the
form, but a part of the rowsource of the unbound multi-select list box.
I have tried to work this out, but can't seem to get it. Here is what
I have so far:

If Me.lstCategory.RowSource.Autos.AutoState = "TX" Then
strDoc = "PrintTXAutoIDCardFromMultiSelect"
Else
strDoc = "PrintMiscAutoIDCardFromMutltiSelect"
End If

lstCategory being the name of the unbound multi-select list box and
Autos.AutoState being a part of the row source of the multi-select list
box that I need to refer to. I know my problem is with the 1st line
and in the way I am trying to refer to the rowsource.

HELP!!!!

Thanks.
DK

Jan 25 '06 #4

P: n/a
No help, but thanks anyway.

Jan 25 '06 #5

P: n/a
No help, but thanks anyway.

Jan 25 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.