A Report/Subreport problem

[Tony Williams]

I have a main report that is based on a query qryreport1 and which has 6
subreports. There is one control on the main report, txtmonthlabel (a
date/time field in my table) which is used to link all the subreports. The
subreports are also based on the same query and have a number of controls
including txtmonthlabel and txtcompany. I have a form at the front end that
the user chooses to view records for all companies or chooses only one and
then chooses a date range. The sub reports should all look like this if the
user chooses to view all companies but only one company if they choose to
view a selected company:

Company1 (which is the txtcompany control )
March 2005........data1............data2...........data3
June 2005...........data1............data2...........data3
September 2005.data1............data2...........data3
December 2005..data1............data2...........data3

Company2
March 2005........data1............data2...........data3
June 2005...........data1............data2...........data3
September 2005.data1............data2...........data3
December 2005..data1............data2...........data3

etc etc etc

However what I'm getting is even if I choose just one company:
Company1
March 2005........data1............data2...........data3
Company2
March 2005........data1............data2...........data3
Company1
June 2005...........data1............data2...........data3
Company2
June 2005...........data1............data2...........data3

The code behind my OnClick event on the front end form is this
Private Sub cmdOK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yy\#"

strReport = "rptsense3"
strField = "txtmonthlabel"


If IsNull(Me.txtstartdate) Then
MsgBox "You must enter a start date", vbOKOnly, "Missing Start Date"
Me.txtstartdate.SetFocus
Else

If IsNull(Me.txtenddate) Then
MsgBox "You must enter an end date", vbOKOnly, "Missing End Date"
Me.txtenddate.SetFocus
Else
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)


If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = """ & Me.cmbselectcompany & """"
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End If
End If
End Sub


Sorry this is so long but I wanted to make sure you had all the relevant
facts. Hope I've not missed anything.
Any help would be appreciated.
Thanks
Tony

 

[Steve Schapel]

Tony,

One of the oddities I noticed in your code is...
strWhere = strWhere & " AND cmbCompany = ...
whereas in your description you mention txtcompany, so I would have
expected...
strWhere = strWhere & " AND txtCompany = ...

Anyway, I would suspect that the issue here is not really to do with
your code, it is more to do with your report design itself. Did you
mean that you have 6 subreports and they are all based on the same
query? This is almost certainly more complicated than necessary.
Probably you shouldn't have any subreports at all. Probably the whole
report should just be based on the one query, and the layout controlled
via the Sorting & Grouping options. From what you have told us so far,
I can't really figure out what you've got. Maybe you could post back
with the SQL view of the query, and some more specific description of
your report design.

 

[Tony Williams]

Thanks Steve.
The cmbcompany is the name of a combobox on my front end form from which the
user can select the company for the report or if it's left blank the report
shows all companies. txtcompany is the name of the equivalent control on my
report and each of the subreports. Here is the outline of the report as it
should appear.

Category1 (this is merely a label control)
Subreport1
Company1 (which is the txtcompany control )Category2
Subreport2
Company1 (which is the txtcompany control )Category3
Subreport3
etc etc etc

