Two distinct queries on a single report (RESOLVED)

[divined]

Hello everybody

I have a report which needs to display a list of employees. This can and has easily been done. The thing is that I want to display a Report Header. The title of this header will come from another table containing a single record-single field. I could create an Inner Join query but then I would be repeating this field for every employee. This is the code I`m currently using :


Dim oApp As New CRAXDRT.Application
Dim oReport As CRAXDRT.Report
Dim strPathToReport, strSQL As String
Dim connbio_tech As ADODB.Connection
Dim oRs As ADODB.Recordset


' ADO Connection to the database
Set connbio_tech = New ADODB.Connection
strConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER = localhost; DATABASE = xxx; UID = xxx; PWD = xxx;"
connbio_tech.ConnectionString = strConnection
connbio_tech.Open ' Άνοιγμα σύνδεσης

' Open Recordset
strSQL = "Select * From Texnikos Order By Kwd_Texnikou"
Set oRs = New ADODB.Recordset
Set oRs = connbio_tech.Execute(strSQL)
CrystalReport1.Database.SetDataSource oRs, 3, 1

' Display in Viewer control.
CrystalActiveXReportViewer1.ReportSource = CrystalReport1

CrystalActiveXReportViewer1.ViewReport


where CrystalReport1 is a viewer control. Could I create a second query for the header title and pass it only to the field in the report header as required?

thx, in advance

George Papadopoulos

[RobDog888]

You could execute a initial query to retrieve this column header info and pass it as a parameter to your CR Report and then execute your rs as
you are and set the DataSource.

[divined]

As a matter of fact, I`m rather new to Crystal Reports. I just learned of the parameter fields, which are I think the best approach. I`ll check it out tomorrow and see how it goes!

[RobDog888]

Use the Field Explorer to create a new parameter. Then drag it to your report design layout and place in place of your current
textfiled that you wish to replace. Then from VB populate the parameter and SetDataSource and presto! :D

[divined]

ok. thx. I managed to successfully call a report and view it on screen. Nonetheless, my application is MDI and it so happens that an item may have been deleted, when the form containing the Viewer control regains focus. I would like to have the report displayed on the viewer control refreshed. Something like the Refresh method of an ADODB.Recordset object, applied to a Crystal Reports report.
Does such a method exist?

[RobDog888]

You could do a rs.Requery to refresh your adodb.recordset object and then call a .Refresh method of the viewer control.
Is this what you meant?

[divined]

Unfortunately, it does not work as expected. Firstly, after refreshing the query, refreshing and displaying the report in the report viewer control, a dialog box pops up requesting a parameter field, which I have previously passed by code. Then when the report is displayed the recordset remains unaltered when it should have at least one less record. Why is this so? This is the code I`m using for the Form Active event.


' Refresh data in query. Refreschieren Sie die data im Grid!
rsListaTexnikwn.Requery

p1.ClearCurrentValueAndRange


Set p1 = CrystalReport_listaTexnikwn.ParameterFields.GetItemByName("Title")
p1.AddCurrentValue (rsYphresia.Fields("Yphresia").Value)


CrystalActiveXReportViewer1.Refresh


Initially, I had tried this code


rsListaTexnikwn.Requery
CrystalActiveXReportViewer1.Refresh


In my Form Load event I call the function that initializes the viewer control and the embedded Crystal Reports report. Here is this code as well :


Private Sub LinkViewerToReport()

CrystalReport_listaTexnikwn.Database.SetDataSource rsListaTexnikwn, 3, 1

Set p1 = CrystalReport_listaTexnikwn.ParameterFields.GetItemByName("Title")
p1.AddCurrentValue (rsYphresia.Fields("Yphresia").Value)

CrystalActiveXReportViewer1.ReportSource = CrystalReport_listaTexnikwn

CrystalActiveXReportViewer1.ViewReport
End Sub


The parameter dialog box, pops up both ways.

Any ideas?