Open CR from Access 2000 VBA?

[lynnd]

Hi,
Per management direction, I have created a simple Access 2000 db to produce a Crystal report (V8.5). The report is only supposed to show one customer. I want to pass a parameter from my VBA code in Access (in the click event procedure of a cmd button) that will open the report with the current record. The code I have so far is:

    Dim crxApp As New CRAXDRT.Application
    Dim crxRpt As CRAXDRT.Report
    Dim crxDbTable As CRAXDRT.DatabaseTable
    
    Set crxRpt = crxApp.OpenReport(App.Path & "\RptName.rpt", 1)

    For Each crxDbTable In crxRpt.Database.Tables
        crxDbTable.Location = App.Path & "\RptName.mdb"
    Next

    crxRpt.RecordSelectionFormula = "{?Parameter_Name} = " & Me.ReportID 'This is a unique identifier for each record

    CRViewer.ReportSource = crxRpt
    CRViewer.ViewReport

The error I get when I run the form is "Object Required". To be honest, I'm not even sure that this is the correct way to go about this, so I'm open to any ideas you may have. I appreciate your help.
Thanks,
Lynn

 

[MarcusHemy]

I'm no expert but at a guess I would say you need to instantiate the Crystal Application object before setting crxRpt something like:

Dim crxApp As CRAXDRT.Application

Set crxApp = CreateObject("CrystalRuntime.Application&quot

You have used:

Dim crxApp As New CRAXDRT.Application

which is not quite the same thing. I seem to remember from somewhere that you shouldn't use 'as New' for creating an object because it doesn't release the memory properly (ie 'Set crxApp = Nothing' does not work).

Anyway, the above works for me.

 

[lynnd]

Thanks for your reply--
As I was playing around with the code this morning, I changed the

 App.path

to the actual path name (ie \\servername\folder1\folder2\report.mdb). I stopped getting the "Object Required" error--and now I just have some syntax problem with the RecordSelectionFormula (which I have never used before).

I'm not sure if what I changed this morning addressed the same problem you mentioned above--I'm self-taught for VBA, and I'm not quite sure that I really understand how objects and memory work together.

If you have any experience using the RecordSelectionFormula in VBA (or VB), please post an example.

Thanks!
Lynn

 

[MarcusHemy]

I don't use RecordSelectionFormula - my reports use parameters and I update the parameters through code eg the report selection says {User} = {?User} and I then update the contents of {?User} by enumerating the contents of ParameterFields.

Sample:

Create the object and open the report as before then:

Dim crxParameter As craxdrt.ParameterFieldDefinition
Dim nCounter As Integer
Dim sParam as string

For nCounter = 1 To crxRpt.ParameterFields.Count
'pick off each parameter one by one
Set crxParameter = crxRpt.ParameterFields.Item(nCounter)
'get the parameter name
sParam = crxParameter.ParameterFieldName
'check the parameter name
Select Case UCase$(Trim$(sParam))
Case "USER"
crxParameter.AddCurrentValue sUser
Case "PERIOD", "PER"
crxParameter.AddCurrentValue sPeriod
End Select
Set crxParameter = Nothing
Next

Here is a sample of RecordSelectionFormula (not mine):

'Create Temp Storage
Dim strSelect As String
'Create Report instance
Dim Report As New UIBenSummrpt
'Set Mouse Pointer to busy
Screen.MousePointer = vbHourglass
'Set Crystal Reports smartviewer source
' to report
CRViewer1.ReportSource = Report
'Reset Reports selection criteria
Report.RecordSelectionFormula = ""
'Create a string for selection criteria
strSelect = "{ado.Client}= '" & strClient & "' AND " & "{ado.Year}= '" & strYear & "'"
'Set Report's selection criteria
Report.RecordSelectionFormula = strSelect
'Display Report
CRViewer1.ViewReport