Here is the sql of the query:
SELECT tblCompany.cmbCompany, tblmaintabs.txtMonthlabel,
tblmaintabs.txtCompany, tblmaintabs.txtDomfacsoleytd,
tblmaintabs.txtDomfacparttyd, tblmaintabs.txtDomfactotytd,
tblmaintabs.txtDomidsoleytd, tblmaintabs.txtDomidpartytd,
tblmaintabs.txtDomidtotytd, tblmaintabs.txtExpfacsoleytd,
tblmaintabs.txtExpfacpartytd, tblmaintabs.txtExpfactotytd,
tblmaintabs.txtExpidsoleytd, tblmaintabs.txtExpidpartytd,
tblmaintabs.txtExpidtotytd, tblmaintabs.txtImpfacsoleytd,
tblmaintabs.txtImpfacpartytd, tblmaintabs.txtImpfactotytd,
tblmaintabs.txtClientsolesaletotytd, tblmaintabs.txtClientpartsaletotytd,
tblmaintabs.txtClientsaletotytd, tblmaintabs.txtNonrecpartytd,
tblmaintabs.txtNonrecsoleytd, tblmaintabs.txtNonrectotytd,
tblmaintabs.txtAllcppsoleytd, tblmaintabs.txtAllcpppartytd,
tblmaintabs.txtAllcpptotytd, tblmaintabs.txtDomfacsole,
tblmaintabs.txtDomfacpart, tblmaintabs.txtDomfactot,
tblmaintabs.txtDomidsole, tblmaintabs.txtDomidpart, tblmaintabs.txtDomidtot,
tblmaintabs.txtExpfacsole, tblmaintabs.txtExpfacpart,
tblmaintabs.txtExpfactot, tblmaintabs.txtExpidsole,
tblmaintabs.txtExpidpart, tblmaintabs.txtExpidtot,
tblmaintabs.txtImpfacsole, tblmaintabs.txtImpfacpart,
tblmaintabs.txtImpfactot, tblmaintabs.txtClientsolesaletot,
tblmaintabs.txtClientpartsaletot, tblmaintabs.txtClientsaletot,
tblmaintabs.txtNonrecsole, tblmaintabs.txtNonrecpart,
tblmaintabs.txtNonrectot, tblmaintabs.txtAllcppsole,
tblmaintabs.txtAllcpppart, tblmaintabs.txtAllcpptot,
tblmaintabs.txtAdvancessole, tblmaintabs.txtAdvancespart,
tblmaintabs.txtAdvancespartnp, tblmaintabs.txtAdvancesassetssole,
tblmaintabs.txtAdvancesassetspart, tblmaintabs.txtAdvancesassetsnp,
tblmaintabs.txtAdvancesothersole, tblmaintabs.txtAdvancesotherpart,
tblmaintabs.txtAdvancesothernp, tblmaintabs.txtAdvancestot,
tblmaintabs.txtDebtoutsole, tblmaintabs.txtDebtoutpart,
tblmaintabs.txtDebtouttot, tblmaintabs.txtStocktot,
tblmaintabs.txtpropertytot, tblmaintabs.txtplanttot,
tblmaintabs.txtOthertradsole, tblmaintabs.txtOthertradpart,
tblmaintabs.txtOthertradtot, tblmaintabs.txtClientsdomfacsole,
tblmaintabs.txtClientsdomfacpart, tblmaintabs.txtClientsdomfactot,
tblmaintabs.txtClientsdomfacABLsole, tblmaintabs.txtClientsdomfacABLpart,
tblmaintabs.txtClientsdomfacABLtot, tblmaintabs.txtClientsdomidsole,
tblmaintabs.txtClientsdomidpart, tblmaintabs.txtClientsdomidtot,
tblmaintabs.txtClientsdomidABLsole, tblmaintabs.txtClientsdomidABLpart,
tblmaintabs.txtClientsdomidABLtot, tblmaintabs.txtClientsexpsole,
tblmaintabs.txtClientsexppart, tblmaintabs.txtClientsexptot,
tblmaintabs.txtClientsexpABLsole, tblmaintabs.txtClientsexpABLpart,
tblmaintabs.txtClientsexpABLtot, tblmaintabs.txtClientsimpsole,
tblmaintabs.txtClientsimppart, tblmaintabs.txtClientsimptot,
tblmaintabs.txtStockfinsole, tblmaintabs.txtstockfinpart,
tblmaintabs.txtstockfintot, tblmaintabs.txtstockfinABLsole,
tblmaintabs.txtstockfinABLpart, tblmaintabs.txtstockfinABLtot,
tblmaintabs.txtTotNbrClientssole, tblmaintabs.txtTotNbrClientspart,
tblmaintabs.txtTotNbrClients, tblmaintabs.txteurovaluemain,
tblmaintabs.txtAdv0, tblmaintabs.txtAdv500, tblmaintabs.txtAdv1000,
tblmaintabs.txtAdv5000, tblmaintabs.txtAdv10000, tblmaintabs.txtAdv50000,
tblmaintabs.txtAdv100000, tblmaintabs.txtAdvTot, tblmaintabs.txtClients0,
tblmaintabs.txtClients500, tblmaintabs.txtClients1000,
tblmaintabs.txtClients5000, tblmaintabs.txtClients10000,
tblmaintabs.txtClients50000, tblmaintabs.txtClients100000,
tblmaintabs.txtClientsTot, tblmaintabs.txtFactClients,
tblmaintabs.txtDiscClients, tblmaintabs.txtFAClientsTot,
tblmaintabs.txtMemberNotes, tblmaintabs.txtCalcMethod,
tblmaintabs.txtFLANotes, tblmaintabs.txtIndMan, tblmaintabs.txtIndDist,
tblmaintabs.txtIndServ, tblmaintabs.txtIndTran, tblmaintabs.txtIndRetail,
tblmaintabs.txtIndConst, tblmaintabs.txtIndOther,
tblmaintabs.txtIndClientsTot
FROM tblCompany RIGHT JOIN tblmaintabs ON tblCompany.cmbCompany =
tblmaintabs.txtCompany;


I broke it down into 6 subreports as I thought that it would be better to
maniulate the design of 6 small reports rather than try and get all these
fields onto one report.

Is this any help?
Thanks again
Tony

 

[Tony Williams]

Steve I read your answer to kate's question this is I think what I'm trying
to do. However my problem is how do I get my front end form to select the
company and date range which are the same for my 6 subreports using the
front end form and the code I have posted?
Thanks
Tony

 

[Tony Williams]

Steve I've now got my subreport1 as my main report and sub reports 2-6 as
subreports of that. They are linked firstly on txtcompany and then on
txtmonthlabel.The front end form selects the company OK for the main report
and the subreports but only selects the date range for the main form and the
subreports only show the data for the end date. I think I'm getting closer!
But still need help with this.
Thanks
Tony

 

[Tony Williams]

Solved my problem just got the filter working on the query.
Tony