Passing A Dynamic Parameter..how is this NOT possible 1

[mgkSHA]

Hello:

I am using Crystal Reports within a VB 6.0 project which using dynamic SQL. I have the Crystal reports working within the criteria: the user selects the August table, the report populates with August data, etc. Here is my problem: the parameter dropdown boxes are not changing with the recordset. I have asked this question on numerous boards and no one seems to have any idea how to do this. This is very simple in Visual Basic and .Net, why is this so difficult in Crystal?

How can I get my parameter values to change with the changing table. As in, my "extension" parameter should change everytime the table changes. But, instead, the choices for extension remain the same, whether it be the July table, August table, etc. Here is the line of code I am using within my CRViewer_Form_Load event...this doesn't work. Any advice?

Report.ParameterFields(1).Value = ("Select DISTINCT extension from [" & strTable & "]&quot

Any ideas of how I can do this? There has to be a way to change a parameter as the ADO source changes..isn't there?

As in:

current default: ADO connection to July table, "phone extension" parameter - - links to the distinct list of extensions found in July

THEN - - via the code found in the CRVIEWER, the reportsource table is changed, therefore the "phone extension" parameter is now a distinct list of extensions found in [variable] table.

The only advice I have been given is to add each value to the parameter in the code: this database has over 35,000 records with hundreds of phone extensions..I have to manually add 300 phone extensions into my parameter field in order to get it to match its ADO source?

I do not have a lot of experience in Crystal and in many ways I am puzzled that this is so hard and no one can help me. If anyone has any advice on this please let me know. Thanks

Martin K

 

[JoeCrystal]

The following code is sample of how I would solve the problem I think you are having:

Dim Para As CRAXDRT.ParameterFieldDefinition
Dim i As Integer
'
' Clear Existing Default Values
'
Set Para = Report.ParameterFields(1)
'
For i = Para.NumberOfDefaultValues To 1 Step -1
Para.DeleteNthDefaultValue i
Next
'
Set ADOrs = New ADODB.Recordset
With ADOrs
'Use the connection already opened
.ActiveConnection = ADOcn
.CursorType = adOpenDynamic
'SQL to retrieve the data
.Open ("Select DISTINCT extension from [" & strTable & "]&quot ' Add order by for sort
End With
'
While ADOrs.EOF = False
Para.AddDefaultValue ADOrs.Fields(0).Value
ADOrs.MoveNext
Wend
'
' User should now see new default list
'

 

[mgkSHA]

Thanks Joe, I will give this a try.

And yes, this is exactly the problem I am having. I appreciate your assistance. I will let you know if it works.

 

[mgkSHA]

Joe:

Just wanted to let you know this was exactly what I needed, I did some tweaking to the code so it fit my exact needs, but that was great, thanks, it's tough to find good Crystal advice.