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!

[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?