Proper recordset feeding (RESOLVED)

Hello everybody

I created a report embedded in my Visual Basic application. This report displays the records of a single table but groups them using a type field contained in the recordset. Since I`m creating the Recordset dynamically and setting it using code, I would like to ask what is the correct SQL syntax for feeding such a report.
This is the code I`m using :

Code:

---


  Set rsListaMhxanhmatwn = New ADODB.Recordset
  strSQL = "Select Kwd_Mhxanhmatos , Typos.Typos , Montelo.Onomasia  , Mhxanhma.Perigrafh, Klinikh.Onomasia, Seriakos_Arithmos From Mhxanhma Inner Join Montelo On Montelo.Kwd_Montelou = Mhxanhma.Kwd_Montelou Inner Join Typos On Typos.Kwd_Typou = Montelo.Kwd_Typou Inner Join Klinikh On Klinikh.Kwd_Klinikhs = Mhxanhma.Kwd_Klinikhs Order By Typos.Typos, Montelo.Kwd_Montelou;"

 rsListaMhxanhmatwn.CursorLocation = adUseClient
 rsListaMhxanhmatwn.Open strSQL, connbio_tech, adOpenDynamic, adLockOptimistic

CrystalReport_listaMhxanhmatwn.Database.SetDataSource rsListaMhxanhmatwn, 3, 1

---

Unfortunately, when I give this recordset to the report, I get no record printed at all. However, I`ve checked that the Recordset does indeed return records. Any ideas?

I can reproduce the exact report in the stand alone version of Crystal Reports and then get the results I want. Here is the SQL query Crystal Reports uses :

Code:

---


 SELECT `typos`.`Typos`, `montelo`.`Onomasia`, `mhxanhma`.`Perigrafh`
 FROM  (`bio_tech`.`mhxanhma` `mhxanhma` INNER JOIN `bio_tech`.`montelo` `montelo` ON `mhxanhma`.`Kwd_Montelou`=`montelo`.`Kwd_Montelou`) INNER JOIN `bio_tech`.`typos` `typos` ON `montelo`.`Kwd_Typou`=`typos`.`Kwd_Typou`
 ORDER BY `typos`.`Typos`, `montelo`.`Onomasia`

---

which is as you can see is the same as the one I`m using. Why the discrepancy between the two?

George Papadopoulos

After some thought and a bit of frustration, I finally found out that Crystal Reports was not grouping correctly, because I had not included the primary & foreign keys of the tables in my Recordset.

George Papadopoulos