Report with "dynamic" subreports? 1

[NKA]

Can anyone help please?

To generate a Job Transaction report, the user is asked to select certain criteria.

First they select the reporting period, which determines which Make Table query is used. Then they select the output option (Cost$, Sell$, Both$ or No$).

The report has 2 subreports on it. I would like to be able to change the recordsource of the subreports based on the selected output option but do not know how this can be done.

I would be most grateful if anyone has managed to achieve this and what code should I be using.

Many thanks.

NKA

The answer's always easy - if you know it! ;-)

 

[KenReay]

Hi

You could define n Subreports, on your main form have just one subform control

have code in the after update event of the reporting period control

have code like

Select case Period
Case "X"
SubFormControl.Source = "MySubForm1"
Case "Y"
SubFormControl.Source = "MySubForm2"
...etc
End Select

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dhookom]

I would use code to change the SQL of the subreport's recordsource query. Use a function like:
Function ChangeSQL(pstrQuery as String, pstrSQL as String) as String
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
ChangeSQL= qd.SQL
qd.SQL = pstrSQL
Set qd = nothing
Set db = Nothing
End Function

Then use a Select Case in your code prior to running the report.
Dim strOldSQL as String
Select Case Me.optSelectSub
Case 1
strOldSQL = ChangeSQL("qselMyQuery", "SELECT ... &quot
Case 2
strOldSQL = ChangeSQL("qselMyQuery", "SELECT ... &quot
End Select

Duane
MS Access MVP

 

[NKA]

Thank you both for your kind suggestions, but I don't think I made myself very clear in the first instance as it's not so much the code that is the issue here, rather than the 'display'.

To produce the desired effect, I have created a report with subrpt1 and subrpt2 (one for actual costs and one for committed costs - they need to be separate).

However, to get the 4 variations, either I need to create a total of 8 subreports (for cosmetic viewing) or do some fancy code to hide/show fields and labels - both options having advantages and disadvantages - which is why I thought I would go down the route of having 8 subreports and try to change the subreport source when selection is made.

I have tried looking into the event procedures when opening the report. Me.subrpt1.recordsource = ??? doesn't work and I don't know what will now!

Looking forward to a solution )

NKA

The answer's always easy - if you know it! ;-)

 

[dhookom]

Your first question was about changing the recordsource of subreports. Now it looks like you want to change the source object of the subreport control. Which is the real question?

Apparently your subreport are different enough that it wouldn't be easy to change the labels and other controls. Is this true?

You could add all subreports and then just make the appropriate ones visible.

Duane
MS Access MVP

 

[NKA]

Sorry for confusion. Yes, I need to change the sourceobject. If I load all subreports, at which point to do I tell them to be visible/invisible - I know reports work slightly different to forms.

Will I have to effectively open the report in design mode before sending to preview/print???


NKA

The answer's always easy - if you know it! ;-)

 

[dhookom]

You can run code in the On Format event of the section containing the subreports.

Me.srptA.Visible =Forms!frmA!txtWhichSub = "A"
Me.srptB.Visible =Forms!frmA!txtWhichSub = "B"


Duane
MS Access MVP

 

[JohnHarkins]

I just went through something similar (actually, it's taken months to accomplish) and my "solution" was to create nearly a dozen separate subreports. The client had a Word application wherein various criteria determined what format to choose. The format for a given item was determined not only by kind of item, but several other factors. The only thing I could come up with was to build a query that "analyzed" the criteria, assigned a particular format number then selected the appropriate format when the assigned format number was presented to the detail section of the main report.

I hate this kind of workaround, because the maintenance on so many formats (oh, yes, the customer wanted fonts, italicization, boldness, vertical lines that grew when the boxes weren't big enough to hold all the data, and control of "overflow" lines) is horrendous. And, you can be sure that it won't be long before I get an emergency call saying that we need another format. Ugly, ugly, ugly.

Hope your case is simpler than mine. I do have to say that I learned a lot about Access' reporting events.

John Harkins