Unable to Access First Record

[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

 

[virk0009]

u r saying i m using dataenvioument. where is that .
u havnt specify any such thing here.

parminder Singh

 

[gazza11]

The data environment is a VB6 ActiveX designer (see under Project/More ActiveX Designers) that allows you to connect to the database specifically for reporting etc. In VB6 you can also add a Data Report that uses the data presented by the Data Environment to create reports.
Instead of using MS Data Report I use the Crystal Report.
This has always been reliable - but I have never mixed the ways I access the database.
This should work - and in a fashion it does but not reliably. Does my method of access in the code above appear OK?

 

[virk0009]

Hi,

I know where does Dataenviorment exists. My question was
bit different. To solve ur problem u should try to
refresh the command object, connecting the report
and dataenviormnet. Just close and reopen that command
object. It may help u solving ur problem.

Parm Singh

 

[virk0009]

Just try to change the line HoursReport.Database.SetDataSource rsADO
to HoursReport.Database.SetDataSource rsADO ,3,1

It may help u.

 

[gazza11]

Thanks parm
Tried you suggestion but still no luck. I have been trying also to use the DataEnvironment connection before I go using the rsado connection.
What I mean is that the Dataenvironment has a connection to the access database via a query, however this query is never used. I seem to be having better luck with this method but would have liked to get my code above working.
If you have any other thoughts I would appreciate
Regards
gary

 

[virk0009]

Ok if its still not working. send me the form, report and
database. I will fix it and will return u soon.

Parm
Send it at
virk0009@hotmail.com

 

[gazza11]

Many thanks again Parm.
I have been trying different ways of obtaining the data and I ended up with the code being quite different from that above. Following your reply above - I returned all the code to original (ready to bundle for you) and ran.
You would not believe it - it worked OK.
All I can think of is that during my alterations I removed the database connection from the Crystal report and then reconnected. It appears that this may have fixed the problem.

Appreciate your assistance.
Regards
Gary

 

[virk0009]

Keep it up buddy,

Parm