473,372 Members | 1,204 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

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
5 3220
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
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
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
No help, but thanks anyway.

Jan 25 '06 #5
No help, but thanks anyway.

Jan 25 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Russell Mangel | last post by:
Is it possible to use the using statement with XmlTextReader? I tryed to use it, but it gives me the error message: Cannot implicitly convert type 'System.Xml.XmlTextReader' to...
5
by: Bill Priess | last post by:
Hey gang, Ok, I'm stumped on this one... I am using the using statement to wrap a SqlDataAdapter that I am using to fill a DataTable. Now, what I need to know is, just how much block-scope...
1
by: Kepler | last post by:
I have a situation where I need to use a Using statement that creates some records in a database. After that completes, if it completes, I need to do some file creation. Any code I'm putting...
8
by: J-T | last post by:
I have a class like below I have a couple of questions about that: 1) I like to use "Using statement" when creating an object of this class,so I had to implement IDisposable.Am I doing this right...
1
by: pascal_pare | last post by:
Hi, Is there a way to declare a using statement with the CodeDom? Something like: using (obj = new Object()) { obj.DoSomething; }
18
by: Trevor | last post by:
I have seen the "using" statement used in strange ways in some C# code. Example: using (StreamReader sr = new StreamReader("TestFile.txt")) { // do some stuff... } What effect does the...
5
by: Andreas Müller | last post by:
Hi, I was wondering, if there is something similar in VB.NET like the using statement in C#. What it does is to automatically call Dispose on the object decrared with in the statement when the...
2
by: Robert Bravery | last post by:
Hi all, Being new to C# and .net I often don't know how to use things. I have created an app that imports excel data, it works well, with methods to open excel, extract the data and close excel....
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.