Passing a recordset to crystal reports 8.5

[Dominator22m]

Hi there,

Please can someone help.. I have been pulling my hair out over this for about a week now.

I have a project with a report, the user can select a vehicle to view in the report and only records for that vehicle should be displayed, but all the records are being displayed. I have created a recordset and tried passing this to the report but it just wont have it Below is a snippet of my code :- (I have hard code the vehicle in for testing purposes)

Dim Report As New CrystalReport2
Dim ConHistory As Connection
Dim RsHistory As ADODB.Recordset

Set ConHistory = New Connection

With ConHistory
.Provider = "microsoft.jet.oledb.3.51"
.ConnectionString = "Data source = " & App.path & "\history.mdb"
.Open
End With

Set RsHistory = New ADODB.Recordset

With RsHistory
.Open "SELECT * FROM history WHERE [vehicle name] = " & Chr(34) & "T560 CFM" & Chr(34), ConHistory, adOpenStatic
End With

Report.Database.Tables(1).Location = App.path & "\history.mdb"

Report.Database.SetDataSource RsHistory, 3, 1

CRViewer1.ReportSource = Report

CRViewer1.ViewReport

Any help or guidance anyone could offer on this subject would be greatly appreciated.

Thanks In Advance

Brian

 

[Golom]

I've found it easier to define the report with SQL that retrieves the whole recordset and then just pass a record selection clause to the report, something like this

rpt.RecordSelectionFormula = "{history.[vehicle name]} = T560 CFM"

 

[MarkSweetland]

You don't need to specify the location of the source data if you're passing the recordset via SetDataSource
[green]'Report.Database.Tables(1).Location = App.path & "\history.mdb"[/green]

Your report also may have been created with the Save Data With Report option set on. In which case you might be seeing the raw data with no filter from the initial connection and recordset used to create the report. Use Report.DiscardSavedData just before the Report.Database.SetDataSource RsHistory call and see if it helps.





Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Dominator22m]

Thanks Golum n MarkSweetLand for your responses.

Golum :-

The user is also able to select a date range, but i couldnt do this using the recordselectionformula. This is why i went for the passing recordset option. In the database the date order is mm/dd/yyyy but i couldnt get the recordselectionformula to accept this order.

(StartDate and Enddate is took from a DtPicker control order DD/MM/YYYY)

    startday = Mid(StartDate, 1, 2)
    startmonth = Mid(StartDate, 4, 2)
    startyear = Mid(StartDate, 7, 4)

    endday = Mid(EndDate, 1, 2)
    endmonth = Mid(EndDate, 4, 2)
    endyear = Mid(EndDate, 7, 4)

    StrStart = "{history.gprs date} > Date(" + startyear$ + ", " + startmonth$ + ", " + startday$ + ")"
    StrEnd = &quot;{history.gprs date} < Date(&quot; + endyear$ + &quot;, &quot; + endmonth$ + &quot;, &quot; + endday$ + &quot;)&quot;

    Report.RecordSelectionFormula = Report.RecordSelectionFormula & &quot; AND &quot; & StrStart & &quot; AND &quot; & StrEnd

When i tried changing the order i got an error message, something like days in month cant be greater than total days.

MarkSweetLand :-

I took out the code that sets the table location and checked the report but "Save data with report" wasnt ticked. I also added the line "Report.DiscardSavedData" but again the report is showing all the fields. My code now reads :-

'Connection created as above
'Recordset created as above

    Report.DiscardSavedData
    Report.Database.SetDataSource RsHistory, 3, 1
    
    CRViewer1.ReportSource = Report
    
    CRViewer1.ViewReport

N.B. There is also a subreport, wether this will make a difference i dont know.

It doesnt matter which way i achieve this wether it be by recordset or recordselectionformula but i also need to do the date ranges and i only know how to do this with the recordset.

Thanks In Advance

Brian


Bad spellers of the world untie!!!