subform sourceobject

Posted on 2014-04-10
Last Modified: 2014-04-10

I am attempting to open a form then change the sourceobject of the subform.  Here is my code:

Private Sub btnEditRpt_Click()
Dim formname As String
Dim formfilter As String
    formname = [Forms]![frmReport]![frmReportReportList_Sub].[Form]![TemplateName]
    formfilter = "[autoReportsAll] = " & [autoReportsAll]
    DoCmd.OpenForm formname, , , [formfilter]
    Forms![xtemplate1]![MainReportForm].SourceObject = [ReportName]
End Sub

The form opens and filters fine, however, in the line that changes the SourceObject I would like to substitute "xtemplate1" (which is an actual form name) with a form name residing in a form field called "TemplateName". (as I did to open the form) .

I have two fields in the form/subform where this code executes from a button:  TemplateName and ReportName.

MainReportForm is the name of the subform object.

Help is appreciated.
Question by:cwbarrett
  • 3
  • 2
  • 2
  • +1
LVL 119

Expert Comment

by:Rey Obrero
ID: 39992036
if the name of the form you want to use is as source object is ReportName

Forms![xtemplate1]![MainReportForm].SourceObject = "ReportName"

maybe this is wht you are looking for

Forms(formName)("MainReportForm").SourceObject = "ReportName"
LVL 12

Expert Comment

ID: 39992205
Try this

me![MainReportForm].SourceObject = [TemplateName]
LVL 49

Accepted Solution

Gustav Brock earned 250 total points
ID: 39992475
That would be:

   Forms(formName)("MainReportForm").SourceObject = [ReportName]

LVL 119

Expert Comment

by:Rey Obrero
ID: 39992535
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.


Author Comment

ID: 39992659
Tried a few different things.  Still can't do it.  Here is the modified code.  The form opens but getting an error "cannot find the referenced form 'formTemplateName'" and debugging shows the last line as the culprit.  What am I doing wrong?

Private Sub btnEditRpt_Click()
Dim formTemplateName As String
Dim formfilter As String
Dim subformSourceObject As String

    formTemplateName = [Forms]![frmReport]![frmReportReportList_Sub].[Form]![TemplateName]
    formfilter = "[autoReportsAll] = " & [autoReportsAll]
    subformSourceObject = [Forms]![frmReport]![frmReportReportList_Sub].[Form]![ReportName]
    DoCmd.OpenForm formTemplateName, , , [formfilter]

    Forms![formTemplateName]![MainReportForm].SourceObject = [subformSourceObject]
End Sub

Author Comment

ID: 39992679
Why does formTemplateName work to open the form (DoCmd.OpenForm formTemplateName, , , [formfilter])  but doesn't work when referencing it to set the SourceObject?
LVL 12

Assisted Solution

pdebaets earned 250 total points
ID: 39992891
Try this:

Forms([formTemplateName])![MainReportForm].SourceObject = [subformSourceObject]

Author Closing Comment

ID: 39992950
Thank you.  You both were a great help.

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now