Working with subreports in a Report

[mfaisalwarraich]

I made a report in design view by importing 4 sub reports. these sub reports based on 4 different queries. each query's search criteria is dateField. if i run the report i have to enter dateField's value 4 times. is there any way to enter date only one time to run 4 queries? please tell me. thank u.

[nico5038]

Best to create a "frmReports" with a field "txtSelectionDate" and a button to start the report (Just follow the button wizard)

Now use in the report queries for the datefield the criteria:
forms!frmReports!txtSelectionDate

Just make sure that the field holds a valid date before pressing the button !

Nic;o)

[mfaisalwarraich]

it really worked :) thanks a lot my friend :) im grateful to you.

well here is another question for you :) i just dont wana let u run from me. get ready ok.

is it possible that i can make reports dynamic format. means if a reports has 50 records then this report should not overlap with other report on the page. after the records of first report finished then other report should start. what should i do now?

and if any report does not have any value then "No Records Found" or "Nil" can be written on that place with heading of that report.

[nico5038]

Best to post two new questions. TheScripts likes to have one question per thread, thus it's easier for other people (e.g. searching a solution with Google) to find and read the info they are looking for. Just send a PM (Personal message) with the URL's of these new threads when you get no response from our experts and I'll "jump in".

Nic;o)

[mfaisalwarraich]

hi, i acted upon nico's advise and search a lot over the web to find out my solution for this problem. surprisingly :) i found solution by default.

but there is one problem that im still facing and failed to get solution over the web. if there is no data in report then a text box should appear on the same report with the value "No Records Found".

i found solution by putting some code into OnNoData event of the report and its working but only working when i open the report alone. when i open this report as a subreport in other report (made in design view), this report is not even visible. i failed to understand why. i tried my level best but i did not find any solution. so im requesting you again to please tell me how i would write a value into a text box if there is no record found in a subreport. thank u

[nico5038]

This can be done in the code of the mainreport by testing or the subreport's query will return records and then fill (or make visible) a label on the mainreport.

Nic;o)

[mfaisalwarraich]

thank u for ur quick reply. but as i told u i tried my best by putting some conditions on the main report. but if u would please tell me how i can do this with some code.

thank u.

[nico5038]

You can use the RecordsetClone method. Set a DAO.recordset variable based on the Reports!YourReportName!YourSubReportName.Report.Re cordsetClone.
Next test for the EOF and BOF being true and the subreport will be empty.

Nic;o)

[mfaisalwarraich]

:) what i can say now? im not an expert friend :). i haven't used DAO.Recordset before :) but i read about it on the web. really it is difficult for me to get the rights syntax. however, i made some effort to solve my problem here how i am doing :)
but im getting error on the second line. txtNoData is a text box with its visible property set to no.

1. Private Sub Report_Open(Cancel As Integer)
2. Dim rs As DAO.Recordset
3. Set rs = Reports!mainReport!subReport.Report.RecordsetClone

4. If (rs.EOF And rs.BOF) = True Then
5. Me!txtnoData.Visible = True
6. Me![txtNoData] = "No Records Found"
7. End If
8. End Sub

here is what im using on the ON OPEN event of the main report. please tell me where im making a mistake. as i told u above i got error at line number 2. please tell me how to solve this problem. thank u.

[nico5038]

It would help when you would post the error message, but I guess that the needed DAO library reference isn't set.
Open in VBA mode Tools/References. Remove a checkmark when you find a reference to "Microsoft Active Data Object" and make sure a reference to "Micorsoft DAO version 3.##" is checked. (Scrolllllll doooown)

Nic;o)

[mfaisalwarraich]

thank u once again nico. i setup the required object library "Microsoft DAO 3.6 Object Library". is it the required one? and i am still receiving the following runtime error

Run-time error '2455':

You entered and expression that has an invalid reference to the property Form/Report

The following is the code which is set on On Open Event of Main Report.

1. Private Sub Report_Open(Cancel As Integer)
2. Dim rs As DAO.Recordset
3. Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.Re cordsetClone

4. If (rs.EOF And rs.BOF) = True Then

5. Me![txtNoData] = "No Records Found"
6. End If

7. End Sub

3rd line is highlighted in yellow in the code window.

rpt_DailyState is mainReport
rpt_BroughtInDead is subreport of this mainReport.

