CR 8, VB 6, Parameterized Queries from Access DB

[YAmoah]

I've developed an interface using VB6 to print several reports that were created using Crystal 8. The interface is suppose to filter records from tables or queries in an Access 97 database based on what the user enters. I'm working with a total of 4 reports. 3 of them were created using data from tables within Access. 1 was created using an Access query. I'm bringing all of these reports into my VB program using the Report Designer Component(RDC). I also use the SetDataSource method in my VB code to overwrite the data source used to initially create the report. This method replaces the data source, at run-time, with the DAO recordset created in my VB code. This method works very well when I'm using tables in my reports; it filters correctly, based on user input. However, when I try to use a query for one of the reports, the filtration does not appear to work. Instead, all records from the query display on the report. So, in an effort to correct this problem, I first tried to place a parameter within the query and pass a value from my VB program. For this particular report, the required value is the
State Abbreviation. When running the query from Access, the user is first prompted to enter the State Abbrev. After entering, the desired results are displayed. So, it works fine in Access. However, when I try to pass the user's state abbrev. entry from VB to the parameter within Access, I get the error message, "Too Few Parameters. Expected 1."
Another problem I noticed, is that when parameters are present within the data source, in this case the Access query, the Crystal Report Designer(RDC) or the Crystal 8 Software itself, will not let you add that particular data source to populate the report. In fact, it doesn't even show up as an option in the list from which to choose.

Any assistance with this problem would be greatly appreciated. This is really driving me crazy!

Thanks in advance,

YAmoah

 

[MarkSweetland]

I've found it equally easy to prompt the user for the parameter value in VB6, create the recordset and pass it as the new data source through ADO rather than use CR's selection filter--as long as the fields in the report don't change based on the value of the state abbreviation and its just a filter:

Create the new instance of the CR report, create a new instance of ADODB.Recordset, create and query the data using the State Abbrv from the user. Set the reports data source to the new ADODB.Recordset. The report will show all records in the resultant (filtered) recordset.



Mark

 

[swilliams]

Instead of having a parameter in the query, reset the SQL statement to include the parameter which you can prompt for in your application. IE

1. Prompt for the parameter in your application and store in a variable.
2. Build the SQL statement in your application using the user input and then overwrite the SQL statement in the query with this new one, that hard codes the "parameter" into the where clause.
3. Leave the report based on the query and run the report.
Simon