• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Using CR 4.6.1.0 with VB 6

Hello,
I am developing an application in VB with Access backend.
I need to generate reports based on the outputs of the datacontrol in the form. The .DataSource for the datacontrol changes every time since it does varied search on the database tables.
I have designed a format of the report in Crystal Report 4.6.1 with all the required fields and named it "searchreport1.rpt".
And I have added the Crystal Reports control to the Result-display-form
I also have provided a button on the form which can be used to get the reports printed.
The .RptFileName has been set to the .rpt file as well.
The .ReportSource has been set to 3-crptDataControl.
How can I set the Datasource property of the CR Control to the datacontrol(machinedatasearch) on clicking the "Print Report" button on the form?.I need to do this since there are 2 datacontrols in the form.
I have already tried
rpt1.DataSource = machinedatasearch
When I give the rpt1.Action = 1 , I do get the  correct results printed.But they are not in the required format.
When the .ReportSource property of the CR control is set to the datacontrol,I seem to get the correct results but in the wrong format,ie, all the fields in the table are printed and there is not formatting whatsoever.
When the .ReportSource is set to the Report,I get the wrong results,ie all the records in the table gets printed without any filtering,but I do get the correct format.I had set some font formatting and bordering in the .rpt file.All these things get printed nice and fine.
Please give the details as I don't have much idea about how to use the CR to generate reports dynamically.
0
vinaylak
Asked:
vinaylak
  • 7
  • 6
1 Solution
 
mlmccCommented:
Since you have now put all the pieces in one place, I think I see the problem.

Are you Saving data with the report?

Open the report in CR
Click FILE
Ensure SAVE DATA WITH REPORT is not checked.

Another way (later versiions of CR)
Before the Action line

rpt1.DisacardSavedData

mlmcc
0
 
vinaylakAuthor Commented:
To Mlmcc,
That doesn't work either since the option is already unchecked.I have also tried the .DiscardSavedData property of the CR control.That doesn't work either.
I am beginning to get depressed!
This is all the code in both the buttons:
This is the first button.It is supposed to print all the fields in the table in a required format(datareport.rpt)
*********************************************************
Private Sub prntresultswhole_Click()
If machinesuccess1 = 1 Then
    machinedatasearch.RecordSource = "SELECT * FROM machinedata WHERE modelname='" & machinedet & "'"
    'rptwhole.SQLQuery =  "SELECT * FROM machinedata WHERE modelname='" & machinedet & "'"
    machinedatasearch.Refresh
    machinesuccess1 = 0
End If
rptwhole.Action = 1
rptwhole.DiscardSavedData
End Sub
**********************************************************
And this is the code in the second button,which is supposed to print an abridged report for the same query.
Obviously ,it shouls contain only a couple of fields in the table.
**********************************************************
Private Sub prntresultssub_Click()
If machinesuccess1 = 1 Then
    'rptsub.SQLQuery =  "SELECT * FROM machinedata WHERE modelname='" & machinedet & "'"
    machinedatasearch.RecordSource =  "SELECT * FROM machinedata WHERE modelname='" & machinedet & "'"
    machinedatasearch.Refresh
    machinesuccess1 = 0
End If
rptsub.Action = 1
rptsub.DiscardSavedData
End Sub
**********************************************************
Properties for rptwhole and rptsub are:
Properties        rptwhole                      rptsub
----------      ----------------       -------------------
Name            rptwhole                      rptsub
DataSource      machinedatasearch       machinedatasearch
Destination     crptToPrinter               crptToPrinter
DiscardSaved..  True                            True
Reportfilename  pth\datareportwhl.rpt  pth\datareport1.rpt
ReportSource    crptReport             crptReport
**********************************************************
Please help me out somehow.
0
 
mlmccCommented:
WHat is the SQL the report is built against?

mlmcc
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
vinaylakAuthor Commented:
I have built the report with all the fields in the 2 table in the database.
Besides it would be impossible to suggest a specific query for the report since the reports are to be generated based on search criteria and search results.
The Code field in both the tables have been set as the primary key and the index field.
0
 
mlmccCommented:
The report should be built on the following query

Select Table1.*, Table2.* FROM
Table1 JOIN Table2 ON
Table1.Code = Table2.Code

In the code you can then

rpt.ReplaceSelectionFormula = FilterCriteria

FilterCriteria is a where clause to restrict the data from the database.

mlmcc
0
 
vinaylakAuthor Commented:
To mlmcc,
Thanks a lot for coming to my rescue again and again and again.But by sad fate,I am still unable to make use of your help.
The reason being that,I don't know how I can build a report based on an SQL Query.Can you please explain?I have always used the Report Designer to get the required fields in the necessary format.Not that I have done it many times either.
And can you please give an example for the ".ReplaceSelectionFormula" properties usage?
Please be patient as I am just learning the process myself.Thanks a lot again.
0
 
mlmccCommented:
Can you build a query in the database?  That is the best place because CR SQL is limited and prone to errors.

What tables are you trying to use?  

mlmcc
0
 
vinaylakAuthor Commented:
I have 2 tables,one for the customers data and the other for machine data for each purchase or enquiry by a customer.
I need to search data in these tables and get them printed with the CR. So the criteria will be different in each search. Since this is the case,will it be possible for me to build a query inthe database,each time the criteria changes?. Even if I do this, how can I get it printed with CR?
Please explain how you can build a report on an SQL QUERY.

0
 
mlmccCommented:
Yes.  I always build the queries in the database and don't use a where, group by, or order by clauses unless it will remain that way for every report.

From your application you can use

crRpt.ReplaceSelectionFormula = "The filter clause"
It is simply the where clause without the word where.  You can use variables and controls from your form.

crRpt.ReplaceSelectionFormula = "{View.Name} = 'Mike'"
crRpt.ReplaceSelectionFormula = "{View.Name} = '" & txtName.text & "'"

mlmcc
0
 
vinaylakAuthor Commented:
At last I have managed to get hold of a newer version of CR.It is CR7.But I have no idea how I can use it.It just gives a CRViewer in the components list.How can I configure it to display or print a previously designed report(done using report designer)?
I have added references to all the CR7 elements in the reference list.
0
 
vinaylakAuthor Commented:
At last I have managed to get hold of a newer version of CR.It is CR7.But I have no idea how I can use it.It just gives a CRViewer in the components list.How can I configure it to display or print a previously designed report(done using report designer)?
I have added references to all the CR7 elements in the reference list.
0
 
mlmccCommented:
The same code you used with 4.6 should work with the OCX control.

What references did you add?

mlmcc
0
 
mlmccCommented:
Glad I could help

mlmcc
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now