Parameters passed to multiple sub reports

[rodmc]

HI Folks

looking for a little help in passing parameters from a form to a report which has multiple sub reports.
Ive seen a few articles on how to pass parameters from a form to a report which has one record source but cant seem to find any demonstrating how to pass parameters from the form to multiple sub reports contained within the same main report.

Any help or links to anything that may help would be greatly appreciated.
thanks

rodmc

 

[vbaInet]

Does this mean that the subreports are not linked to the parent report?

 

[rodmc]

Does this mean that the subreports are not linked to the parent report?

morning

well the parent report doesnt really contain any data as such, my plan was to just have subreports from different queries getting their parameters dates from the one form and it would run all the subreports on the one main report.

What Ive thought about is creating individual reports as the article/tutorial Ive got demonstrates, and then dragging these individual reports on to a blank report.

The queries will all use the same dates from the form to filter. Am I making sense here?

 

[vbaInet]

Morning,

Use the report's Filter property to filter by the date from the form. Write the code in each subreport's On Load event:

    Me.Filter = "[DateField] = #" & Forms!FormName!DateTextbox & "#"
    Me.FilterOn = True

Instead of FilterOn = True, you can turn on the Filter On Load property in the property sheet but I prefer doing it in code as above.

 

[rodmc]

Morning,

Use the report's Filter property to filter by the date from the form. Write the code in each subreport's On Load event:

    Me.Filter = "[DateField] = #" & Forms!FormName!DateTextbox & "#"
    Me.FilterOn = True

Instead of FilterOn = True, you can turn on the Filter On Load property in the property sheet but I prefer doing it in code as above.

cheers bud, I'll give it a go!

thanks again

 

[vbaInet]

Oki doki. Keep us informed of your progress.

 

[rodmc]

just out of intrest, Im assuming i replace the code currently on the forms ok button from

DoCmd.OpenQuery "AHPsNotified", acViewNormal, acEdit

to

DoCmd.OpenReport "AHPsNotifedreport", acViewNormal, acEdit

cheers!

 

[vbaInet]

I'm unsure where you're coming from

The Source Object of the subreport control should be based on an already existing report. That report has a LOAD event. Put the code in the Load event of that report. The events are under the Events tab of the report's property sheet.

 

[rodmc]

ok, The form that collects the parameters has a OK button which beforehand I ran the following code to run the queries that the reports are based on.

Option Compare Database
Private Sub btnCancel_Click()
DoCmd.Close 'Close Form
End Sub
Private Sub btnOK_Click()
   Me.Visible = False
    'DoCmd.OpenQuery "AHPsNotified", acViewNormal, acEdit
    'DoCmd.OpenQuery "PracticeStaffNotified", acViewNormal, acEdit
    'DoCmd.OpenQuery "SecondaryCareNotified", acViewNormal, acEdit
    DoCmd.OpenReport "PracticeStaffNotified subreport", acViewPreview
    DoCmd.Close acForm, "frmPrintReport1"
End Sub

as you can see Ive commented out the queries at present. The code opens the queries based on the form parameters and worked quite well, but as I said I wanted the results of the different queries on one report hence my original question.

 

[vbaInet]

Why do you need to open or run the queries before you open the report?

Are they not SELECT queries?

 

[rodmc]

Why do you need to open or run the queries before you open the report?

Are they not SELECT queries?

yes, they are select queries. The subreports are based on said queries as I find them easier to manipuate (Im more used to ANSI SQL than anything else, which doesnt really help in access )

 

[vbaInet]

I didn't mention anything about openening the queries and you shouldn't even need to in the first place.

I think you should re-read my post #4

All you need to this is to put the code in the LOAD event of those subreports. Clicking the subreport twice will take you to the report object of the subreport control.

 

[SOS]

FYI for rodmc:

It is a common misunderstanding that you have to open a select query in order to "refresh" the data so it can be used in another query or report. You do not have to do this. You open the final report, based on the query (or report with subreports based on multiple queries) and all of the queries execute at that time.

 

[rodmc]

cheers guys, I'll give it a shot tomorrow

 

[rodmc]

If I wanted a start date and end date would it be case of adding another line of code in the Load event of the report but switching Start date with end date?

 

[vbaInet]

Me.Filter = "[DateField] BETWEEN #" & refToStartDate & "# AND #" & refToEndDate & "#"
Me.FilterOn = True

 

[rodmc]

Me.Filter = "[DateField] BETWEEN #" & refToStartDate & "# AND #" & refToEndDate & "#"
Me.FilterOn = True

vbaInet, youre a genius

cheers

 

[vbaInet]

Where's my trophy?

Happy to hear you're up and running.

 

[rodmc]

You'll never guess what, showed the users the reports today and they turned round and asked it they could just get it in excel

Its just as well guns arent legal in the UK.

 

[vbaInet]

Just charge them for the services