Dialog Box

S

Susie

I have a dialog box (with a drop down) set up to choose criteria for a
report. Is there a way to pick more than one option in a dialog box. For
example, if I had a drop down that displayed "Red", "White", and "Blue", is
there a way to choose "Red" and "White". Thank you for your help....

Susie
 
R

Rick Brandt

Susie said:
I have a dialog box (with a drop down) set up to choose criteria for a
report. Is there a way to pick more than one option in a dialog box.
For example, if I had a drop down that displayed "Red", "White", and
"Blue", is there a way to choose "Red" and "White". Thank you for
your help....

Susie
Click to expand...

Not with a ComboBox, no, but you can make multiple selections from a ListBox
if the Multi-Select option is set. However; in that situation you need code
to extract all of the chosen values. You won't be able to simply refer to
the Value property like you can with a ComboBox or a ListBox with
Multi-Select disabled.
 
S

Susie

Thank you for your response, Rick. I don't know code, but I wanted to throw
out a shot in the dark. Is there a way that I can combine my categories with
a category that read "Red and White", and then somehow use a wild card when I
just want it to pull "Red" or "White"? I hope this makes sense.....Thank you
for your help.....
 
R

Rick Brandt

Susie said:
Thank you for your response, Rick. I don't know code, but I wanted
to throw out a shot in the dark. Is there a way that I can combine
my categories with a category that read "Red and White", and then
somehow use a wild card when I just want it to pull "Red" or "White"?
I hope this makes sense.....Thank you for your help.....
Click to expand...

If you just want an option for "All" you can make your criteria...

WHERE SomeField = Forms!FormName!ComboBoxName
OR Forms!FormName!ComboBoxName Is Null

The result of that is if the ComboBox is left blank you will get all
records, but when a selection is made you will get only those records
matching the selection.

Will that suffice? You can certainly accomplish more complex stuff, but it
gets increasingly difficult to do without VBA code.
 
S

Susie

Thanks for your help Rick. Can you send me the code to choose more than 1
category (your first response). I'll try my hand at it and take a chance.
Thank you so much for your help.
 
R

Rick Brandt

Susie said:
Thanks for your help Rick. Can you send me the code to choose more
than 1 category (your first response). I'll try my hand at it and
take a chance. Thank you so much for your help.
Click to expand...

A bit more involved than you might think. The problem is that even after
you use code to extract the values selected in the ListBox it is not easy to
retrieve them in a fashion that your query can do anything with. The
simplest way I could think of was this...

In a standard module create a public variable and a function that returns
its value...

Public SelectedColors As String

Public Function GetSelectedColors() As String
GetSelectedColors = SelectedColors
End Function

Modify your query criteria to...

WHERE InStr(1,GetSelectedColors(),[ColorField]) > 0
OR GetSelectedColors() = ""

In your dialog form I assume you have a button that opens the report. Its
code will need to look like...

Private Sub CommandButton_Click()
SelectedColors = ""
Dim VarItem As Variant

For Each VarItem In Me.ColorList.ItemsSelected
SelectedColors = SelectedColors & ", " & Me.ColorList.Column(0,
VarItem)
Next VarItem

DoCmd.OpenReport "ReportName", acViewPreview
End Sub

How the above works...

The button will loop through the selections in the ListBox and store them as
a comma separated list in the public variable named SelectedColors. EX:
"Red, Blue, Black"

A variable cannot be used directly in a query, but a user-define function
can be which is why we created a function that does nothing more than return
the value of the variable.

The InStr() part of the query will return a non-zero value for any row where
the color field is contained within the SelectedColors variable. If no
selections were made the value of GetSelectedColors will be a zero length
string "" which will cause the query to return all rows.

The criteria of InStr() is not very efficient but the alternative is to use
code to actually construct the SQL for the query based on the selections and
then construct a proper IN() clause which would be more efficient.
 
Top