Button and Combo Box

[rclark1850]

I have a form with a a combo box that is already populated via. a query. I
have a report already created that creates a report based on all records in
the combo box.

I want to add a button that takes the selected item(s) from the combo box
and feed them into the report so it only shows the items the user picks. This
seems like an easy issue but I cant find for the life of me how to do it. I'm
a newbie to this...

 

[Dirk Goldgar]

I have a form with a a combo box that is already populated via. a query. I
have a report already created that creates a report based on all records
in
the combo box.

I want to add a button that takes the selected item(s) from the combo box
and feed them into the report so it only shows the items the user picks.
This
seems like an easy issue but I cant find for the life of me how to do it.
I'm
a newbie to this...

Are you sure you are talking about a combo box, not a list box? A combo box
only allows you to select one item at a time. and you referred to "selected
item(s)".

If it's a combo box, so only one item can be selected, the code for the
button's Click event procedure would look something like this:

'------ start of example code ------
Private Sub cmdReport_Click()

If IsNull(Me.cboYourComboBox) Then
MsgBox "Please select something first."
Me.cboYourComboBox.SetFocus
Else
DoCmd.OpenReport "rptYourReport", _
acViewPreview, _
WhereCondition:="SomeField=" & Me.cboYourComboBox
End If

End Sub
'------ end of example code ------

In the above,

cmdReport = the name of your button
cboYourComboBox = the name of your combo box
rptYourReport = the name of the report
SomeField = the name of the field (in the report's
recordsource) that you want to
filter by

The above code assumes that the field ("SomeField") is a numeric field. If
it's a text field, then the code needs to be modified to wrap the value of
the combo box quotes:

WhereCondition:="SomeField=" & _
Chr(34) & Me.cboYourComboBox & Chr(34)

 

[rclark1850]

Sorry, I have both list boxes and combo boxes. Would the code be similar for
a list box?

This code looks very helpful though.

 

[Dirk Goldgar]

Sorry, I have both list boxes and combo boxes. Would the code be similar
for
a list box?

The code would be the same for a single-select list box, which lets you
select only one value at a time. If it's a multiselect list box, though,
the code has to allow for multiple values. Here's an example of how to do
that. This example assumes that the field to be filtered on is numeric.

'------ start of code for multiselect list box ------
Private Sub cmdReport_Click()

Dim strCriteria As String
Dim varItem As Variant

With Me.lstYourListbox

If .ItemsSelected.Count = 0 Then
MsgBox "Please select something first."
Me.lstYourListbox.SetFocus
Else
' Build a comma-separated list of selected values.
For Each varItem In .ItemsSelected
strCriteria = strCriteria & "," & .ItemData(varItem)
Next varItem

' Drop leading comma.
strCriteria = Mid(strCriteria, 2)

If .ItemsSelected.Count = 1 Then
strCriteria = "YourFieldName = " & strCriteria
Else
strCriteria = "YourFieldName In (" & strCriteria & ")"
End If

DoCmd.OpenReport "rptYourReport", _
acViewPreview, _
WhereCondition:=strCriteria

End If

End With

End Sub
'------ end of code ------

In the above,

cmdReport = the name of your button
lstYourListbox = the name of your list box
rptYourReport = the name of the report
YourFieldName = the name of the field (in the report's
recordsource) that you want to
filter by

Again, if the field to be filtered by is not numeric, the code has to be
modified to surround each value with quotes. Here's the relevant amended
line:

strCriteria = strCriteria & "," & _
Chr(34) & .ItemData(varItem) & Chr(34)