Enter parameter after using .FilterOn

[Muaz]

Hi;

I am using .FilterOn = True , when it executes the system popup "Enter parameter Value", I do not understand why ?

I have designed a report. By default it executes for all records. After execution there is a form where a list box contains values to filter the report. After selecting the required value. When press button "Filter Record" it executes the below code :

Dim varItem As Variant
Dim strBaseStation As String
Dim strFleetType As String
Dim strEngineType As String
Dim strFilter As String

'Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rpt1ActEmpSch_qry") <> acObjStateOpen Then
DoCmd.OpenReport "rpt1ActEmpSch_qry", acViewPreview
Exit Sub
End If

' Build criteria string from lstPayType listbox
For Each varItem In Me.lstPayType.ItemsSelected
strBaseStation = strBaseStation & "," & Me.lstPayType.ItemData(varItem)
Next varItem
If Len(strBaseStation) = 0 Then
strBaseStation = "Like '*'"
Else
strBaseStation = Right(strBaseStation, Len(strBaseStation) - 1)
strBaseStation = "IN (" & strBaseStation & ")"
End If


' Build filter string
strFilter = "[PayType] " & strBaseStation

MsgBox "" & strFilter & "."
' Apply the filter and switch it on
With Reports("rpt1ActEmpSch_qry")
.Filter = strFilter
.FilterOn = True

End With


When the second last line " .FilterOn = True " executes the Enter parameter dialog box appear. Why.... I do not understand it should filter the report without appearing this dialog box. I do not understand where I am missing something ?

Any suggestion will be highly appreciated

Regards
Muaz

 

[vbaInet]

Did you write this code yourself or copied it from somewhere?

What is the message you get? Let's see the full text.

 

[Muaz]

Thanks for your reply.

I copied this code during google search and modify as per my report name. During debugging it shows that when it execute .FilterOn = True the dialog box appear "Enter Parameter Value".

 

[vbaInet]

What is the full message?

 

[Muaz]

Below mentioned image shows the dialog box. If I enter ATT i.e is shown in the dialog box the report will filter. This dialog box for entering parameter should not appear.

Regards
Muaz

 

[vbaInet]

So what is ATT? And where is it being used?

 

[Muaz]

ATT is PayType Code saved in one table. Report is based on query and that query have this code and its description. The description is showed in the report.

 

[vbaInet]

Is ATT a field? And is Paytype a field or a table?

 

[Muaz]

Filed Name is PayType. It has values ATT, CAS, NAT and SPC. ATT is code for ATTACHEE, CAS is code for CASUAL and NAT for NATIONAL as shown in snapshot previously attached.

 

[vbaInet]

Can you upload a cut down version of your db? Let me have a quick look.

 

[Muaz]

Please have a look.

 

[vbaInet]

I've just selected ATTACHEE, CASUAL and ran the report without any problems. Test it on the sample you uploaded.

 

[Muaz]

I just check the uploaded version on my system also on another system. After pressing the Button First the MsgBox message appear then popup "Enter parameter Value" ...

May be there is some access option setting... I don't know...

I am looking for access options may be I find something...

 

[vbaInet]

Tell me the exact process and I'll follow exactly what you did.

 

[Muaz]

I double click on database to open. after I double click on form to open. I select ATTACHEE and click on FILTER button. First It gives MsgBox message then "Enter Parameter Value" .... If I click on OK the report filters as blank. If I enter ATT in parameter value report is filtered.

 

[vbaInet]

Ok, I understand. See attached!

 

[Muaz]

Unable to open. message appear "Unrecognised database format"

 

[Muaz]

I am using Access 2007

 

[vbaInet]

Here's the code (I've just moved things around and added one or two things):

Private Sub btnApplyFilter_Click()
 Dim varItem As Variant
    Dim strBaseStation As String
    Dim strFleetType As String
    Dim strEngineType As String
    Dim strFilter As String

' Build criteria string from lstPayType listbox
    For Each varItem In Me.lstPayType.ItemsSelected
        strBaseStation = strBaseStation & ", '" & Me.lstPayType.ItemData(varItem) & "'"
    Next varItem
    If Len(strBaseStation) = 0 Then
        strBaseStation = "Like '*'"
    Else
        strBaseStation = Right(strBaseStation, Len(strBaseStation) - 1)
        strBaseStation = "IN (" & strBaseStation & ")"
    End If

' Build criteria string from lstFleetType listbox
'    For Each varItem In Me.lstFleetType.ItemsSelected
'        strFleetType = strFleetType & "," & Me.lstFleetType.ItemData(varItem)
'    Next varItem
'    If Len(strFleetType) = 0 Then
'        strFleetType = "Like '*'"
'    Else
'        strFleetType = Right(strFleetType, Len(strFleetType) - 1)
'        strFleetType = "IN (" & strFleetType & ")"
'    End If

' Build criteria string from lstEngineType listbox
'    For Each varItem In Me.lstEngineType.ItemsSelected
'        strEngineType = strEngineType & "," & Me.lstEngineType.ItemData(varItem)
'    Next varItem
'    If Len(strEngineType) = 0 Then
'        strEngineType = "Like '*'"
'    Else
'        strEngineType = Right(strEngineType, Len(strEngineType) - 1)
'        strEngineType = "IN (" & strEngineType & ")"
'    End If

' Build filter string
    strFilter = "[PayType] " & strBaseStation
'                " AND [FleetName_ID] " & strFleetType & _
'                " AND [Engine1_ID] " & strEngineType & _
'                " AND [Engine2_ID] " & strEngineType
                
MsgBox "" & strFilter & "."

'Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rpt1ActEmpSch_qry") <> acObjStateOpen Then
        DoCmd.OpenReport "rpt1ActEmpSch_qry", acViewPreview, , strFilter
    Else
        ' Apply the filter and switch it on
        With Reports("rpt1ActEmpSch_qry")
            .Filter = strFilter
            .FilterOn = True
        End With
    End If

End Sub

 

[Muaz]

Thank you very much for your time.... It works perfectly now.