[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Can I pass a recordset to a report?

I have a form with a subform which contains a list of transactions filtered in various ways, by a filter which I create in code and load into the Filter property of the subform, which works.

I have a report similar to the form which I want to be able to print from a command button on the form, to contain the same data.  I have tried saving the filter from the subform and loading it into the Filter property of the subreport, but Access gives a compile error.   (Property doesn't support the method).

Can I pass the recordset from the subform to the Source Object of the subreport?

Thanks in advance,

Clive
0
CRB1609
Asked:
CRB1609
  • 2
  • 2
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is code you can run to set the report filter before it is opened:
'------------------------------------ SetReportFilter
Sub SetReportFilter( _
   psReportName As String _
   , pvFilter As Variant)
'strive4peace
   'Save a filter to the specified report
   'You can do this before you send a report in an email message
   'You can use this to filter subreports instead of putting criteria in the recordset

   ' USEAGE:
   ' example: in code that processes reports for viewing, printing, or email
   ' SetReportFilter "MyReportname","someID=1000"
   ' SetReportFilter "MyAppointments","City='Denver' AND dt_appt=#9/18/16#"

   ' PARAMETERS:
   ' psReportName is the name of your report
   ' pvFilter is a valid filter string or null

   On Error GoTo Proc_Err

   '---------- declare variables
   Dim rpt As Report

   '---------- open design view of report in Hidden mode (> Access 2000)
   DoCmd.OpenReport psReportName, acViewDesign ', , , acHidden

   '---------- set object variable to report
   Set rpt = Reports(psReportName)

   '---------- set report filter and turn it on
   rpt.Filter = pvFilter
   rpt.FilterOn = IIf(Len(Nz(pvFilter, "")) > 0, True, False)

   '---------- Save and Close report
   DoCmd.Close acReport, psReportName, acSaveYes

Proc_Exit:
   '----------  Release object variable
   Set rpt = Nothing
   Exit Sub

Proc_Err:
   Resume Next

   MsgBox Err.Description, , "ERROR " & Err.Number & "  SetReportFilter"
   'press F8 to step thru code and fix problem
   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume

End Sub

Open in new window

0
 
CRB1609Author Commented:
Thank you Crystal.  Worked wel.l
0
 
CRB1609Author Commented:
I don't seem to be able to award points and close this question.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now