Solved passing filtered recordsets to reports (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
On my main form I have filtered 9 records

In the General Declarations area I have

Code:
Option Compare Database

Option Explicit

Public rst9 As DAO.Recordset



Private Sub cmdReport_Click()

  Set rst9 = Me.Recordset

'Debug.Print rst9.RecordCount  returns 9 records



DoCmd.OpenForm "popPrint", acNormal

 

    Me.Visible = False



cmdReport_Click_Exit:

    Exit Sub



End Sub

Then I have a parameter form called popPrint
Option Compare Database
Code:
Option Explicit

Dim dbs As Database

Dim rst9 As DAO.Recordset

Private Sub Form_Open(Cancel As Integer)

    Set dbs = CurrentDb

        Set rst9 = Forms("splField").Recordset

End Sub

    

Private Sub btnCancel_Click()

    DoCmd.Close

    DoCmd.SelectObject acForm, "splField"

    DoCmd.Restore

End Sub



Private Sub btnPrint_Click()

Dim strReport As String



'Debug.Print rst9.RecordCount    also returns 9 records

Select Case Me.optPrint

    Case 1 '45 Lines - pg break at lane

        strReport = "rptField"

    Case 2 '65 Lines - pg break at Lane

        strReport = "2-rptField"

    Case 3 '45 Lines - no pg break

        strReport = "NoPBrptField"

    Case 4 '65 Lines - no pg break

        strReport = "NoPB2-rptField"

    Case 5 '2 columns

        strReport = "rpt2column"

End Select



DoCmd.OpenReport strReport, _

    View:=acViewPreview

    

Me.Visible = False



End Sub

Then my report:
Code:
Option Compare Database

Private Sub Report_Open(Cancel As Integer)



   Me.Recordset = rst9



End Sub

The report has a query attached and insists on printing all 86 pages instead of the filtered 9 records.
If I remove the query it doesn't work either.
Where am I wrong?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
Firstly you should be using Option Explcit at the top of every module, that includes reports?

That would then highlight that rst9 does not exist in the report as it has gone out of scope.?
It was declared on your form?

Use the Where parameter when you open the report or refer to the form recordset from the report if it is still open, or make the rst9 object global?
 
Last edited:

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
I thought putting it in the declarations and making it public was making it global
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
I thought putting it in the declarations and making it public was making it global
Ah OK, my apologies, I missed that. :mad: Think I should just go to bed. :(
Let me try it here.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
I am getting an error about only available with ADP
Plus in the Locals window rst9 is empty, so try checking there?

Plus, should you not be using Set again in the report?

I must admit I have always opened my reports with the WHERE clause.
 

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
There are a dozen filtered columns. Where gets so messy.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
Plus I think that just makes it Public to the whole form, not outside of it.?
No matter if I make it global I stiill get that ADP error
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
There are a dozen filtered columns. Where gets so messy.
Yes, but it is just a filter?, so if the source is the same, except for the filter then
Code:
DoCmd.OpenReport "rptTransactions", acViewPreview, , Me.Filter

works just as well?
I've just tested it and it works.

One solution at least.?

Plus from what you have just said, that is the recordset? it just happens to be filtered?, but the recordset remains the same.?
 

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
See that's where I lose it... It goes from the filtered form through a parameter form to select the report. I can't figure how to get through the parameter form. I was using openargs but I though there must be a better way.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:08
Joined
May 7, 2009
Messages
19,169
add filter on your recordset:
Code:
Private Sub cmdReport_Click()
    Dim strFilter As String
    Set rst9 = Me.Recordset
    'Debug.Print rst9.RecordCount returns 9 records

    If Me.FilterOn Then
        strFilter = RemoveFormReference(Me.Filter)
        rst9.Filter = strFilter
       
        Set rst9 = rst9.OpenRecordset
    End If
   
    DoCmd.OpenForm "popPrint", acNormal

    Me.Visible = False

cmdReport_Click_Exit:
    Exit Sub

End Sub

Paste the following function to a separate Module, this will remove the "[Form]!" reference
from your Filter (on the form):
Code:
'from someone i forgot where and who
Public Function RemoveFormReference(SQLCode As String) As String

'   The purpose of this function is to take an SQL statement and convert
'   a reference to a form control into a literal value

'For example, in SQL code, we need to change this:
'WHERE (((tblAmounts.intType) = [Forms]![frmTypeSelector]![cmbType]))
'OR this:
'WHERE (((tblAmounts.intType) = Forms!frmTypeSelector!cmbType))
'...to this, if the value is a number:
'WHERE (((tblAmounts.intType) = 1))
'...or this, if the value is a string:
'WHERE (((tblAmounts.strName)='Dennis'))
'...or this, if the value is a Date:
'WHERE (((tblAmounts.DateField)=#10/1/2015#))


    Dim intFormRefPos As Integer, strFormName As String, strCtlName As String
    Dim strTemp As String

'   If no form references are found, just return the string argument
'   If a form reference IS found, manipulate the string argument first, then return it

'   Find the first type of form reference (see examples above)
    intFormRefPos = InStr(1, SQLCode, "[Forms]![")
    If intFormRefPos > 0 Then
'       Extract the form name and control name from the SQL code
        strFormName = Mid(SQLCode, intFormRefPos + 9)
        strFormName = Left(strFormName, InStr(1, strFormName, "]") - 1)
        strCtlName = Mid(SQLCode, intFormRefPos + 9 + Len(strFormName) + 3)
        strCtlName = Left(strCtlName, InStr(1, strCtlName, "]") - 1)
       
'       Insert the literal value in the place of the control reference
        strTemp = Left(SQLCode, intFormRefPos - 1)
        If IsNumeric(Forms(strFormName).Controls(strCtlName)) = False Then
            If IsDate(Forms(strFormName).Controls(strCtlName)) Then
                strTemp = strTemp & "#"
            Else
                strTemp = strTemp & "'"
            End If
        End If
        strTemp = strTemp & Forms(strFormName).Controls(strCtlName)
        If IsNumeric(Forms(strFormName).Controls(strCtlName)) = False Then
            If IsDate(Forms(strFormName).Controls(strCtlName)) Then
                strTemp = strTemp & "#"
            Else
                strTemp = strTemp & "'"
            End If
        End If
        strTemp = strTemp & Mid(SQLCode, intFormRefPos + 9 + Len(strFormName) + 3 + Len(strCtlName) + 1)
        SQLCode = strTemp
    Else
'       Find the second type of form reference (see examples above)
        intFormRefPos = InStr(1, SQLCode, "Forms!")
        If intFormRefPos > 0 Then
'           Extract the form name and control name from the SQL code
            strFormName = Mid(SQLCode, intFormRefPos + 6)
            strFormName = Left(strFormName, InStr(1, strFormName, "!") - 1)
            strCtlName = Mid(SQLCode, intFormRefPos + 6 + Len(strFormName) + 1)
            strCtlName = Left(strCtlName, InStr(1, strCtlName, ")") - 1)

'           Insert the literal value in the place of the control reference
            strTemp = Left(SQLCode, intFormRefPos - 1)
            If IsNumeric(Forms(strFormName).Controls(strCtlName)) = False Then
                If IsDate(Forms(strFormName).Controls(strCtlName)) Then
                    strTemp = strTemp & "#"
                Else
                    strTemp = strTemp & "'"
                End If
            End If
            strTemp = strTemp & Forms(strFormName).Controls(strCtlName)
            If IsNumeric(Forms(strFormName).Controls(strCtlName)) = False Then
                If IsDate(Forms(strFormName).Controls(strCtlName)) Then
                    strTemp = strTemp & "#"
                Else
                    strTemp = strTemp & "'"
                End If
            End If
            strTemp = strTemp & Mid(SQLCode, intFormRefPos + 6 + Len(strFormName) + 1 + Len(strCtlName))
            SQLCode = strTemp
        End If
    End If

    RemoveFormReference = SQLCode

End Function

Just Open the Report Without OpenArgs.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:08
Joined
May 7, 2009
Messages
19,169
i am afraid you cannot set the Report's Recordset.
instead use WhereCondition parameter when you Open the report.
see this sample. filter the form and open the report.
 

Attachments

  • FilteredReport.zip
    57.8 KB · Views: 144

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
I have searched and searched... you went far and above what was expected. Thank you so much for explaining and working through this for me.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:08
Joined
May 7, 2009
Messages
19,169
you're welcome :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
See that's where I lose it... It goes from the filtered form through a parameter form to select the report. I can't figure how to get through the parameter form. I was using openargs but I though there must be a better way.
Well you could make the filter a global variable or tempvar?
 

ClaraBarton

Registered User.
Local time
Today, 15:08
Joined
Oct 14, 2019
Messages
427
The filter works beautifully but my problem still exists...
How do I pass a filter and recordset from my original form, through a parameter form (checking which report of various that I want)
and then to the report.
The error I get is "the method is not valid because the form (the pass through parameter form) is not bound to a table or query.
My where condition is correct for the filter but I lose the recordset on the middle form between the recordset and the report.
Code:
 DoCmd.OpenForm _
    FormName:="popPrint", _
    View:=acNormal, _
    WhereCondition:=strFilter
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:08
Joined
Sep 21, 2011
Messages
14,047
Well I was expecting arnelgp to respond to you, as he had given you code previously.
You could do it by saving the recordsource of the form to a Tempvar/Global variable and use that to set the recordsource of the report in it's open event. You could also pass it in via openargs?, which might be a better solution as you would have to know if you need to change the recordsource of the report perhaps?, by having a value in the OpenArgs then that would kill two birds with one stone. ?
 

Users who are viewing this thread

Top Bottom