Selection sub reports

[taranto]

I have numerous reports that are created from seperate queries all of which are filtered on "between dates".

I want to have a master report that on some occaisions will contain some of these reports as subreports and on others a different combination of subreports.

Can I somehow create a form with an "include y/n" radio button that will enable selection of the required subreports on a particular occaision.

Is there a different way to achieve this, for example through a macro ?

Thanks for your time

 

[Uncle Gizmo]

I have numerous reports that are created from seperate queries all of which are filtered on "between dates".

I want to have a master report that on some occaisions will contain some of these reports as subreports and on others a different combination of subreports.

Can I somehow create a form with an "include y/n" radio button that will enable selection of the required subreports on a particular occaision.

Is there a different way to achieve this, for example through a macro ?

Thanks for your time

It is difficult for me to provide a meaningful answer because the answer would depend a lot on the structure of your existing database. If your database has been designed from the outset with the prospect of this type of report in mind, then it should be relatively simple to accomplish.

 

[taranto]

As an example I have a table for: 2 hourly weather observations, 6 hourly weather forecasts, daily incidents, diving operations and dredging operations.

A query is run on each of these tables daily to produce a "daily report" which is made up of all the above reports as subreports.
I also have queries on all the above tables so that details not only for a paticular day but a range of days is produced.

The daily report is fine, but I want to be able to produce one where I can include, or not, some of the listed reports (depending on clients required information).

I hope this has helped

 

[vbaInet]

Maybe we can see a print screen of your report as it is? You can hide certain subreports based on checkboxes by changing the Visible property of the subreport. All you do is check against the values of checkboxes on the Load event of the main report. Something like:

With Forms("NameOfForm")
     If .Controls("Checkbox1").value = True
           Subreport1.Visible = True
     Else
           Subreport1.Visible = False
     End If
 End With

This would depend on how the subreports are laid out. If they are stacked then you would need to code for filling up gaps that may exists between the subreports after hiding/unhiding.

 

[taranto]

I'm sorry for the long delay in responding. I do still have this question and have attached a screen shot for clarification.

The shot shows a daily report which is made up of sub reports. Some days I don't want all the subs included and so have to go down the list and change visible from yes to no. I would like to set up a form ? where there are radio buttons for each sub report which when ticked will include that sub in the main daily report

thanks

 

[vbaInet]

There are quite a lot of subreports on there.

The code you will put in the Open event of the main report will look like this:

With Forms("[COLOR=Red]NameOfForm[/COLOR]")
    Me.[COLOR=Red]Subreport1[/COLOR].Visible = [COLOR=Red].[/COLOR]Controls("[COLOR=Red]Checkbox1[/COLOR]")
    Me.[COLOR=Red]Subreport2[/COLOR].Visible = [COLOR=Red].[/COLOR]Controls("[COLOR=Red]Checkbox2[/COLOR]")
    Me.[COLOR=Red]Subreport3[/COLOR].Visible = [COLOR=Red].[/COLOR]Controls("[COLOR=Red]Checkbox3[/COLOR]")
    .
    .
    .
    etc ...
End With

 

[taranto]

Many thanks, I feel that I am on the way now. I have shown below just 3 lines for subreports as an example (as you say there are a lot). The name of my Db is "PoS Db". I am still getting a compile error "Expected end of statement". Can you see what my error is please ?

Thanks


Option Compare Database
With Forms("DR selector")

PoS Db.DR DutyAHM Query.Visible = .Controls("Option0")
PoS Db.DR Wx from River Broadcast Query.Visible = .Controls("Option2")
PoS Db.DR Diving Record Rpt.Visible = .Controls("Option4")

End With
Private Sub Report_Open(Cancel As Integer)
End Sub

 

[vbaInet]

1. Your code is not inside the Open event.
2. There's no where in my code where I mentioned referring to the name of your db.
3. You've not got a good naming convention going. See the link. If there's a space in the name of a control, it must be enclosed in square brackets, e.g.

Me.[DR DutyAHM Query].Visible = .Controls("Option0")

 

[taranto]

Thanks for that .

I had wrongly assumed that the "me" was referring to my Db name.

The code was written in the main form properties under the "Event" "On open" which automatically has inserted :

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

End Sub

before I type in any of the code that you provided me with.

Thanks for the square bracket advice as well.

Fingers crossed

 

[vbaInet]

So when it goes straight to the code window, the cursor blinks between the Private Sub and End Sub lines. That's where the code goes.

So:

Option Compare Database

Private Sub Report_Open(Cancel As Integer)
.... Code here ...
End Sub

 

[taranto]

Absolutely brilliant, works a dream

Thanks for all your help and patience

 

[vbaInet]

No probs. Happy to hear!