Can we Query A Report??(need urgent help please)

[varunms]

Hi There,
I have Titles and their descriptions displayed in one report with page numbers added to that report, and what i am trying to do in the second report is printing the Titles in the order of their subject.So, in this second report can i get the pagenumber of the Title in which it is present in the first report.??can i do this??

I have tried doing like this:
In the design view of the second report in the control source i.e in the expression builder of the pagenumber(2nd report) can i write some thing like this:

Reports![Labels order by title]![pageno] where Reports![Labels order by title]![Title] Like Reports![orderby subjectindex1]![Title]

Report1 is ![Labels order by title]
report2 is ![orderby subjectindex1]

i don't think we can write but there should be a way to do this.

Could you please tell me some ways or a path how to start this application...

for further description you can look into my previous post on subject"Accessing or linking two reports" where i didn't get any responses. hope i will get some repsonse today.

Thanks for your Ideas,
varun

 

[jjonesal]

Okay, I probably have made this more complicated than it has to be, and there might be an easier way, but I think this will work....

Instead of trying to directly access the report fields, you can add some code behind the report to do what you need. Create you a temporary table for any fields you need from the detail line of the report. (I created a table that has three fields, field1, field2, and pageno) Then, in the Report_Open procedure, clear out this table so that each time you run the report you get your table re-initialized.

In the Detail_Print procedure (the On Print property of the Detail section of the report), add code that will basically insert a record into the temporary table each time you write a detail record to the report.

Here's some sample code....obviously you'd have to take into consideration your field types, etc. (I used the Page built-in variable to access tha page number).

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim sql As String
Dim vfield1 As String
Dim vfield2 As String
Dim vfield3 As String

vfield1 = Me.tblfield1
vfield2 = Me.tblfield2
vfield3 = Me.Page

sql = "INSERT into testtbl values ('" & vfield1 & "', '" & vfield2 & "', " & vfield3 & &quot"
DoCmd.RunSQL (sql)
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.RunSQL ("DELETE * from testtbl&quot
End Sub


Hope this helps... J. Jones
jjones@cybrtyme.com