Dynamically set subreport source object (1 Viewer)

On my main nav form in my Access 2010 db, I have a tab called 'Offerings' that displays a form with 2 unbound combo boxes. The 1st combo box displays a list of offerings from a field in one of my tables, and is used in the criteria of corresponding fields in my queries that feed my reports. The 2nd combo box is a list of report names.

I then have an unbound subreport, and a command button. The command button has an on click event procedure that is supposed to update the source object of the unbound subreport to the desired report, thereby allowing the user to select the offerings that they want to view, the desired report associated with that offering, and have the correct report with the correct data set display in the subreport object.

It all works beautifully for the 1st 'offering' listed in combo box #1. I can pick the report, click the command button, and view the desired report with the desired record set in the subreport object. However, if I select any other 'offering' in the list, it displays a blank record set. The correct headings for the fields in the selected report are displayed, so it is changing the report appropriately, there is just no data.

Interestingly, if I set combo box #1 to the desired offering and just open the desired report directly from the navigation pane, the report displays correctly.

My on click code for the command button is:

Private Sub cmdRunReport_Click()

Dim strSubRptName As String

strSubRptName = "Report.srp" & Me!cmbReport.Column(0)

Me!srpChild.SourceObject = strSubRptName

End Sub

The 'Offering' field in each of my queries used by my forms has [Forms]![Main]![NavigationSubform].[Form].[cmbDealSelect] as the criteria.

I'm quite stumped and running up against a deadline. Any help would be tremendous.

You just need the actual name of the report. You don't need Report. part in there.

So, if the name of the report is

sbrptTest you would just use

strSubRptName = "sbrptTest"
Me.SubreportControlNameHere.SourceObject = strSubRptName

So if the combo has the actual name of the report, you would do the same:

strSubRptName = Me.ComboBoxNameHere
Me.SubreportControlNameHere.SourceObject = strSubRptName

and you may have to set the master/Child links too, if that is necessary for the subreport to set properly.

Bob,

Thanks for the quick reply. I should have specified that the values in the combo box are the names of the report, minus the srp that I use to signify a subreport. ie., the value 'History' should call the report srpHistory, just to make a slightly cleaner user experience. That's why I have strSubRptName = "Report.srp" & Me!cmbReport.Column(0).

I tried dropping the Report. part, but it gave me run-time error 2101.

Is there a better way to concatenate the 'srp' and the combo box value to accurately reflect the actual name of the report?

Thanks,
Craig

Why not just have the actual name in a hidden column in the combo and then have that as the bound column?

Second best is

strSubRptName = "srp" & Me!cmbReport.Column(0).

I swear, sometimes the most obvious solution escapes me. I'll give it a try with a hidden column and report back.

I had already tried the "srp" & Me!cmbReport.Column(0). solution, but received back error 2102.

No dice. I still get Run-time error '2101': The setting you entered isn't valid for this property.

ncraigg said:

No dice. I still get Run-time error '2101': The setting you entered isn't valid for this property.

Click to expand...

Can you post a copy of the database (with bogus data)? Make sure to compact and repair and zip it up first.

Indeed. Please see attached. Thanks

Bob,

Sorry to double up on you, but while you have the db, would you mind telling me the best way to make the subreports sortable based on the field headings? I added what I thought was the correct code to the on click event for the field names, but the result was that the 1st click made the data disappear, and the 2nd click made it reappear.

Thanks,
Craig

Oh, and I guess the username and password would help (though they are not entirely necessary). They are test and test, respectively.

Well, first off - without the backend data file I can't test anything.

Second, you shouldn't be using a value list for the combo box's row source. Use a table (you can also use the MSysObjects table to display those reports with srp as the first three characters if you wish, so it automatically will add any you add later). See my example here on how to do something of that nature:

http://downloads.btabdevelopment.com/Samples/listbox/SampleSelectReportFromForm.zip

Next, you seem to be trying to display a report on a form. For subforms (not subreports) you would use a subFORM to display the values on the form.

Sorry, I'm just not with it today. The backend is attached.

Re: the subreport vs. subform, is it not possible to display a report within a form? (sounds very basic and naive, I know, but I've done so in the past with no problem)

Bob,

With the assumption that the problem was related to displaying a report within a form, I created a form that displays the same data as one of the reports that I've been using. I then updated the on click event procedure to use the form name rather than the report name.

The result is the same. If I select the first list item in combo box #1, it displays all of the data correctly. If I select any other list item in combo box #1, the subform is blank. The subform fields display, but there is no data.

To see if I could get it to work for a subform, I took the 2nd combo box entirely out of the equation. I changed strSubRptName = "frm" & Me!cmbReport.Column(0) to strSubRptName = "frmHistory", which is the form that I created to use as my test subform. Same result. It works for list item #1 in combo box #1, but no other list item.

Finally, I took the command button out of the equation. I just went in to properties and set the source object for the subform to "frmHistory". Same Result! It displays correctly for the 1st list item, but it is blank for every other list item.

Yet, if I open frmHistory directly in the navigation pane, it displays the correct data for whichever list item is selected on in combo box #1 of the nav form. At this point, I think I've proven that it is not a problem with the on click event procedure of the command button, or that I am using a subreport within a form, but rather there is something else (likely more fundamental) going on here.

After having looked at the database, do you have any ideas? I'm stumped.

Thanks,
Craig

I haven't had opportunity to go actually play with it since my first go (still at work so am limited on what I can do while at work) but I should be able to look at it soon.

No worries at all. I very much appreciate your time.

So I've tried a couple more things to try to isolate the problem, but I haven't gotten any closer. 1st, I added the 'offerings' field from the query to the report and discovered that, as you would expect based on the problem, the subreport, or subform, because I tried both, doesn't show the 'offering' when it is opened in the nav form, even though it displays properly when opened as a standalone form/report.

Therefore, I got the bright idea that somehow I was referencing the combo box incorrectly from within the subform/report. I therefore set the on change property of the combo box to run an event procedure to set a TempVar (tmpOffering), and changes the criteria of the query that drives the subform/report to TempVars![tmpOffering]. The result is the same. The query displays correctly, the report displays correctly, but when opened within the nav form, no data displays.

I don't know if this helps the thought process, but wanted to pass it along to help rule out possibilities.

I finally figured it out. I knew it had to be something more fundamental. When I originally created the form that the Offerings tab utilizes, I set the Record Source to the name of the table that provides the list of offering names. When I just now deleted the table name from the Record Source (which isn't necessary because the combo box that provides the list of offering names is an unbound combo box anyway), it corrected the problem.