Trying to figure out how to make a public sub work going from Me. to Forms! (etc.) (1 Viewer)

[christopherjoubert]

I have the following code segment in a vba module for a specific form :

Me.srpt_Report.SourceObject = Report
Forms!frm_MainLanding!srpt_Report.Report.filter = Filter
Forms!frm_MainLanding!srpt_Report.Report.FilterOn = True

It works perfectly embedding and filtering the report, my issue is I have about 100 options for this code, and if I can avoid copy/paste I'd like to.
So I figured I would create a module that does the exact same thing, I can feed in everything I need, except... When I try this:

Forms!frm_MainLanding.srpt_Report.Form.SourceObject = reportname
Forms!frm_MainLanding!srpt_Report.Report.filter = reportfilter
Forms!frm_MainLanding!srpt_Report.Report.FilterOn = True

I get error 2467 object doesn't exist or is closed

and when I try this:

Forms!frm_MainLanding!srpt_Report.SourceObject = reportname
Forms!frm_MainLanding!srpt_Report.Report.filter = reportfilter
Forms!frm_MainLanding!srpt_Report.Report.FilterOn = True

I get error 2101 the setting you entered is not valid

I am assuming I am just not referencing the main form (frm_MainLanding) or the subreport (srpt_Report) correctly, but I can't seem to find a variation that closes the loop, am I trying to do something that I can't do, or can someone see my mistake?

The debug line that is highlighted is the first line.

Full module :

Option Compare Database
Option Explicit

Public Sub CallReport(reportname As String, reportfilter As String, Optional searchreport As String)

Forms!frm_MainLanding.srpt_Report.Form.SourceObject = reportname
Forms!frm_MainLanding!srpt_Report.Form.filter = reportfilter
Forms!frm_MainLanding!srpt_Report.Form.FilterOn = True

End Sub

 

[bastanu]

I believe in your first iteration you should replace ".Report." with ".Form." for the last two lines.

Cheers,

 

[theDBguy]

What are the descriptions for those error numbers? What are the values in your variables? How are you assigning them? Can you post your entire standard module code?

 

[christopherjoubert]

I edited the original post to include the info you requested @theDBguy the values are passed in as a string, specifically the name of a report, ie rpt_Report1 and the filter "[FIELDNAME] LIKE " variable "... or some permutation of that.

 

[moke123]

The filtering part should be easy as you can just pass in the sub report object.

Option Compare Database
Option Explicit

Public Sub CallReport(objSrpt as report, reportname As String, reportfilter As String, Optional searchreport As String)

Forms!frm_MainLanding.srpt_Report.Form.SourceObject = reportname

objSrpt.filter = reportfilter
objSrpt.FilterOn = True

End Sub

Setting the sourceObject is challenging as I believe it needs to be done in the load event, but not positive.