Problem filtering Report by value selected from a Listbox on a Form

[David Ball]

Hi,

I have a query, qryJobCardsOnly_10, with a field Reporting Discipline Category. In the Criteria row for that field I have:

[Forms]![frmSelectProjectsSUMMARYREPORT_WEnding17]![lsbProjectSel10]

There is a report, rptJobCardsOnly_20. In the Data tab of the Property Sheet the Record Source is qryJobCardsOnly_10 and the Filter is [Reporting Discipline Category] IN('Scaffolding / Rigging'). This is what appears in the Filter when 'Scaffolding / Rigging’ is selected from the list box on the form.

There is a form, frmSelectProjectsSUMMARYREPORT_WEnding17, that has a listbox, lsbProjectSel10. The Row Source of the listbox is:
SELECT DISTINCT [Reporting Discipline Category] FROM tblSMSdata;
There is also another listbox for the Reporting Week Ending date.
There is a command button on the form with the following code:

Private Sub cmdApplyFilter2_Click()
Dim varItem As Variant
Dim strProject As String
Dim strWEdate As String
Dim strFilter As String
'Open the report
DoCmd.OpenReport "rptJobCardsOnly_20", acViewReport
' Check that the report is open
' If SysCmd(acSysCmdGetObjectState, acReport, "rptJobCardsOnly_20") <> acObjStateOpen Then
' MsgBox "You must open the report first."
' Exit Sub
'End If
' Build criteria string from lsbProjectSel10 listbox
For Each varItem In Me.lsbProjectSel10.ItemsSelected
strProject = strProject & ",'" & Me.lsbProjectSel10.ItemData(varItem) _
& "'"
Next varItem
If Len(strProject) = 0 Then
strProject = "Like '*'"
Else
strProject = Right(strProject, Len(strProject) - 1)
strProject = "IN(" & strProject & ")"
End If
' Build criteria string from lsbWEdate10 listbox
For Each varItem In Me.lsbWEdate10.ItemsSelected
strWEdate = strWEdate & ",#" & Format(Me.lsbWEdate10.ItemData(varItem) _
, "mm\/dd\/yyyy") & "#"
Next varItem
If Len(strWEdate) = 0 Then
strWEdate = "Like '*'"
Else
strWEdate = Right(strWEdate, Len(strWEdate) - 1)
strWEdate = "IN(" & strWEdate & ")"
End If
' Build filter string
strFilter = "[Reporting Discipline Category]" & strProject
' Apply the filter and switch it on
With Reports![rptJobCardsOnly_20]
.Filter = strFilter
.FilterOn = True
End With
End Sub

The problem is that when I try to run this by pressing the command button on the form, after selecting values from the list boxes, nothing appears in the report.
However, if I open the report (without using the form) and just type values into the enter parameter boxes it works perfectly.
When I use the form to do this it seems to put the correct value, as selected from the form’s list box, into the Filter row of the report as below:
[Reporting Discipline Category] IN('Scaffolding / Rigging')
However, the report opens up blank!
Initially I set this up to be able to select a value from each of the list boxes and run the report but when the report opened blank I decided to try the individual list boxes’ separately. I first modified the code so that I was only filtering the report by the date selected from the date list box and it worked perfectly.
I then modified the code to filter the report only by the Reporting Discipline Category selected from the lsbProjectSel10 list box. This is not working.
I have used copies of the same query, report and form for reports that return various other values and they all work fine.

What is it about this Reporting Discipline Category that is causing the problem?
Thanks very much

Dave

 

[bastanu]

Hi David,

Your method is a bit too complicated (it would be much easier if you replaced the listbox with a subform based on a local table of reporting discipline categories that has a Selected yes/no field and then incorporating that into the reports record source query). But anyway, I believe your problem is caused by the fact that you open the report in "Report" view. You need to open it in acViewDesign (maybe set its visible property to false), apply your filter then switch to report view (and make Visible=True).

Cheers,
Vlad

 

[Pat Hartman]

Here's a sample that shows how to use a multi-select listbox to filter a form. The same technique works for a report.

 

[David Ball]

Thanks guys. It is working now.

I had entered formulas into the criteria rows of the two fields of interest in the query.

I deleted these and it worked straight away.