VB & Crystal 8 - Help with code to manipulate report in VB!

[HDer]

Hi, I appreciate you looking at my request for help!

Using VB6, Crytsal Reports 8, Access Database, through ODBC, Win98/ME. Database has many tables, Products is the main table, related tables for discounts, costs, etc. also included in structure.

Report arranged in fashion to show product, specs, and discount structure - grouping is by Product name, details by quantity discount structure (qty 1 for $10 each, qty 10 for $9 each, etc.) , page breaks after each quantity discount listing. Report prints out fine, several pages, one for each product with discount structure.

Report would look like:
----------------------------- (page 1)
PRODUCT: MOTOR (Product table)
PRICE SCHEDULE:
QTY PRICE (discount table)
1 $10
10 $9
100 $8
500+ $6

------------------------------ (page 2)
PRODUCT: BEARING (Product table)
PRICE SCHEDULE:
QTY PRICE (discount table)
1 $20
5 $19
50 $18
100+ $16

I want to be able to manipulate the report in VB to be able to print out only one specific product, or range of products by product number,or name - in this case, it means choosing a particular product name or ID or range within the grouping, not the details. I tried using some suggested code in VB, but it does not seem to work (shown below).

It seems to work for examples with one table, with the values in the details section of the report (which is NOT what I have). When I introduce related tables, and groupings, it does not seem to work. Do I need to create a recordset that includes all the tables used? If so, what happens to the tables defined in the ODBC/ADO connection in the Report? Do these tables need to be 'detached'? ..... to many questions!

I would think what I want to do is a very common requirement with VBa nd Crystal. Any suggestions on the ideal and simple (least amount of code) approach to report manipulation from within VB would be greatly appreciated!

Sample code in zipped file would be even better! Email is HDer@Smartt.com

Thanks in advance.
Henry Der


VB CODE USED WHICH DOES NOT WORK FOR MY REPORT
************************************************
Dim Report As New CrystalReport1
Dim rs As New ADOR.Recordset

Private Sub Form_Load()

'Make report print only one section by product name
rs.Open "select * from Products where PrdName = 'Disc - 10 inch'", "sms"
Report.Database.SetDataSource rs

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

End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth

End Sub

 

[kenhamady]

You can't pass a recordset, unless the report was designed to work with a recordset (ie created with the Active Data Driver), and the recordsets match field for field.

You probably don't need a recordset to do what you want (although you can do it that way also). Typically the way to get different criteria each time is to base the report's selection formula on a parameter field or fields, and then pass values from VB to the parameter. Or you can simply write a valid CR selection formula in VB and pass that to the report. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)