One Report for multiple subforms 1

[chanman525]

I have a form that has multiple subforms that are populated based off of a multi select control. All sub forms have the same fields, it is just filtered based off of what is selected in its associated list box.

What i would like to do is have a single report that displays the filtered selection from the subform.

here is what it looks like on a single form. ( 2 multi-select boxes and 2 subforms)

[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
multi select field 1 Subform display selection 1
-------------------- -------------------------
Open (Selected) display all Open records
Closed


multi select field 2 Subform display selection 2
-------------------- -------------------------
Access display all Word records
Aix
Word (Selected)
]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]

At each multi select there is a "Get Report" button that will call the report and display the data in the associated subform.

If I hard code the query associated to a select box it works fine. But what i want to do is change the record source of the report based no which "Get Report" button is pressed.

I Hope this post is not to confusing, i tried to clearly explain and display what the delima is. Any ideas would be appreciated and thanks for the time to reivew this post.

 

[lameid]

As your question is about a form and not a report you should have posted in forum702.

But to answer your question, you can have each query behind each subform use the control on the main form as criteria. Just open the query and in the criteria under the correct field right click and click build. You should be able to work your way in the panes at the bottom from left to right to get to the correct control. Just double click it when you see it. Next, on the after update event of your list box you will need to requery each of your sub forms.

The below line is an example of requerying a subform on the mainform:

Me![Sub Form Control Name].Requery

 

[chanman525]

Lameid;
The question was about populating a report based off of multiple sub-forms, so is this not considered a report question?

To reply to you; The main form does not have any controls other than the list box and subforms. They are each named independantly but work in pairs, one list box per subform and each subform is controlled by its own query.

here is a link to what the form looks like.;

http://picasaweb.google.com/xscatolare/TekTips/photo#5079990906175845394

Ideally the focus at this point is the form but it is setting the ground work for how I can incorporate a single report based off of multiple subforms, simply by modifying the record source of the report(which would be the individual queries behind each subform) depending on which button you press.

Hope this helps clear up the question.

 

[lameid]

You talked so much about subforms it didn't seem like a report question, I lost that line...

So you want to use the same report object and use completely different recordsources and opened on different button click events.

Use a global variable for your Record source...

'Moduale
Global strReportRecordSource as String

Sub IntializeGlobals
    'Run this at startup either via runcommand in autoexec
    'macro or Startup form's on open event
     
     strReportRecordSource = "" 'or set it to your default
                                'recordsource
End Sub

Next on the forms On open event set the record source...

     Me.Recordsource = strReportRecordSource

Finally, set strReportRecordSource to the appropriate recordsource before calling it. You could also detect a zero length string on the open event and instead use whatever recordsource you want. The problem with this method is it is hard to maintain if you don't know what is going on.

 

[chanman525]

I think I can see where you are going with this but I would like to confirm with you.

The goal is;
button1 clicked = RptSummary record source is set to Qry1
button2 clicked = RptSummary record source is set to Qry2
button3 clicked + RptSummary record source is set to Qry3

On your previous statement you stated to place me.recordsource = globalvariable on the forms On Open event. Is this a type-o and should be set on the reports on open event?

 

[chanman525]

lameid,
I did what I mentioned above and put it on the on open event of the report and it worked great! Thanks for the code snippet!! This can be a useful option for a lot of things.

For those that may be interested in using something like this...

on the Click Event simply set the recordsource to what you want prior to calling the report.

strReportRecordSource = "qselRequestorFromListBox"
DoCmd.OpenReport "RptProjectSummary", acViewPreview

and on the report simpley set the recordsource on the on open event to

Me.RecordSource = strReportRecordSource

and you are good to go.

 

[lameid]

Glad you got it working... I can't believe I wrote forms and meant report after getting it confused the first time.

I must be experiencing a caffein deficicency or something.