VB6 & CR 8.5 help needed 1

[pmaxon]

I have a VB6 application which will pass a parameter called sLName from a combox to a report which I created using CR 8.5. In VB6, I selected "Add Crystal Report" from the menu which created for me a CrystalReport1.DSR and a FORM1. I am using an Access database. My report name is AdjBill.rpt. What code must I add to the FORM1 in order to get this to work? Also, do I only need to view the SQL in my report and add WHERE Adjuster.'Adjuster.Last.Name' = sLName. Ultimately, I need to select a name from my combobox and pass that name to my report and produce my report.

Any help would be GREATLY appreciated! Thanks all!

 

[vidru]

Have you set up a parameter for the report?

In the Report Designer, right click on Parameter Fields > New > name it sLName > Value Type should be String.

Right click on some blank space on the report > Report > Edit Selection Formula > Records > and enter this formula:
[tt]
{Adjuster.Last.Name} = {?sLName}
[/tt]
I'm assuming that Form1 is the default form created by the designer with the CRViewer control. Look at the code, and add a variable in the General Declarations section:
[tt]
Dim mLNameParam As String
[/tt]
On your form with the last name combobox, in your OnClick event, send the parameter value and show Form1:
[tt]
Form1.mLNameParam = cboLName.Text
Form1.Show
[/tt]
In Form1's Form_Load event, before the report is sent to the viewer, you can set the parameter value like this:
[tt]
Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam
Report.EnableParameterPrompting = False
[/tt]
That should bring up the report automatically, using whatever name was selected in your combobox.

-dave

 

[vidru]

Whoops...
[tt] Dim mLNameParam As String[/tt]
should be
[tt] Public mLNameParam As String[/tt]

-dave

 

[pmaxon]

Thanks! I hope this works as I have about 15 reports to add. Will it be that same process for each one? In that each needs it's own form and dsr?

 

[vidru]

If you're going the .dsr route, then yes, you'd need a separate .dsr for each report. You'd only need one 'Viewer' form though.

You could control the flow of the reports to the viewer by using a code module to determine which report the user is trying to run, then use a Case statement in the viewer form to call the corresponding designer.

-dave

 

[pmaxon]

Well, it is attempting to open the report, however, I am receiving the following message: "Server has not yet been opened". Looking around in the forum, I find where people are having this issue with SQL server, but I am not running on a server, I am attempting to go against an access database containing 5 tables. If I in fact need a username/password, is there a generic way to set this up and does it need to be done for each table? Please help if you are able.

TIA

 

[vidru]

Depends on how you're connecting to the database. If the database is password protected, and you're connecting using ODBC, you need to call the LogOnServer method before you can interact with the report.

Here's an example using the Xtreme database that comes with Crystal. I set up the database with a password of "test", and created an ODBC User DSN named Xtreme pointing to the database. Before viewing the report, I log in to the database with the following:
[tt]
Report.Database.LogOnServer "pdsodbc.dll", "Xtreme", , , "test"[/tt]

If you're not using ODBC, then let me know how you're connecting to the database and we'll go from there.

-dave

 

[pmaxon]

I am using ODBC but did not setup any password or username. The database name is Heartland_Insurance_Tables which includes the following 5 tables: Adjuster, Agency, Claim, Company and Office. The particular report I am trying to get to run uses the Adjuster, Claim and Company tables.

 

[vidru]

I can't recreate the problem. If I take the password off of the database, I don't have any problems calling a report without using the LogOnServer method.

-dave

 

[pmaxon]

So... what does that mean? I don't need the LogOnServer method and something else is going on? If I wanted to try the LogOnServer method, how would it be setup?

 

[pmaxon]

It appears as though there is a User ID of "admin". Would that mean there is also a password?

 

[vidru]

I suggest you take a look at the sample apps from Crystal:

http://support.businessobjects.com/communityCS/TechnicalPapers/apps_rdc.pdf.asp

-dave

 

[pmaxon]

I didn't see anything that would really pertain to me since I don't have any security on my database. And as you mentioned, if there is no password, you don't need the LogOnServer method.

 

[pmaxon]

dave - when I reach the following line: Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam
I get an error Runtime error "424" Object required and themLNameParam is empty. This is in the form load event. Should the Report.ParameterFields... actually reflect the name of my report as in AdjBill.ParameterFields...

 

[vidru]

If you're using .dsr's to create reports, you'd call the report like this:

Dim Report as New AdjBill

... where AdjBill is the name you've given to that particular .dsr. Then, before you preview or print the report, you could access the properties and methods
by using Report.<PropertyOrMethod>....

-dave

 

[pmaxon]

I have placed the following on my form:

Dim Report As New CrystalReport1
Public mLNameParam As String
Public sLName As String

Private Sub Form_Load()
Report.EnableParameterPrompting = False
Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam

Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

I blow up with "Object Required" when I reach this line of code: Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam

mLNameParam contains the value from my combobox. What might be wrong?

 

[pmaxon]

Hey Dave...

GetItemByName does not seem to be a property of Report.Parameterfields in the following line.

Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam

 

[vidru]

I'm pretty sure it exists. The problem might be with your parameter fields.

In your code, put this line right before you try to do anything with the parameter fields:

MsgBox Report.ParameterFields.Count

If that comes up with a zero, then you don't have the parameter(s) defined properly in the report.

If it's not zero, try looping through the ParameterFields collection to see the names:

Dim cParams As CRAXDRT.ParameterFieldDefinitions
Dim cParam As CRAXDRT.ParameterFieldDefinition
Set cParams = Report.ParameterFields
For Each cParam In cParams
With cParam
Debug.Print "Name: " & .Name & vbCrLf & _
"ParamFieldName = " & .ParameterFieldName & vbCrLf
End With
Next

-dave

 

[pmaxon]

The parameter is setup ok now and I receive a 1 in my msgbox. My parameter name is sLName but I receive an "object required" message when I reach this line of code:

Report.ParameterFields.GetItemByName("sLName").Value = mLNameParam

mLNameParam contains the value from my combobox. I tried adding the following DIM sLName as string or DIM sLName as parameter on my Form1 but neither of those made any difference. Is it not picking up my sLName from the report?

 

[pmaxon]

dave-

You have a good heart and I really appreciate all of the help you are offering me.

pat