is the 3rd line has the right syntax to call a subreport in a report?
please guide me. thank u

[MMcCarthy]

Move your code to the On Format event of the Report rather than the On Open event.

[mfaisalwarraich]

ok i put the code in or format event of the main report and now getting this error:

Run-time error '2465':

Application-defined or object-defined error.

what should i do now?

[MMcCarthy]

ok i put the code in or format event of the main report and now getting this error:

Run-time error '2465':

Application-defined or object-defined error.

what should i do now?

What line of code is it stopping at?

[mfaisalwarraich]

What line of code is it stopping at?

Here is the code:

1. Private Sub Report_Open(Cancel As Integer)
2. Dim rs As DAO.Recordset
3. Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.Re cordsetClone

4. If (rs.EOF And rs.BOF) = True Then

5. Me![txtNoData] = "No Records Found"
6. End If

7. End Sub

it is stopping at the 3rd line. and the error is: Application-define or Object-defined Error.

[MMcCarthy]

Try this instead

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.Recordset
 
    Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.RecordsetClone
 
    If (rs.EOF And rs.BOF) = True Then
 
        Me![txtNoData] = "No Records Found"
    End If
 
End Sub
 

RecordsetClone is all one work. It is being broken up by the tag editor.

[mfaisalwarraich]

Try this instead

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rs As DAO.Recordset
 
    Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.RecordsetClone
 
    If (rs.EOF And rs.BOF) = True Then
 
        Me![txtNoData] = "No Records Found"
    End If
 
End Sub
 

RecordsetClone is all one work. It is being broken up by the tag editor.

thank u for an instant reply. but regretfully submitted that i got the same error at the same line.

[nico5038]

Hmm, guess we need to see the .mdb to detect the problem. Can you make an "insensitive" testversion and post the zipped .mdb here as attachment ?

Nic;o)

[MMcCarthy]

Hmm, guess we need to see the .mdb to detect the problem. Can you make an "insensitive" testversion and post the zipped .mdb here as attachment ?

Nic;o)

To attach it, you will need to post first and then edit the post to add the attachment.

[mfaisalwarraich]

To attach it, you will need to post first and then edit the post to add the attachment.

sorry i cant make it 97.7kb file. what should i do now? is there any other way to upload this MDB???

[mfaisalwarraich]

ok here is the testVersion. i made one table, 3 queries and 3 reports based on this table.

condition field has 2 separate values ("Normal" and "Dead in Hospital"). In search critera of 2 queries i found the records. but at third query named qry_BroughtInDead i did not find any record. after making queries i made three report based on these 3 queries. and then i added these three reports into a main report called rpt_DailyState. i what i want is if there is no result in "rpt_BroughtInDead", a text box should have the value "No Records Found" on the main report (rpt_DailyState). now please tell me where im doing mistake. thank u

[nico5038]

OK, looks indeed that the RecordsetClone won't work :-(
But as your report isn't linked to the main report, you can use the report query to get the number of records like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim rs As DAO.Recordset
 
         Set rs = CurrentDb.OpenRecordset("qry_BroughtInDead")
 
         If rs.EOF And rs.BOF Then
 
              Me![txtNoData] = "No Records Found"
 
         End If
 
End Sub
 

Make sure that the field [txtNoData] is placed in the Details section and that the "Visible" property is set to "Yes", otherwise it won't show.

Nic;o)

[mfaisalwarraich]

OK, looks indeed that the RecordsetClone won't work :-(
But as your report isn't linked to the main report, you can use the report query to get the number of records like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim rs As DAO.Recordset
 
         Set rs = CurrentDb.OpenRecordset("qry_BroughtInDead")
 
         If rs.EOF And rs.BOF Then
 
              Me![txtNoData] = "No Records Found"
 
         End If
 
End Sub
 

Make sure that the field [txtNoData] is placed in the Details section and that the "Visible" property is set to "Yes", otherwise it won't show.

Nic;o)

thank u nico its really working in testVersion. but when i copy that code into my original database i presented with another error "Too Few Parameters. Expected 1." im sorry i know its my mistake. i was not aware of the consequences not to include a search form which is working as search critera for these queries. here is my testVersion file again which has "Too Few Parameters" error. please help me out. thank u

[nico5038]

This message is cause by an erroneous fieldname.
Check all fields to appear in the table/query !

Nic;o)