You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the
mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down
SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or
deselects
that item.)
Once you've made that change:
Dim strChoice As String
Dim varSelected As Variant
' No need to set a criteria if nothing's selected in the list box
If Me!NameOfListBox.ItemsSelected.Count > 0 Then
' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.
For Each varSelected In Me!NameOfListbox.ItermsSelected
' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.
strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "
Next varSelected
' Since we were adding the comma and space after each entry,
' we need to remove the final pair.
strChoice = Left(strChoice, Len(strChoice) - 2)
' strChoice now contains a comma-separated list that we can
' use in an IN clause
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else
DoCmd.OpenReport "NameOfReport", acViewPreview
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
dsc2bjn said:
Douglas,
Would you walk me through the code you provided?
I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a
query
to
return both sets of records of "BO" and "CCE" with the last selection.
I changed the combo box to a list box as your code instructs.
If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me
to
select multiple items.
:
Dim strChoice As String
Dim varSelected As Variant
If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) &
",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else
DoCmd.OpenReport "NameOfReport", acViewPreview
End If
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I created a pop-up form with a pull down list that I created by
typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the
database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement
to
return both sets of records.
From what has been said in the message string, that doesn't seem
possible.
So...now I need to figure out a way to allow them to pick from the
list
and
return the record for those values.
I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.
:
If they've chosen All, you don't need a condition. If they've
chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not
clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?
Would I need to do a If statement within the "On Open" event?
Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"
ETC.
End If
end if
:
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO',
"CCE')"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
That is exactly what I am attempting to do.
I had the IIf statement within the query.
How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?
:
I get it now. You've trying to create a criteria.
The problem with what you're trying to do is that you'll end
up
with a
condition like
Field1 = "BO" or "CCE"
which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.
Presumably you're trying to do this for the query itself.
You'll
either
have
to dynamically generate the SQL for the query, or else don't
put
a
criteria
on the query, but pass a criteria when you open the report.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
I guess a better thing to ask would be:
"How do I write a IIf statement to have the true portion
return
multiple
values?
I have tried: IIf(this happens, <>"SCAMPI", False part)
and
IIf(this
happens, "BO" or CCE", [False part)
When I use the <>, nothing is returned.
When I use the 'or', I get an error message saying the
statement
is
typed
incorrectly or too complex to be evaluated.
:
Okay, I suppose it might be correct. The True part of that
last
IIf
statement is
([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"
which means it'll return True or False, whereas all the
other
portions
are
return a string. I had been thinking that it was supposed
to
be
another
IIf
statement.
I don't see, though, how you expect to get two values on
the
report
from
that.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
I don't understand what you mean.
The last IIf statement is:
IIf([Forms]![frmOther Weakness Reporting
Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency
Source])<>"SCAMPI",