Parameters for multi SUB Reports

[teqtaq]

I am going to try my best to explain but it is not easy so I apologize in advance for confusion.

I am writing set of Reports based on data

Region Office USERID TypeOfBussiness
East NY A2 SchoolA
East NY A3 SchoolC
East NY A1 Store
East NY A5 School

West ATL A3 Store
West ATL A3 SchoolC
West ATL A3 Store

Central CHG B6 Store
Central CHG C9 Hotel
___________________________________________

I would create few SUB Reports
Schools
Region Office USERID TypeOfBussiness
East NY A2 SchoolA
East NY A3 SchoolC
East NY A5 School

STORES___________________________________________
Region Office USERID TypeOfBussiness
East NY A1 Store
West ATL A3 Store
West ATL A3 Store
Central CHG B6 Store

HOTELS
Region Office USERID TypeOfBussiness
Central CHG C9 Hotel

I woould place SUBS into MAIN Report and I need to have form that will give user option to run Report by Region, by Office or by TypeOfBusiness.
___________________________________________________________

Every SUB based on query Select* from Main_Data_table where Region="West" etc per Region

Every SUB based on query Select* from Main_Data_table where Office="NY" etc per Office

Every SUB based on query Select* from Main_Data_table where TypeOfBussiness="Store" etc per TypeOfBussiness
--------------------------------------------------------
Presently I am using code written on click of the cmd

Private Sub cmdRunReport_Click()
Dim strWhere As String
On Error GoTo ErrHandler

DoCmd.SetWarnings True

If Not IsNull(Forms!Reports!cmbTypeOfBusiness) Then
strWhere = "BusinessType =" & Chr(34) & Forms!Reports!cmbTypeOfBusiness.Column(0) & Chr(34) & _
" And [Region]=" & Chr(34) & Forms!Reports!cmbRegion.Column(0) & Chr(34)
End If


DoCmd.OpenReport ReportName:="Report2", View:=acViewPreview, WhereCondition:=strWhere
Exit Sub

Now I need to have SUB Reports to react to this Forms!Reports!cmbX parameters as well.

So I have a qyery per each SUB.
I am using following method.

I am having
Forms!Reports!cmbRegion or Forms!Reports!cmbRegion is null
Forms!Reports!cmbTypeOfBusiness or Forms!Reports!cmbTypeOfBusiness is null
Forms!Reports!cmbOffice or Forms!Reports!cmbOffice is null

and Report seems working and all taken care of...

Until I must had created Report with 12 SUBs and now my parameters method becoming an absolete!

Please, help me with another option for the parameters when having a lot of SUB Reports.

 

[MajP]

I have to look at this a little more when I have some time, but here is a thought. Sub forms and sub reports can be linked to controls as well as fields (the normal way). You can use this trick to synch multiple continous subforms on a form. So I think you can use the same trick here and have many hidden text boxes to link your subreports. Example: Have hidden text boxes called
txtBxRegion, txtBxBuisType, etc
Set the value of these textbox on the On Open event. i.e txtBxRegion = "East"

Now you can set up a master child link to txtBxRegion just like any subform/subreport. Also if necessary you can use composite keys.

This will save a lot of parameter queries.

 

[lameid]

Any reason you don't simply group on Type of Business in one report?

If it is to isolate the different types of business with their own criteria, you can write criteria to accomplish that.

 

[teqtaq]

I want to share with you how I solved this problem.

I've decided to treat each SUB as Main and removed all the Form!FormName!ControlName refferences from all queries.

Than I took this code

If Not IsNull(Forms!Reports!cmbTypeOfBusiness) Then
strWhere = "BusinessType =" & Chr(34) & Forms!Reports!cmbTypeOfBusiness.Column(0) & Chr(34) & _
" And [Region]=" & Chr(34) & Forms!Reports!cmbRegion.Column(0) & Chr(34)
End If


and placed it on each SUB's Report-open event and it seems working fine!

Thanks to all for thinking with me, you helped me to get there!