Dynamic Report based on Current Form Filter

[Denise2020]

I am driving myself crazy with this. I have a form with various filters. One is a free-form textbox where you can type in a word or phrase and it will search most of the controls on the form, another is a button that selects all items that have a certain checkbox checked, and another is a dropdown list where certain pre-made lists can be chosen. I have a generic report and a button to open that report and would like to pass the filter criteria, whichever it may be, to the report. Basically, I want to print a list of whatever items I have filtered with the various filter methods on the report with one single button.

Nothing I have tried is working. I have tried various methods of either applying the filter to the report with the button on the form, as well as to the report itself in the On Open event, to no avail. It either gives me errors, or else it opens with all 8000 items and does not filter the results. I should note that the form is actually a subform on a main navigation form. An example of code I have tried in so many various ways:

Dim RptFilter as String
RptFilter = Me.Filter
DoCmd.OpenReport "rptAmmo", , , RptFilter

or

Dim RptFilter as String
RptFilter = Forms!fMainForm!fFix.Form.Filter
me.Filter = RptFilter
me.Filteron = True

or

Dim RptFilter as String
RptFilter = Forms!fMainForm!fFix.Form.Filter

If Len(RptFilter)>0 Then
DoCmd.OpenReport "rptAmmo", , , RptFilter
Else
DoCmd.OpenReport "rptAmmo"
End If

I am just grasping at straws now as I have tried so much it is blurry. I've been googling all day but am about to give up and therefore I am here. What am I doing wrong? (be gentle! )

 

[theDBguy]

Just using DoCmd.OpenReport "ReportName", acPreview, , Me.Filter should be enough. What's wrong with it?

 

[tvanstiphout]

Without acViewPreview (preferred over acPreview even if both have the same value), the default action would happen which is to print to the printer. Maybe your print queue is getting full.

 

[plog]

You need to choose one of those methods that actually opens your report and then find out what is in the filter string you are using. Use Debug.Print or MsgBox() to show what is actually in the filter string so you can see it with your own eyes and verify it is correct.

 

[arnelgp]

if the source of your subform is not same as the source of your report, then that is the problem when you filter.
since the filter might result like:

[table1].[field] = value

while your report has same datasouce, table1, but maybe you are using uery1 on table1?
so the filter will not work since (table1 is not found, should be query1, ei: query1.field).

you need to Remove the source reference to your filter, you can put these in a Module and call it on
when you open the report:

'arnelgp

Public Function RemoveFormRef(ByVal strFilter As String)
Dim var As Variant, i As Integer, ln As Integer
Dim sTmp As String
var = Split(strFilter, ".")
ln = UBound(var)
If ln = 0 Then
    RemoveFormRef = strFilter
    Exit Function
End If
var(0) = Trim(Replace$(var(0), "(", ""))
sTmp = var(0)
For i = 0 To ln
    var(i) = Trim$(Replace$(var(i), sTmp, ""))
Next
sTmp = Trim$(Join(var))
Do Until fnCountChar(sTmp, "(") = fnCountChar(sTmp, ")")
    sTmp = "(" & sTmp
Loop
RemoveFormRef = sTmp
End Function


' arnelgp
Public Function fnCountChar(ByVal sText As String, sChar As String) As Integer
Dim cnt As Integer
Dim i As Integer
i = InStr(1, sText, sChar)
Do Until i = 0
    cnt = cnt + 1
    i = InStr(i + 1, sText, sChar)
Loop
fnCountChar = cnt
End Function

your code:

    Dim frm As Form
    Set frm = Forms!fMainForm!fFix.Form
    If frm.FilterOn Then
        DoCmd.OpenReport ReportName:="rptAmmo", View:=acViewPreview, WhereCondition:=RemoveFormRef(frm.Filter)
    Else
        DoCmd.OpenReport ReportName:="rptAmmo", View:=acViewPreview
    End If

 

[Denise2020]

Just using DoCmd.OpenReport "ReportName", acPreview, , Me.Filter should be enough. What's wrong with it?

Yes, that was the first thing I tried and I was disappointed that it does not pull the filter over. Instead, it creates the report with all 8000+ objects.

 

[Denise2020]

Without acViewPreview (preferred over acPreview even if both have the same value), the default action would happen which is to print to the printer. Maybe your print queue is getting full.

I have tried it with acViewReport so that it didn't print, I was short on time and typed quickly and hopped over that bit in my examples here, sorry!

 

[Denise2020]

if the source of your subform is not same as the source of your report, then that is the problem when you filter.
since the filter might result like:

[table1].[field] = value

while your report has same datasouce, table1, but maybe you are using uery1 on table1?
so the filter will not work since (table1 is not found, should be query1, ei: query1.field).

you need to Remove the source reference to your filter, you can put these in a Module and call it on
when you open the report:

'arnelgp

Public Function RemoveFormRef(ByVal strFilter As String)
Dim var As Variant, i As Integer, ln As Integer
Dim sTmp As String
var = Split(strFilter, ".")
ln = UBound(var)
If ln = 0 Then
    RemoveFormRef = strFilter
    Exit Function
End If
var(0) = Trim(Replace$(var(0), "(", ""))
sTmp = var(0)
For i = 0 To ln
    var(i) = Trim$(Replace$(var(i), sTmp, ""))
Next
sTmp = Trim$(Join(var))
Do Until fnCountChar(sTmp, "(") = fnCountChar(sTmp, ")")
    sTmp = "(" & sTmp
Loop
RemoveFormRef = sTmp
End Function


' arnelgp
Public Function fnCountChar(ByVal sText As String, sChar As String) As Integer
Dim cnt As Integer
Dim i As Integer
i = InStr(1, sText, sChar)
Do Until i = 0
    cnt = cnt + 1
    i = InStr(i + 1, sText, sChar)
Loop
fnCountChar = cnt
End Function

your code:

    Dim frm As Form
    Set frm = Forms!fMainForm!fFix.Form
    If frm.FilterOn Then
        DoCmd.OpenReport ReportName:="rptAmmo", View:=acViewPreview, WhereCondition:=RemoveFormRef(frm.Filter)
    Else
        DoCmd.OpenReport ReportName:="rptAmmo", View:=acViewPreview
    End If

Darn, I was really hoping this was the issue but they have the same source, called qryAmmo. I am just at a loss.

ETA: So I just noticed that when I filter my form using my text box or buttons and then switch to design mode, the Filter property is empty. Shouldn't the filter be stored there? If so, can see why it isn't pulling any filter (Me.Filter) as it looks like there is no Filter. So if my buttons, search text box, and combo boxes are functioning and filtering records, where are those filters being stored?

 

[Denise2020]

I am narrowing it down now. If I open the subform on its own (ie not on the navigation form), the filter pass-through works as expected. If I try it through the navigation form, I get Runtime Error 2450, cannot find the referenced form. This is the code I am using:

Private sub Report_Open(Cancel as Integer)
Me.RecordSource = Forms!fFixObj.Form.RecordSource
End Sub

fMainForm is the name of my navigation form.
fFixObj is the name of the subform.

I know there is something about referencing subforms on a navigation form but have googled and can't seem to find just what I am after for referencing the form.

I have tried Forms!fMainForm!fFixObj.Form.RecordSource but get the a similar error, but rather than cannot find the form, it says cannot find the field.

Thanks in advance to all!

 

[arnelgp]

check your "filter" code maybe it is not filtering at all, but changing recordsource.
create a Temp querydef (saved) using the navigationsubform recordsource as sql.
then on open event of your report change the recordsource to temp querydef.
on closing the report, delete the querydef.