Set a Subform's SourceObject from a different subform on the main form

[GrandMasterTuck]

[SOLVED] Set a Subform's SourceObject from a different subform on the main form

Hi folks.

I have frmMain, and on frmMain is a tab-control with a control called tabPeople. This tab-control has two controls, both on the same tab: ctlPeopleList (a subform control that shows sbfmPeopleList, a continuous form with all employee records in it) and ctlCardPreview (a subform control who's control source I wish to dynamically set). ctlCardPreview needs to display one of three reports: rptEmployee, rptContractor, or rptManager.

What I want to do is choose which report to show in ctlCardPreview by double-clicking a record in ctlPeopleList . ctlPeopleList has a field called [EmployeeType] which is set to either Employee, Contractor or Manager for all records. I want the system to choose which report to put in that ctlCardPreview control by looking for a report that matches the name of the EmployeeType.

Is the most elegant way to do this with VBA or a Macro (can it even be done with a Macro?)?

I tried to create a VBA Function called SetCardType() with the following code that executes on double-click for that record on ctlPeopleList:

Forms!frmMain!tabPeople.Form!ctlCardPreview.SourceObject = "Report.rpt" & [EmployeeType]

Also tried this:

Me!ctlCardPreview.SourceObject = "Report.rpt" & [EmployeeType]

And this:

Me.ctlCardPreview.SourceObject = "Report.rpt" & [EmployeeType]

...aaaaand it doesn't work. Any ideas? Again, this needs to take data from a record on ctlPeopleList (the recordID and EmployeeType) and use the recordID to choose which record to fetch and use the EmployeeType to choose which report shows up in ctlCardPreview. Two separate controls on the same subform. Neither of the controls is embedded in the other (no forms inside forms, other than the subform inside the tab control with these two offending subforms... confusing, I know..)

Thanks for any help you guys are able to give!

 

[pbaldy]

If the reports have the same names as in the control, simply:

Me.ctlCardPreview.SourceObject = [EmployeeType]

You may need to set a variable first. By the way, tab controls are not part of a form/subform reference.

 

[GrandMasterTuck]

That's just it, the report ALMOST has the same name. It's got a "rpt." in front of it. So if the EmployeeType is "Employee", it needs to send "rpt.Employee" to that control.

When I do it like you suggested, I get an 'Invalid use of Me' VBA error. Is that because I put the "Me.ctlCardPreview.SourceObject = "rpt." & [EmployeeType] in a Function and call the Function on double-click? I'm thinking the issue I'm having is that Access is 'losing' the location of the destination control into which I want this report loaded, because the double-click action is contained on a subform that's in a completely different control on the same form that has the destination control.

Like this:

Main Form
_______________________________________
|  Tab Control                        |
|                                     |
|  CtlPeopleList   CtlCardPreview     |
|  -------------   --------------     |
|  |           |   |            |     |
|  | clickable |   |  rpt goes  |     |
|  |  record   |   |    here    |     |
|  |           |   |            |     |
|  -------------   --------------     |
|                                     |
---------------------------------------

Forgive the crappy ascii representation... but this is basically what I have. Why do I get that 'Invalid use of Me' error? The clickable record executes the Function, and it always fails.

 

[pbaldy]

You can't use Me in a function in a standard module, so if it's there it would need the full form reference. Yes, the correct syntax for the report name would be:

...SourceObject = "rpt." & [EmployeeType]

 

[GrandMasterTuck]

Okay, now I get "object doesn't support this property or method" and it highlights this line:

Forms!frmMain!tabPeople.Form!ctlCardPreview.SourceObject = "rpt" & [EmployeeType]

EDIT: I tried changing the path somewhat, taking into account what another poster said about the tab control not being part of the path, and tried this:

Forms!frmMain!ctlCardPreview.SourceObject = "rpt" & [EmployeeType]

And I got "The setting you entered isn't valid for this property"

I've attached the database to this post. Thanks for the help! By the way, I know there's a broken query in there right now, so the report, if and when it comes up in that ctlCardPreview object, will not return any data. I didn't program the TempVar assignment macro yet.

 

[Frothingslosh]

What version of Access are you using?

 

[GrandMasterTuck]

Access 2010

 

[Frothingslosh]

Okay, so much for that idea - the ability to view reports as subforms is only available in A2010 and up, but you have that covered. Is this, by chance, a database that was created initially in A2007? Because as far as I'm aware, 'Me.Parent.ctlCardPreview.SourceObject = "rpt" & [EmployeeType]' should work. Does it work when you just use rptEmployee instead of the concatenated string?

 

[GrandMasterTuck]

Is this, by chance, a database that was created initially in A2007?

Nope. Created by me earlier this week on my Access 2010 PC at work.

I have now modified the form so the 'CardPreview' control is gone. Now double-clicking the ID in the PersonList opens a popup form, the popup form has ctlCardPreview on it, and on the popup form's OnLoad event, I placed this:

Me.cltCardPreview.SourceObject = "rpt" & [EmployeeType]

And that doesn't work, either. I get the same error as before.

Does it work when you just use rptEmployee instead of the concatenated string?

It does not. I replaced the above code with this:

Me.ctlCardPreview.SourceObject = "rptEmployee"

And I get an error message that says "The setting you entered isn't valid for this property"
I may officially be going crazy. What am I missing??

 

[Frothingslosh]

Well, going from the subform was fine, you just use Me!Parent.SubformControlName.Whatever.

It SHOULD be working - I use something similar in one of my projects, albeit using an option group rather than a subform:

Private Sub fraOptionList_AfterUpdate()
  
Dim FormName As String
Dim FormCaption As String
  
     Select Case Me.fraOptionList.Value
        Case 1      'Users
            FormName = "frmAdmin_tblUsers"
            FormCaption = " Users"
        Case 2      'Process Status
            FormName = "frmAdmin_ProcessStatus"
            FormCaption = " Process Status"
        Case 3      'Info From Ind
            FormName = "frmAdmin_InfoFromInd"
            FormCaption = " Confirmation Sources"
        Case 4      'Maintenance
            FormName = "frmAdmin_Recycle"
            FormCaption = " Maintenance"
        Case 5      'Rights
            FormName = "frmAdmin_RightsMain"
            FormCaption = " Rights"
        Case 6      'Reports
            FormName = "frmAdmin_Reports"
            FormCaption = " Reports Maintenance"
        Case 7      'Rationales
            FormName = "frmAdmin_Rationales"
            FormCaption = " Confirmation Rationales"
    End Select
    
    Me!sfmSubform.SourceObject = FormName
    Me.lblSubform.Caption = FormCaption
    Me.Refresh
    Me.sfmSubform.SetFocus
    
End Sub

 

[GrandMasterTuck]

For the love... I just figured out why it's not working for me, thanks to the post above's code example. I'm looking at it and I realize that he is using FORMS as his SourceObjects. The issue is that I was trying to set the SourceObject to a REPORT, not a FORM. When I created individual FORMS for each report, I was then able to set the FORMS as the SourceObject of ctlCardPreview.

As to why it doesn't work when you try to set the SourceObject to a REPORT, I have no idea. Possibly this is unsupported, even though you can place the report onto the form this way? I dunno. Anyway, thanks all around for the input and assistance. Mark this one SOLVED.

*sigh* I wonder when I'll get used to the Access quirks.

 

[Frothingslosh]

Everything I read said that in 2010 and later, reports as subforms is supported. In fact, now that I'm home and have 2010 available to me (it's 2007 at work), I'm going to look into it a bit more on general principle.

 

[Frothingslosh]

Aaaaand fixed it.

Private Sub ID_DblClick(Cancel As Integer)
    Forms!frmMain!ctlCardPreview.SourceObject = "[COLOR="Green"]Report.[/COLOR]rpt" & [EmployeeType]
End Sub

The change is in green. Tested and it worked - the report would show, although it would have no data due to that busted query you mentioned.

Also, you should ALWAYS use Option Explicit after the Option Compare statement.