By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Working with subreports in a Report

100+
P: 194
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.
Oct 10 '07 #1
Share this Question
Share on Google+
23 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 10 '07 #2

100+
P: 194
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.
Oct 11 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #4

100+
P: 194
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
Oct 13 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 13 '07 #6

100+
P: 194
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.
Oct 13 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 13 '07 #8

100+
P: 194
:) 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.
Oct 13 '07 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 13 '07 #10

100+
P: 194
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
Oct 14 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Move your code to the On Format event of the Report rather than the On Open event.
Oct 14 '07 #12

100+
P: 194
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?
Oct 14 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 14 '07 #14

100+
P: 194
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.
Oct 14 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this instead
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2. Dim rs As DAO.Recordset
  3.  
  4.     Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.RecordsetClone
  5.  
  6.     If (rs.EOF And rs.BOF) = True Then
  7.  
  8.         Me![txtNoData] = "No Records Found"
  9.     End If
  10.  
  11. End Sub
  12.  
RecordsetClone is all one work. It is being broken up by the tag editor.
Oct 14 '07 #16

100+
P: 194
Try this instead
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2. Dim rs As DAO.Recordset
  3.  
  4.     Set rs = Reports!rpt_DailyState!rpt_BroughtInDead.Report.RecordsetClone
  5.  
  6.     If (rs.EOF And rs.BOF) = True Then
  7.  
  8.         Me![txtNoData] = "No Records Found"
  9.     End If
  10.  
  11. End Sub
  12.  
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.
Oct 14 '07 #17

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 14 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 14 '07 #19

100+
P: 194
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???
Oct 15 '07 #20

100+
P: 194
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
Attached Files
File Type: zip testVersion.zip (20.6 KB, 92 views)
Oct 15 '07 #21

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.    Dim rs As DAO.Recordset
  3.  
  4.          Set rs = CurrentDb.OpenRecordset("qry_BroughtInDead")
  5.  
  6.          If rs.EOF And rs.BOF Then
  7.  
  8.               Me![txtNoData] = "No Records Found"
  9.  
  10.          End If
  11.  
  12. End Sub
  13.  
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)
Oct 15 '07 #22

100+
P: 194
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.    Dim rs As DAO.Recordset
  3.  
  4.          Set rs = CurrentDb.OpenRecordset("qry_BroughtInDead")
  5.  
  6.          If rs.EOF And rs.BOF Then
  7.  
  8.               Me![txtNoData] = "No Records Found"
  9.  
  10.          End If
  11.  
  12. End Sub
  13.  
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
Attached Files
File Type: zip testVersion.zip (29.2 KB, 94 views)
Oct 16 '07 #23

nico5038
Expert 2.5K+
P: 3,072
This message is cause by an erroneous fieldname.
Check all fields to appear in the table/query !

Nic;o)
Oct 16 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.