How do you apply a filter in a form for a report 1

[tiger3p]

Hello,

I would like to create a form where the user would select a category and based on the selection, will click on the report button and report with the category selected would open.

I have several reports that I would like to display based on the filter that the user selects on the main form. In other words, I have the following lists:

NY
DC
VA
ALL

If the user selects NY, the report will list employees of NY only. If the user selects ALL, the report will list everyone in the company.

Is there an easy way to do this?

Thanks!

 

[dhookom]

I would use the command button wizard to create a button that opens the unfiltered report. Then modify the code to use the "where condition" of the DoCmd.OpenReport method. Assuming you have a list box of states named "lboStates"

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.lboStates) Then
   strWhere = strWhere & " And [State]='" & Me.lboStates & "' "
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

Duane MS Access MVP

 

[tiger3p]

So what I'm envisioning is:

on the left of the form (query displaying the lists of states)

on the bottom will be a button [ALL] which would automatically highlights all the states and a button [NONE] which would deselect the highlighted states.

on the right of the form will be a report button to open up the report and will only display the filtered items.

Sounds easy enough?

Thanks again!

 

[dhookom]

To select all or none, I use the code found on Allen Browne's web page

http://www.allenbrowne.com/func-12.html.

My code would treat "none selected" the same as "all selected". I can't imagine why you would want to open a report with no records.

Did you want to be able to select more than one (but not all) states in the list box? If so, Allen has another page that describes one solution

http://www.allenbrowne.com/ser-50.html.

There is another at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP

 

[tiger3p]

I got the following error when I try to do the Clear All and Select All function:

The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected:SelectAll.

Here's the step I took:

Created a form via Northwind database
Created a lists
Created a command button and under On Click put the following code [Event Procedure]:

Private Sub ClearAll_Click()
Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
'Purpose: Unselect all items in the listbox.
'Return: True if successful
'Author: Allen Browne.

http://allenbrowne.com

June, 2006.
Dim varItem As Variant

If lst.MultiSelect = 0 Then
lst = Null
Else
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
End If

ClearList = True

Exit_ClearList:
Exit Function

Err_ClearList:
Call LogError(Err.Number, Err.Description, "ClearList()")
Resume Exit_ClearList
End Function

If I take out the "Private Sub ClearAll_Click()" I get no results.

 

[tiger3p]

Here's another question:

I got the "multi-select list box to filter a report" to work. Thank you very much for the reference...

How can I put a note to show that if they selected an item which is not on the report to show "The item you selected does not have a report"

For example:

They select NY and MD but the report only has MD, I would want to show: There's no report for NY. Then the report for MD pops up?

Is this doable?

 

[dhookom]

The code from Allen's web site should be located in a standard module with a name like "modControlCode". To call the code, you would use so

Private Sub ClearAll_Click()
   If Not ClearList(Me.lboStates) Then
      Msgbox "something"
   End If
End Sub

I would use the ShowAllSelected() function to display the states that were selected. This is shown on the form in the sample. Leave it up to the report reviewers to determine which states don't have data. YOu could create a subreport that displays only the included state names that actually had records.

Duane MS Access MVP

 

[tiger3p]

Duane,
I can't seem to get the clearlists and selectall function to work. Where do I call the function code to?

 

[dhookom]

The code should be in a standard module. You would probably add a command button to your form that would call either function. Something like:

Private Sub cmdSelectAll_Click()
    Call SelectAll(Me.lboStates) 
End Sub

If this doesn't work, you need to provide more information about your control names.

Duane MS Access MVP

 

[tiger3p]

I get the following error:

The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

I'm using still the Northwind database.

The name of my list is lstCategory taken from the "multi-select list box to filter a report" code.

Not sure what I'm doing wrong..

 

[tiger3p]

Here's the entire code under FORM1 that I created under the Northwind Database:

Option Compare Database

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 = "[CategoryID] 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
Function ClearList(lst As ListBox) As Boolean
On Error GoTo Err_ClearList
'Purpose: Unselect all items in the listbox.
'Return: True if successful
'Author: Allen Browne.

http://allenbrowne.com

June, 2006.
Dim varItem As Variant

If lst.MultiSelect = 0 Then
lst = Null
Else
For Each varItem In lst.ItemsSelected
lst.Selected(varItem) = False
Next
End If

ClearList = True

Exit_ClearList:
Exit Function

Err_ClearList:
Call LogError(Err.Number, Err.Description, "ClearList()")
Resume Exit_ClearList
End Function
Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
'Purpose: Select all items in the multi-select list box.
'Return: True if successful
'Author: Allen Browne.

http://allenbrowne.com

June, 2006.
Dim lngRow As Long

If lst.MultiSelect Then
For lngRow = 0 To lst.ListCount - 1
lst.Selected(lngRow) = True
Next
SelectAll = True
End If

Exit_Handler:
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, "SelectAll()")
Resume Exit_Handler
End Function

Private Sub SelectAll_Click()
Call SelectAll(Me.lstCategory)
End Sub

 

[dhookom]

It looks like you have:
Function ClearList(lst As ListBox) As Boolean
and
Public Function SelectAll(lst As ListBox) As Boolean
in the form's module rather than a standard module (unless you have the code in both places.

Try search all you database code for "Function SelectAll" to see if you need to get rid of the copy in your form's module.

Duane MS Access MVP

 

[tiger3p]

Hi Duane,

How do I search the database for this? Do i go one by one to each form?

 

[dhookom]

While in any module you can click the menu items until you find search. The dialog has the option to search the entire MDB.

Duane MS Access MVP