dialog box to filter report

[Neil_Pattison]

I have set up a report called rptAbsence, and also a dialog box called frmAbsenceFilter. This dialog box is a form containing a combo box and 2 command buttons (apply and remove). The combo box comes from a table called[Issues].

The problem I'm having is when I apply the filter a box pops up asking for a parameter value for [issue].

The code I have used for the dialog box is below

Private Sub cmdApplyFilter_Click()
Dim strIssue As String
Dim strFilter As String
' Checks that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptAbsence") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Builds the criteria string for the Issue field
If IsNull(Me.cboIssue.Value) Then
strIssue = "Like '*'"
Else
strIssue = "='" & Me.cboIssue.Value & "'"
End If
strFilter = "[Issue] " & strIssue
' Applies the filter and switchs it on
With Reports![rptAbsence]
.Filter = strFilter
.FilterOn = True
End With
End Sub
----------------------------------------------------------------------
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
Reports![rptAbsence].FilterOn = False
End Sub


Does anyone know why this is occuring and how it can be resolved? Any help would be greatly appreciated

 

[KenHigg]

Just a quick glance - Should it be:

strFilter = "[Issue] ='" & strIssue & "'"

???

 

[Neil_Pattison]

Thanks for replying Ken but unfortunatly this doesn't work

 

[KenHigg]

Hum...

Just another thing after a quick glance; I think this:

.Filter = strFilter
.FilterOn = True

Should be

me.Filter = strFilter
me.FilterOn = True

 

[jsanders]

You might try running the "Orders Entry" wizard in a new DB and take a look at how they do it.

They use that approach when viewing an orders report.

 

[Neil_Pattison]

I've had a look at this and can't see what I'm doing wrong. I have tried this for a seperate report and the same thing is happening. The code for this one is:

Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strTeam As String
Dim strWorkStream As String
Dim strLicenceArea As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptdatafilter") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Office field
If IsNull(Me.cboTeam.Value) Then
strTeam = "Like '*'"
Else
strTeam = "='" & Me.cboTeam.Value & "'"
End If
' Build criteria string for Department field
If IsNull(Me.cboWorkStream.Value) Then
strWorkStream = "Like '*'"
Else
strWorkStream = "='" & Me.cboWorkStream.Value & "'"
End If
' Build criteria string for Gender field
Select Case Me.fraLicenceArea.Value
Case 1
strLicenceArea = "='HV'"
Case 2
strLicenceArea = "='LV'"
Case 3
strLicenceArea = "Like '*'"
End Select
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Team] " & strTeam & " AND [WorkStream] " & strWorkStream & " AND [LicenceArea] " & strLicenceArea
' Apply the filter and switch it on
With Reports![rptdatafilter]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptdatafilter].FilterOn = False
End Sub

The problem is the same. If an option is selected on the dialog a pop up screen appears asking for parameter values and I can't understand why. Any further ideas?

 

[KenHigg]

I would suggest you use the immediate window and step trhough the code to see what strFilter looks like...

 

[Neil_Pattison]

as far as I can see the code is fine. I have had this code working on a different report and have just changed the field and report names but for some reason the pop ups asking for the parameters occur on this one

 

[KenHigg]

Usually this happens when you use brackets to ref a fld but fail to fully qualify correctly where the fld exists. The parameter it asks for should give a good clue where the error is happening...

Hope this makes sense and maybe helps you find the error

 

[Neil_Pattison]

as far as you can see though is the code written correctly?

 

[KenHigg]

When it prompts you for an input, what does the msg box say?

 

[Neil_Pattison]

Thanks for getting back quickly Ken, but I've sorted it now. thanks for your help

 

[KenHigg]

.
.
.
.
Cool