Error Accessing Database Records

[gazza11]

I am using VB6 with Crystal reports 8.5 to produce a couple of reports. Both reports use the VB Data Environment to access the Access 2000 database 'workcare.mdb' and until now I have not had any real problems.

If I run the application and select cmdInjClass_click (see code below), data is obtained via the Data Environment which simply uses a query within Access to provide the information. If I then select cmdManhours_click it runs a query within the routine OpenHoursReport - this also works fine. I can run all selections alternately and multiple times and all reports are fine.

However - if I first open the cmdManhours report - that works fine but when I select the cmdInjClass report I get the following error.

******************************
Crystal reports Database Error
Unable to Access first Record
******************************

I select OK and then get

*********************
Crystal Report Viewer
SQL Server Error.
*********************
I can still run the manhours report multiple times but cannot run InjClass.

So long as I run the InjClass report first everything is OK. Can someone please help me - what have I done wrong.

Complete code is below.

----------------
Option Explicit
Dim ClassReport As New crInjClass
Dim HoursReport As New crManhours
Dim ServiceConnection As ADODB.Connection
Dim rsADO As ADODB.Recordset

Private Sub cmdInjClass_Click()
ClassReport.DiscardSavedData
CRVReport.ReportSource = ClassReport
CRVReport.ViewReport

End Sub



Private Sub OpenHoursReport()
Dim SQL As String
Dim strConnect As String

HoursReport.Text4.SetText Format$(DTShowStartDate.Value, "dd/mm/yyyy&quot
HoursReport.Text5.SetText Format$(DTShowEndDate.Value, "dd/mm/yyyy&quot
' Create and bind the ADO Recordset object
Set ServiceConnection = New ADODB.Connection
Set rsADO = New ADODB.Recordset

' Open the connection
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\workcare.mdb;Mode=Read"
ServiceConnection.Open strConnect
SQL = "SELECT Sum(manhours.Manhours) AS SumYearHours, Vehicles.Vehicle" & _
" FROM manhours INNER JOIN Vehicles ON manhours.VehicleID =" & _
" Vehicles.VehicleID WHERE (((DatePart(" & Chr$(34) & "yyyy" & Chr$(34) & ",[rptdate]))=2002) AND" & _
" ((DatePart(" & Chr$(34) & "m" & Chr$(34) & ",[rptdate])) Between 7 And 12)) OR" & _
" (((DatePart(" & Chr$(34) & "yyyy" & Chr$(34) & ",[rptdate]))=2003) AND" & _
" ((DatePart(" & Chr$(34) & "m" & Chr$(34) & ",[rptdate])) Between 1 And 6)) GROUP BY Vehicles.Vehicle, manhours.VehicleID"


rsADO.Open SQL, ServiceConnection, adOpenDynamic, adLockBatchOptimistic
HoursReport.Database.SetDataSource rsADO
On Error Resume Next

CRVReport.ReportSource = HoursReport ' other code modules can enable and disable it
CRVReport.ViewReport
End Sub


Private Sub cmdManhours_Click()
Call OpenHoursReport
End Sub

 

[gazza11]

Appears no one can help me. I suspected this would be a tough one. I have spent some time looking at my problem and it appears that if I use the VB Datareport to do the reporting under cmd InjClass_Click. All is OK so it looks like Crystal does not like using the Data report + runtime connections.

I have used the Datareport successfully in all other instances -

It just seems strange that if I run the cmdInjclass first everything is OK. Maybe I can run a hidden report (something like cmdInjclass_click) that no one sees.